| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Paritioning Challenge: alternate unique constraint
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]
> Sent: Friday, March 12, 2004 9:12 AM
> To: oracle-l_at_freelists.org
> Cc: Justin Cave (DDBC)
> Subject: RE: Paritioning Challenge: alternate unique constraint
>
> 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-----
> > From: oracle-l-bounce_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Justin Cave (DDBC)
> > Sent: mercredi, 10. mars 2004 23:35
> > To: oracle-l_at_freelists.org
> > Subject: RE: Paritioning Challenge: alternate unique constraint
> >
> >
> > 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.
> >
> > -----Original Message-----
> > From: oracle-l-bounce_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org]
> > Sent: Wednesday, March 10, 2004 5:48 PM
> > To: oracle-l_at_freelists.org
> > Subject: RE: Paritioning Challenge: alternate unique constraint
> >
> > 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_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
> -----------------------------------------------------------------
This communication is confidential and may contain privileged material.
If you are not the intended recipient you must not use, disclose, copy or retain it.
If you have received it in error please immediately notify me by return email
and delete the emails.
Thank you.
-- 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 - 17:58:13 CST
 |
 |