Re: Understanding partitioning by hash

On partitioned tables, you can create different types of indexes:

• NORMAL: just like you are used to: one big fat non-partioned index, unique or not unique, no problem.
• LOCAL: they are partitioned, with the same partition layout as the table. But, what if you plan to create a UNIQUE index? A unique index on a column means that ALL values in the column should be unique, regardless of the fact that they are in different partitions! So a UNIQUE LOCAL index can only be created when the partition key is included in the column list of the index. It does not matter if the partition key columns(s) is/are at the first or last position or somewhere in the middle.
• GLOBAL: they are partitioned, but with a different layout than the table (never used them, so I can not give you any gotcha's)

Partitioned indexes can be prefixed (the partition key is included) or non-prefixed (the partition index is NOT included).

• LOCAL : both are possible
• GLOBAL : only prefixed indexes are possible. With non-prefixed, partition pruning would not be possible (the reason why they are not supported?)

We have RANGE partitioned tables, partitioned on a date column (one partition per year). But this column is NOT part of the primary key. For the moment we have a NORMAL index, UNIQUE, to support the primary key constraint.

But you known that a primary key or unique constraint can be enforced, even with a non-unique index: create your index first, then your constraint, and the constraint will 'kidnap' the index created. But this kidnapping does not work with partitioned tables. So we ended up with a FAT non partitioned unique index for the primary key of our partitioned tables. (Or did I overlooked something?). Maybe I could have tried to create the PK constraint DEFERRABLE, for which Oracle can not and will not create a UNIQUE index. Somebody out there tried this one?

We are running Oracle 8.1.7.4, maybe Oracle 9 or Oracle 10 behave differently?

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:qn49ovoe9fqae16q3l2am4eafe8mlu4p1g_at_4ax.com...
> "Luc Gyselinck" <Luc.Gyselinck_at_nospampandora.be> wrote:
>
>
> Excellent explanation. I think I got it.
>
> >Now, what if you update the partitioning key? If you are lucky, the hash
> >value of the new partitioning key is the same as the hash value of the
old
> >partitioning key, and the row will stay in the same partition. If NOT,
the
> >row will be moved to another partition if and only if you specified
ENABLEd
> >ROW MOVEMENT in the CREATE statement of the table, or if you forgot, you
can
> >do an ALTER TABLE <table> ENABLE ROW MOVEMENT. If no ROW MOVEMENT is
> >enabled, you will receive an ORA- error, in the case the row should be
> >moved.
>
> So this is a "gotcha". A assume that enabling row movement has a price
> that has to be paid for, otherwise it would be always be enabled by
> default.
>
> The only thing that remains to be cleared is whether partitioned
> indexes should be local or global, still working on that (pros and
> cons)
>
> Thanks again.
>
> Rick Denoire
Received on Wed Oct 08 2003 - 19:25:39 CDT