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 -- //www.freelists.org/webpage/oracle-l