RE: need enhance feature for CBO :)

With an IOT, I don't think you can count on it using a subordinate index at all when the optimizer constructs the query plan. Since there is no physical data construct, it's pretty much forced to use the primary key (since it is the whole table to begin with, in a sense). I don't know if it's documented anywhere, but I've never been able to get the optimizer to avoid an index scan when doing unqualified selects against an IOT.

To repeat another poster's question: What is your goal?

Are you really sure, even if the optimizer used the unique index, that it return the results set any faster? Are you hoping for partition elimination? I don't see how that could happen with a select distinct. Maybe we're missing something....

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

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l