Re: Column as arrays.. more efficient than columns?

On 9/6/07, Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> wrote:
> Table is like
>
> create table foo (
> number int,
> subset int,
> value int
> )
>
> select * from foo;
> number | subset | value
> 1 1 1
> 1 2 2
> 1 3 10
> 1 4 3
>
> current query is like
>
> select number,
> avg(case when subset = 1 then value else null end) as v1,
> avg(case when subset = 2 then value else null end) as v2,
> avg(case when subset = 3 then value else null end) as v3,
> avg(case when subset = 4 then value else null end) as v4
> from foo
> group by number

arrays are interesting and have some useful problems. however, we
must first discuss the problems...first and foremost if you need to
read any particular item off the array you must read the entire array
from disk and you must right all items back to disk for writes. also,
they cause some problems with constraints and other issues that come
up with de-normalization tactics.

however, If a particular data is expressed actually as an array of
items (the polygon type comes to mind), then why not? let'l

that said, let's look at a better way to express this query. what
jumps out at me right away is:

select number, subset, avg(value) from foo group by subset;

does this give you the answer that you need? If not we can proceed
and look at why arrays may or may not be appropriate (i suspect I am
not seeing the whole picture here).

merlin