Setahu saya partition key tidak disimpan dalam struktur khusus selain dalam data dictionary kecuali partition key tersebut di-index. Jadi menurut saya index yang paling tepat (dari yang tersedia di script create table di bawah) untuk digunakan query tersebut ialah PVC_U1. Index ini cukup efisien untuk menjawab query tersebut, mengingat hanya kolom seq (dan memiliki constraint not null) yang muncul dalam select list sehingga tidak perlu mengakses lagi ke table yang dalam hal ini berupa IOT karena akses ke IOT dengan logical rowid lebih expensive jika dibandingkan rowid akses pada heap organized table.
Salah satu keuntungan yang dimiliki PVC_U1 terhadap PVC_PK ialah size-nya yang lebih kecil, jadi besar kemungkinan Oracle memilih PVC_U1 sebagai access path-nya, dengan tujuan meminimalkan pemakaian buffer cache. Karena ditulis sebagai special comment, hint yang salah tidak akan menimbulkan syntax error :) Untuk menggunakan PVC_PK seharusnya hint tersebut berupa /*+ index(PVC PVC_PK) */ tanpa use. Jika saya melihat struktur dari query di bawah, dimana hasil tidak perlu diurutkan (missing order by clause), akan lebih efisien lagi jika index diakses dengan multiblock read, menggunakan hint index_ffs(TABLE_NAME INDEX_NAME). Saya tidak mengerti yang bapak maksud dengan IOT mapping, akses PVC_U1 menimbulkan akses ke PVC_PK, sedangkan dari plan di bawah saya tidak melihat ada akses ke PVC_PK, bisakah tolong dijelaskan ? -- Regards, Rudi Kristanto. ________________________________ From: Ujang Jaenudin <[EMAIL PROTECTED]> To: Riyaj Shamsudeen <[EMAIL PROTECTED]> Cc: Oracle Discussion List <[EMAIL PROTECTED]>; [email protected] Sent: Wednesday, November 12, 2008 13:23:20 Subject: [indo-oracle] Re: need enhance feature for CBO :) 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 <riyaj.shamsudeen@ gmail.com> 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... >> >> >> > > Get your new Email address! Grab the Email name you've always wanted before someone else does! http://mail.promotions.yahoo.com/newdomains/aa/ [Non-text portions of this message have been removed]
