Re: need enhance feature for CBO :)

I'm curious as to why the primary key is 5 columns, and yet you can create a unique index based on 2 columns from that same set.

Kind of an unusual design.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

On Tue, Nov 11, 2008 at 2:49 AM, Ujang Jaenudin <ujang.jaenudin_at_gmail.com>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
>
>
> 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
>
>
>

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