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 -----------------------------------------------------------------