RE: Paritioning Challenge: alternate unique constraint

  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 10 Mar 2004 16:48:27 -0800

Could you expand on this please Mr. Cave?

You said "If you did have a number of local indexes, Oracle would have to scan 
each index before it inserted a new row in any partition, which would likely be 
a rather poorly performing option."

I'm not sure what this means. In my example below I have a table hash 
partitioned by column A, with unique index 1 global range partitioned by column 
B, and unique index 2 global range partitioned by column C. Are you saying that 
the uniqueness for columns B and C can be enforced by a better algorithm 
because indexes 1 and 2 are global, rather than local?

SQL> create table t (n1 number, n2 number, n3 number)
  2  partition by hash (n1) partitions 2 ;
Table créée.

SQL> create unique index tgui1 on t (n2) global partition by range (n2)
  2  (partition values less than (100), partition values less than (maxvalue)) ;
Index créé.

SQL> create unique index tgui2 on t (n3) global partition by range (n3)
  2  (partition values less than (100), partition values less than (maxvalue)) ;
Index créé. 

> -----Original Message-----
> Justin Cave (DDBC)
> 
> As I understand it, you want to create local indexes on a 
> partitioned table that do not include the partition key.
> 
> Logically, this sort of construct doesn't strike me as 
> possible.  Since uniqueness has to apply to the whole table, 
> you logically need to, in this case, have a single object to 
> store all possible first & last names.  This would require a 
> global index.  If you did have a number of local indexes, 
> Oracle would have to scan each index before it inserted a new 
> row in any partition, which would likely be a rather poorly 
> performing option.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: