Re: need enhance feature for CBO :)
Date: Tue, 11 Nov 2008 16:43:49 -0800
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.
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)
> CREATE UNIQUE INDEX PVC_U1 ON PVC
> (SEQ, ER)
> LOCAL (
> ............ (500 partitions even more)
> 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
> 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