Yes absolutely you are correct. the problem is scanning by PVC_U1 index is longer than PVC_PK index. that way even I give it HINT, but oracle always ignored it.
-- thanks and regards ujang | oracle dba jakarta | http://ora62.wordpress.com On Tue, Nov 11, 2008 at 10:25 PM, Riyaj Shamsudeen <[EMAIL PROTECTED]> wrote: > 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... >> >> >> > >
