RE: Paritioning Challenge: alternate unique constraint

  • From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 11 Mar 2004 18:01:07 -0800

I get it now. Since you can't create a non-prefixed global partitioned index 
(the part I didn't think about) Oracle will only have to check one partition in 
the global index to enforce uniqueness in the column.

> -----Original Message-----
> Lim, Binley
> 
> The "key" difference is a global unique partitioned index is _not_
> partitioned on the same columns as the table!  It is 
> partitioned on the
> leading column(s) of the unique index, which are different from the
> partitioning column(s) of the table - otherwise they would be 
> local ;-)
> 
> > -----Original Message-----
> > From:       Jacques Kilchoer [SMTP:Jacques.Kilchoer@xxxxxxxxx]
> > 
> > I guess I'm dense because I still don't understand the 
> difference. Here's
> > what I think you're saying (simplifying the statement to 
> only consider one
> > column): If a unique index is local partitioned, and the 
> index column is
> > not the partition column, Oracle would have to go read 
> every partition of
> > the (local partitioned) index to see if the value is unique.
> > 
> > In my example I have global partitioned unique indexes 
> where the index
> > column is not the partition column. So Oracle would have to 
> go read every
> > partition of the (global partitioned) index to see if the 
> value is unique.
> > Isn't that the same performance hit than if the index was local?
> > 
> > If Oracle forbids, for performance reasons, a unique local 
> partitioned
> > index where the index column is not the partition column, 
> shouldn't the
> > same rule apply to a global unique partitioned index where the index
> > column is not the partition column?
> > 
> > > -----Original Message-----
> > >  Justin Cave (DDBC)
> > > 
> > > In this situation, it's easiest to think of a partitioned 
> > > table as a bunch of separate objects that Oracle happens to 
> > > know are related and local indexes, similarly, as a bunch of 
> > > separate index objects Oracle happens to know are related.  
> > > In this case, table t would be thought of as two separate 
> > > tables (t1 & t2).  If there were local indexes on t, those 
> > > would similarly be thought of as two separate indexes (i1 & i2).  
> > > 
> > > If the local indexes did not contain the column t is 
> > > partitioned on, Oracle would need to scan both i1 and i2 
> > > looking for the new row to ensure uniqueness.  We know from 
> > > elementary computer science that the cost of reading a 
> > > binary-tree index looking for an element is log( height of 
> > > tree ).  Because of the way Oracle sets up its b-tree 
> > > indexes, the height of i1 & i2 will almost always be the same 
> > > as, or very close to, the height of a single global index 
> > > (generally a height of 3 or 4).  This means that it will be 
> > > twice as expensive to verify the uniqueness constraint in 
> > > this example if the indexes were local rather than global.  
> > > In a more realistic example, where there are 10s or 100s of 
> > > partitions, it will be 10s or 100s of times more expensive to 
> > > ensure uniqueness on a local index rather than on a global index.
----------------------------------------------------------------
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: