RE: need enhance feature for CBO :)
- From: "Michael Fontana" <mfontana@xxxxxxxxxxx>
- To: "'Oracle Discussion List'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 Nov 2008 09:10:03 -0600 (CST)
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
> >>
> >>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
