Re: need enhance feature for CBO :)

  • From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
  • To: ujang.jaenudin@xxxxxxxxx
  • Date: Tue, 11 Nov 2008 09:25:44 -0600

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


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




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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 11-2008