Printable View
If i have composite index on(deptno,job) columns and i am doing partitioning on "sal"(salary column) ...so here sal column in not part of composite index.
still it is said that we can have 'sal' column as partition key column.
But why can sal only be "non-unique"..why only deptno and job if used as partition key column can be "unique" ???
The question isn't clear to me. You can partition on columns that are constrained to be unique -- if you have a column that is unique and you do not partition by it then the uniqueness constraint must be supported by a global index. Is that what you mean?
Let me see if I understand your scenario.
You have a table somehow partitioned by SAL column.
You have an composite index on columns (deptno,job) pointing to that partitioned table.
You are asking why you can't have an unique index on SAL column.
Did I get it right?
Now I'm assuming you are trying to build your unique index on SAL column as locally partitioned. Did I get it right?
If this is the case, the answer is "local unique indexes must contain all of the columns of the partition key."
You can always build a non-partitioned index on SAL column and define it as unique, no problem at all.
Just remembered the story of five blind men & an elephant...
If the table is partitioned on SAL then a unique constraint on SAL can be supported by a local index. The index on (dept, job) is irrelevant.Quote:
Originally Posted by PAVB
You can have a unique index on your partition key, using global & local indexes.
You can have a unique index on non-partition keys using global indexes.
You can have a unique composite index using global indexes if the leading edge of the index does not contain the partition key.
You can have a unique composite index using local indexes if the leading edge of the index is the partition key.
Oracle cant't inspect multiple indexes to verify a uniqueness constraint, so you can't have a unique composite local index UNLESS the composite index includes the partition key as the leading column.