Re: need enhance feature for CBO :)

Ujang
  But your index hint is incorrect, at least, I have not seen that. Try one of this:

 select /*+ index(pvc pvc_pk) */ distinct seq from pvc;
 select /*+ index_asc(pvc pvc_pk) */ distinct seq from pvc;
 select /*+ index_ffs(pvc pvc_pk) */ distinct seq from pvc;

  I still doubt that scanning through pvc_pk will be cheaper than pvc_u1 index. It is possible that pvc_pk to be cheaper if the table has many long primary key columns and very few short non primary columns. How many leaf blocks are there in both indices though?

Cheers
Riyaj

Ujang Jaenudin wrote:
> Yes absolutely you are correct.
> the problem is scanning by PVC_U1 index is longer than PVC_PK index.
> that way even I give it HINT, but oracle always ignored it.
>
>

Hi Ujang
> Table is an IOT with SEQ as second column and partitioned by SEQ column.
> So, if secondary index on this IOT is smaller in size, then it is probable
> that cost of that index access is cheaper. Also, plan shows index access
> only. Table blocks are not accessed at all. No sort either.
> Did I misunderstand the question? What is the problem are you trying to
> resolve here?
>
> Cheers
> Riyaj
> blog: http://orainternals.wordpress.com
>
> Ujang Jaenudin wrote:

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

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