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_at_quest.com]
> >
> > 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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Thu Mar 11 2004 - 19:58:09 CST
