Re: need enhance feature for CBO :)
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: ujang.jaenudin@xxxxxxxxx
- 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.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
On Tue, Nov 11, 2008 at 2:49 AM, Ujang Jaenudin <ujang.jaenudin@xxxxxxxxx>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
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
