ok...thanks pak rudy, hal yang sama pun dinyatakan salah satu anggota milist oralce-l, dan ini factanya dari yg saya ungkapkan ke mereka, dari fakta tsb, oracle tidak secara murni mengeluarkan datanya as is dari PVC_U1, tapi sepertinya baca dulu ke PK nya setelah baca dari U1.
hi, this table and its indexes never analyzed, due to huge rows (more than billion rows). of course PVC_U1 is smaller physical blocks rather than PVC_PK. from development machine which has <14 million rows (through dba_segments): PVC_PK = 1280 blocks per partitions PVC_U1 = 256 blocks per partitions in development: select distinct seq from pvc; using PVC_U1 with parallel process -- > 5 mins never end :) select /*+ noparallel_index(pvc) */ distinct seq from pvc; using PVC_U1 with no parallel process -- 3mins 31 sec select /*+ index_ffs(pvc pvc_pk) */ distinct seq from pvc; using PVC_PK with no parallel process --46 sec another parameter : we set dynamic_sampling to 2 -- thanks and regards ujang | oracle dba jakarta | http://ora62.wordpress.com 2008/11/13 rudi kristanto <[EMAIL PROTECTED]>: > 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... >>> >>> >>>
