the combination of columns that unique as of this index :) CREATE UNIQUE INDEX PVC_U1 ON PVC (SEQ, ER)
SEQ alone is not unique. -- thanks and regards ujang | oracle dba jakarta | http://ora62.wordpress.com On Tue, Nov 11, 2008 at 10:32 PM, John D Parker <[EMAIL PROTECTED]> wrote: > you have an IOT with the leading edge being AN > and a unique index with the leading edge being SEQ. > > The question you asked of the database is give me all the distinct SEQ > numbers. The fastest way to do that is to full scan the unique index with > SEQ on the leading edge. There is no structure that specifically indexes the > partition key unless you build it. > > Tom Kyte(Expert one on one oracle) and Jonathan Lewis(practical oracle8i) > both have good chapters on partitioning in their books. A quick review of > one or both will help lots. Both books though not "10g" have very good > explanations of how partitioning works and how to take full advantage of > partitioning. > > > > ________________________________ > From: Ujang Jaenudin <[EMAIL PROTECTED]> > To: Oracle Discussion List <[EMAIL PROTECTED]>; > [email protected] > Sent: Tuesday, November 11, 2008 4:49:26 AM > Subject: need enhance feature for CBO :) > > dear all, (sorry cross posting) > > oracle 10.1.0.5 > huge machine :) > > CREATE TABLE PVC > ( > ........ > CONSTRAINT PVC_PK > PRIMARY KEY > (AN, SEQ, ES, ETI, ER) > ) > ORGANIZATION INDEX <=== IOT :( > PARTITION BY RANGE (SEQ) > ........... (500 partitions even more) > ) > NOPARALLEL; > > > CREATE UNIQUE INDEX PVC_U1 ON PVC > (SEQ, ER) > LOCAL ( > ............ (500 partitions even more) > ) > NOPARALLEL; > > > there are more than billion of rows > > select /*+ use_index(pvc pvc_pk) */ distinct seq from pvc; > > SELECT STATEMENT ALL_ROWS > PARTITION RANGE ALL > SORT UNIQUE NOSORT > INDEX FULL SCAN INDEX(UNIQUE) PVC_U1 > > > so, my question: > - why don't oracle utilize "partition key" for this case.... > (by not scanning full index will be better, because partition key > based on SEQ column). > getting list of partition key is the fastest way :) > - even when force using PVC_PK, CBO won't do it, I think scanning Primary > key > more efficient rather than roundtrip read PVC_U1 index and then again > read PVC_PK index, due to IOT mapping... > > > -- > thanks and regards > ujang | oracle dba > jakarta | http://ora62.wordpress.com > -- > http://www.freelists.org/webpage/oracle-l
