diambil dari emailnya Mr. J sebelumnya Ordering Keys for Composite Indexes Follow these guidelines for ordering keys in composite indexes: 1. Create the index so the keys used in WHERE clauses make up a leading portion. 2. If some keys are used in WHERE clauses more frequently, then be sure to create the index so that the more frequently selected keys make up a leading portion to allow the statements that use only these keys to use the index. 3. If all keys are used in WHERE clauses equally often, then ordering these keys from most selective to least selective in the CREATE INDEX statement best improves query performance. 4. If all keys are used in the WHERE clauses equally often but the data is physically ordered on one of the keys, then place that key first in the composite index.
On 10/5/06, jimmy <[EMAIL PROTECTED]> wrote: > > Bagaimana ya cara menentukan kolom2 mana yang harus di > index sesuai dengan query yang kita buat ? > Misalkan kita membuat suatu query, trus kita harus > membuat index yang seperti bagaimana, supaya query > kita itu cepat. > Ada nggak ya panduan tentang hal ini ? > > Terima kasih sebelumnya. > > Salam, > Jimmy > > --- Tomi Wijanto <[EMAIL PROTECTED] <restomi_w%40yahoo.com>> wrote: > > > Kalau semua statistik antara index 1 dan 3 sama, ya > > tidak masalah toh oracle mau pilih yg mana utk > > dipakai. Kemungkinan sih pilih index berdasarkan > > mana > > yg dibuat lebih dulu atau berdasarkan urutan abjad > > (gak yakin sih, perkiraan doang). > > > > Kalau saya pribadi sih, seandainya akses lewat index > > 1 > > dan 3 sama costnya, tetapi index 3 jumlah kolomnya > > lebih sedikit dibanding index 1 (6 vs 3), mending > > gue > > pilih index 3 sih. Bukankah membandingkan 3 kolom > > lebih cepat dibandingkan 6 kolom :).. > > > > Kembali ke kasus anda, sebenarnya index yg Anda buat > > ngasal doang atau memang berdasarkan kebutuhan > > aplikasi??? Index yg tidak efektif hanya > > menghabiskan > > space dan terkadang mengganggu optimizer utk > > mendptkan > > exec plan yg optimal. > > > > regards, > > tomi > > > > --- J <[EMAIL PROTECTED] <nullzr%40yahoo.com.sg>> wrote: > > > > > select > > > > > > > table_name,index_name,num_rows,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor > > > from dba_indexes where owner='SAUDI6' and > > index_name > > > like > > > 'IDX_ITM_COST_MNTH%'; > > > ITEM_COST_MONTH IDX_ITM_COST_MNTH1 57275 > > > > > 49271 1 1 > > > 39909 > > > ITEM_COST_MONTH IDX_ITM_COST_MNTH2 57275 > > > > > 45292 1 1 > > > 41034 > > > ITEM_COST_MONTH IDX_ITM_COST_MNTH3 57275 > > > > > 49271 1 1 > > > 39909 > > > ITEM_COST_MONTH IDX_ITM_COST_MNTH4 57275 > > > > > 22 9 35 > > > 780 > > > > > > ada alasan lain kenapa si oracle keukeuh milih > > > IDX_ITM_COST_MNTH3 ? > > > index clustering factor nya sama aja........... > > > > > > --J > > > > > > > > > > > > > Kalau exec plan Anda mengakses table melalui > > > index, > > > > maka statistics CLUSTERING_FACTOR di index > > sangat > > > > mempengaruhi COST. Semakin kecil nilainya, > > semakin > > > > kecil total cost, dan semakin besar kemungkinan > > > index > > > > tsb dipakai. > > > > > > > > > > __________________________________________________ > > Do You Yahoo!? > > Tired of spam? Yahoo! Mail has the best spam > > protection around > > http://mail.yahoo.com > > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > [Non-text portions of this message have been removed] -- -----------I.N.D.O - O.R.A.C.L.E--------------- Keluar: [EMAIL PROTECTED] Website: http://indo-oracle.blogspot.com Mirror: http://indooracle.wordpress.com ----------------------------------------------- Bergabung dengan Indonesia Thin Client User Groups, Terminal Server, Citrix, New Moon Caneveral, di: http://indo-thin.blogspot.com Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/indo-oracle/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/indo-oracle/join (Yahoo! ID required) <*> To change settings via email: mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
