Re: need enhance feature for CBO :)

you have an IOT with the leading edge being AN
and a unique index with the leading edge being SEQ.

The question you asked of the database is give me all the distinct SEQ numbers. 
The fastest way to do that is to full scan the unique index with SEQ on the 
leading edge. There is no structure that specifically indexes the partition key 
unless you build it.

Tom Kyte(Expert one on one oracle) and Jonathan Lewis(practical oracle8i) both 
have good chapters on partitioning in their books. A  quick review of one or 
both will help lots. Both books though not "10g" have very good explanations of 
how partitioning works and how to take full advantage of partitioning.






________________________________
From: Ujang Jaenudin <ujang.jaenudin@xxxxxxxxx>
To: Oracle Discussion List <oracle-l@xxxxxxxxxxxxx>; indo-oracle@xxxxxxxxxxxxxxx
Sent: Tuesday, November 11, 2008 4:49:26 AM
Subject: need enhance feature for CBO :)

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


      

Other related posts: