Re: need enhance feature for CBO :)
- From: TESTAJ3@xxxxxxxxxxxxxx
- To: ujang.jaenudin@xxxxxxxxx
- Date: Tue, 11 Nov 2008 07:08:53 -0500
Ok i'll ask the obvious, did you gather stats on the table and indexes?
joe
_______________________________________
Joe Testa, Oracle Certified Professional
Senior Consultant
Nationwide Investments
Data Engineering and Administration
(Work) 614-677-1668
614-312-6715
From:
"Ujang Jaenudin" <ujang.jaenudin@xxxxxxxxx>
To:
"Oracle Discussion List" <oracle-l@xxxxxxxxxxxxx>,
indo-oracle@xxxxxxxxxxxxxxx
Date:
11/11/2008 05:50 AM
Subject:
need enhance feature for CBO :)
Sent by:
oracle-l-bounce@xxxxxxxxxxxxx
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:
