RE: need enhance feature for CBO :)
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <jkstill@xxxxxxxxx>, <ujang.jaenudin@xxxxxxxxx>
- Date: Wed, 12 Nov 2008 11:49:39 -0500
Jared asks an interesting question, but I'm even more interested in why you
think
"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..." ?
Your query only needs the seq column. All the column seq values are in
pvc_u1, which is smaller than the PK.
So of course the CBO uses the smaller index. Since both SEQ and ES are in
the PK, I can absolutely guarantee that pvc_u1 is smaller than the PK, which
contains them and additional columns.
Now if you needed to look up values not contained in pvc_u1 (that is other
columns than SEQ and ES) that are contained in the PK, or for that matter
any non-overflow column in the case of an IOT, then your concern about going
back to the IOT would be valid. But the value you want IS in the index and
that index is guaranteed to be the smaller object.
Regards,
mwf
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jared Still
Sent: Tuesday, November 11, 2008 7:44 PM
To: ujang.jaenudin@xxxxxxxxx
Cc: Oracle Discussion List
Subject: Re: need enhance feature for CBO :)
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:
