Re: Primary Keys with local indexes

Hi all,

I got a reply from Tom Kyte (Ask Tom's page) in oramag. It is not possible and I agree that partitioning would be pretty useless if it were because then for an primary key enforcement Oracle would have to scan *all* partitions.

Thank you for reading.

martin

Martin Haltmayer wrote:
>
> Hi all,
>
> we want to enforce a primary key constraint in 8.1.6.1 in a partitioned table
> where the partition key is not part of the index columns. The doc reads
>
> "Note that:
>
> Constraints use existing indexes; they do not create indexes unless necessary.
>
> Unique and primary keys can use non-unique as well as unique indexes. They can
> even use just the first few columns of non-unique indexes.
>
> At most one unique or primary key can use each non-unique index.
>
> The column orders in the index and the constraint do not need to match.
>
> If you need to check whether an index is used by a constraint, for example when
> you want to drop the index, the object number of the index used by a unique or
> primary key constraint is stored in CDEF$.ENABLED for that constraint. It is not
> shown in any catalog view. "
>
> This works very well for non-partitioned tables:
>
> SQL>
> SQL> drop table test cascade constraints;
>
> Table dropped.
>
> SQL>
> SQL> create table test (
> 2 n integer
> 3 , d date
> 4 )
> 5 /*
> 6 partition by range (d)
> 7 (
> 8 partition test_p1 values less than (to_date ('01.01.2000',
> 'DD.MM.YYYY'))
> 9 , partition test_p2 values less than (to_date ('01.01.2001',
> 'DD.MM.YYYY'))
> 10 , partition test_p3 values less than (maxvalue)
> 11 )
> 12 */
> 13 /
>
> Table created.
>
> SQL>
> SQL> create index test_i
> 2 on test (n)
> 3 /*
> 4 global
> 5 partition by range (d)
> 6 (
> 7 partition test_p1 values less than (to_date ('01.01.2000',
> 'DD.MM.YYYY'))
> 8 , partition test_p2 values less than (to_date ('01.01.2001',
> 'DD.MM.YYYY'))
> 9 , partition test_p3 values less than (maxvalue)
> 10 )
> 11 */
> 12 /
>
> Index created.
>
> SQL>
> SQL> alter table test add (constraint test_pk unique (n));
>
> Table altered.
>
> SQL>
> SQL> select index_name, partitioned, uniqueness from user_indexes where
> table_name = 'TEST'
> 2 /
> TEST_I NO NONUNIQUE
>
> SQL>
> SQL> spool off
>
> However, this does not work with partitioned tables:
>
> SQL>
> SQL> drop table test cascade constraints;
>
> Table dropped.
>
> SQL>
> SQL> create table test (
> 2 n integer
> 3 , d date
> 4 )
> 5 partition by range (d)
> 6 (
> 7 partition test_p1 values less than (to_date ('01.01.2000',
> 'DD.MM.YYYY'))
> 8 , partition test_p2 values less than (to_date ('01.01.2001',
> 'DD.MM.YYYY'))
> 9 , partition test_p3 values less than (maxvalue)
> 10 )
> 11 /
>
> Table created.
>
> SQL>
> SQL> create index test_i
> 2 on test (n)
> 3 local
> 4 /
>
> Index created.
>
> SQL>
> SQL> alter table test add (constraint test_pk unique (n));
> alter table test add (constraint test_pk unique (n))
> *
> ERROR at line 1:
> ORA-01408: such column list already indexed
>
> SQL>
> SQL> select index_name, partitioned, uniqueness from user_indexes where
> table_name = 'TEST'
> 2 /
> TEST_I YES NONUNIQUE
>
> SQL>
> SQL> spool off
>
> What can I do? Please help.
>
> Martin
Received on Thu Aug 10 2000 - 00:00:00 CDT