Re: Auto Partitioning

Hi,

Zeugswetter Andreas ADI SD wrote:
>
>> CREATE INDEX x ON test(a, b, c);
>>
>> isn't the same as
>>
>> CRETAE INDEX x ON test(c, b, a);
>
> That is only a problem if you also want to avoid a sort (e.g. for an
> order by),

..or if you want to use that index for 'WHERE a = 5'. The first one is
probably helping you, the second isn't.

> (an example would be a query "where c=5 and b between 0 and 20"
> and two partitions one for 0 <= b < 10 and a second for 10 <= b)

Hm.. in that case, an index on (a, b, c) wouldn't help. An index on (c,
b, a) would be just perfect, agreed?

Now, for the partitioning: you simply have to scan two partitions in
that case, no matter how you arrange your indexes. And this is where we
need some sort of multi-table index scan functionality. (I'm not saying
a multi-table index. Such a thing would be too large on disk. That
functionality should probably better be realized by using the underlying
per-table indexes).

>> That's why I'd say, the first columns of an index would have
>> to be equal to all of the columns used in the partitioning key.

I correct my own statement somewhat, here: only in that case, a single
table index can satisfy your request. For other cases, you'd have to
query more than one partition's indexes and mix them correctly to
maintain the right order, if required.

> No. It may change performance in some situations, but it is not needed
> for unique constraints.

Agreed, for unique constraints. But indexes are used for some more
things than just unique constraints checking. ;-)

Regards

Markus