Comment in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
____England______January 21/23
____USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Vsevolod Afanassiev wrote in message
<4f7d504c.0301232329.795dacd8_at_posting.google.com>...
>Now, the same test but the table is range-partitioned on column n2
>and index is local:
>--------------------- beginning of Test#2 -----------------------
>drop table test;
>create table test
>(
> n1 number,
> n2 number,
> n3 number
>)
>partition by range (n2)
>(
> partition p1 values less than (-10),
> partition p2 values less than (0),
> partition p3 values less than (10)
>);
>
>create index test_pk on test(n1) local;
Create a local non-unique index called test_pk
>alter table test add (constraint test_pk primary key(n1) using index);
Create a GLOBAL unique index called test_pk to support the
constraint test_pk. But you already have an index (which is not
a global index) called test_pk. Hence
ORA-00995: name is already used by an existing object
>-------------------------- end of Test#2 ------------------------
>
>Note that index name is the same as constraint name, but this
>should be OK as indexes and constraints are in different Namespaces
>(see Oracle SQL Reference, Chapter 2 "Basic Elements of Oracle SQL",
>section "Schema Object Names and Qualifiers'). And it worked for Test#1.
>
>However, when Test#2 is executed, ALTER TABLE fails with
>ORA-00995: name is already used by an existing object
>
>Let's modify index name and make it different from constraint name:
>alter table test add (constraint testpk primary key(n1) using index);
>
>In this case it fails with
>ORA-01408: such column list
already indexed
>
This would be:
Create a GLOBAL unique index on column (n1) to support the
otherwise-named constrain - but (n1) is indeed already
indexed.
>IMHO both error messages are wrong: I don't have a constraint
>with the same name and I don't have another index on the same
column -
>the table has been dropped just before being created.
>
>* * * *
>
>I think the following situation is quite common:
>You have a large historic table (for example, TRANSACTIONS).
>The table has Primary Key on Transaction_ID populated from a
sequence.
>The table also has TRANSACTION_DATE column, and you want to partition
the
>table by this column without mofifying Primary Key. Is it possible?
>
No. If you want to support a unique or primary key constraint
with a local index, then the constraint must contain the
partitioning column(s). This restriction exists to maximise
partition independence - if it weren't in place, there would be
no point in using a local index anyway.
The problem is going to hit a lot of people who have used
purely synthetic keys on extremely large data sets. They
won't be able to partition them on natural boundary columns
and have local unique indexes.
>BTW, I got exactly the same result in 9.2.0
>
>Thanks, Sev
Received on Fri Jan 24 2003 - 03:13:05 CST
