yup bisa dipaksa ternyata, hint saya yg pertama memang salah :) index_ffs hint doing the jobs pak :)
2008/11/13 rudi kristanto <[EMAIL PROTECTED]>: > Kalau di-plan-nya nggak muncul tapi ada akses, repot juga troubleshootnya. > Btw, apakah setelah dengan membetulkan index hint yang sepertinya salah > syntax tadi, Oracle mau menggunakan PVC_PK ? > > -- > Regards, > Rudi Kristanto. > > ________________________________ > From: Ujang Jaenudin <[EMAIL PROTECTED]> > To: [email protected] > Sent: Thursday, November 13, 2008 12:20:07 > Subject: Re: [indo-oracle] Re: need enhance feature for CBO :) > > 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] com>: >> 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 <ujang.jaenudin@ gmail.com> >> To: Riyaj Shamsudeen <riyaj.shamsudeen@ gmail.com> >> Cc: Oracle Discussion List <[EMAIL PROTECTED] org>; >> indo-oracle@ yahoogroups. com >> 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... >>>> >>>> >>>> > > > New Email names for you! > Get the Email name you've always wanted on the new @ymail and > @rocketmail. > Hurry before someone else does! > http://mail.promotions.yahoo.com/newdomains/aa/ > > [Non-text portions of this message have been removed] > > -- thanks and regards ujang | oracle dba jakarta | http://ora62.wordpress.com
