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.
--- J <[EMAIL PROTECTED]> wrote: > something funny euy.... > padahal utk query di bawah yg cocok ya index > idx_itm_cost_mnth1 kok idx_itm_cost_mnth3 yg dipake? > ada yg bisa jelasin why..why..why :( > > create index idx_itm_cost_mnth1 on > saudi6.item_cost_month > (book_code,cost_type,cost_method,year_month,locn_code,item_code) > tablespace bpms_idx; > > create index idx_itm_cost_mnth2 on > saudi6.item_cost_month > (year_month, locn_code, wac_cost) > tablespace bpms_idx; > > create index idx_itm_cost_mnth3 on > saudi6.item_cost_month > (year_month, locn_code, item_code) > tablespace bpms_idx; > > create index idx_itm_cost_mnth4 on > saudi6.item_cost_month > (cost_method, cost_type, year_month) > tablespace bpms_idx; > > > select index_name,num_rows,distinct_keys from > dba_indexes where index_name like > 'IDX_ITM_COST_MNTH%'; > IDX_ITM_COST_MNTH1 57275 49271 > IDX_ITM_COST_MNTH2 57275 45292 > IDX_ITM_COST_MNTH3 57275 49271 > IDX_ITM_COST_MNTH4 57275 22 > > SELECT Wac_Cost, WAC_Qty > FROM ITEM_COST_MONTH > WHERE Item_Code = :b4 > AND Book_Code = :b3 > --AND Centre_Code = J.Centre_Code > and locn_code = :b2 > AND Year_Month = TO_CHAR(:b1,'YYYYMM') > AND Cost_Type = 'S' > AND Cost_Method = '2' > > call count cpu elapsed disk > query current rows > ------- ------ -------- ---------- ---------- > ---------- ---------- ---------- > Parse 1 0.01 0.00 0 > 0 0 0 > Execute 7128 2.20 1.86 0 > 0 0 0 > Fetch 7128 0.37 0.34 1 > 21386 0 7128 > ------- ------ -------- ---------- ---------- > ---------- ---------- ---------- > total 14257 2.59 2.21 1 > 21386 0 7128 > > Misses in library cache during parse: 1 > Optimizer goal: ALL_ROWS > Parsing user id: 65 (SAUDI6) (recursive depth: 1) > > Rows Row Source Operation > ------- > --------------------------------------------------- > 7128 TABLE ACCESS BY INDEX ROWID ITEM_COST_MONTH > > 7128 INDEX RANGE SCAN IDX_ITM_COST_MNTH3 > (object id 34994) > > > Rows Execution Plan > ------- > --------------------------------------------------- > 0 SELECT STATEMENT GOAL: ALL_ROWS > 7128 TABLE ACCESS GOAL: ANALYZED (BY INDEX > ROWID) OF > 'ITEM_COST_MONTH' > 7128 INDEX GOAL: ANALYZED (RANGE SCAN) OF > 'IDX_ITM_COST_MNTH3' > (NON-UNIQUE) > > > --J __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- -----------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/
