> Boleh tau nilai dari kolom DISTINCT_KEYS dan NUM_ROWS > di view user_indexes utk index idx_itm_cost_month.
masih blank....belom di gather stats nya... :( > > Dilihat dari jumlah block yg diakses, memakai index > justru lebih banyak. Biasanya ada 2 kemungkinan: > - jumlah baris yg diambil cukup banyak (mis: > 50% > dari total baris). Karena akses index anda diikuti > dengan akses table, maka bisa jadi total block yg > diakses lebih banyak dibandingkan full table scans yg > cuma mengakses table. rows yg direturn cuma 1. > - tidak semua kolom index benar2 dipakai utk > mengakses index. Ini bisa terjadi kalau ada konversi > tipe data pada WHERE clause. > Utk memastikannya, coba cara ini: > > explain plan for SELECT Wac_Cost, WAC_Qty > select * from table(dbms_xplan.display); > SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 40 | 2 | | 1 | TABLE ACCESS BY INDEX ROWID| ITEM_COST_MONTH | 1 | 40 | 2 | | 2 | INDEX RANGE SCAN | IDX_ITM_COST_MONTH | 1 | | 1 | ----------------------------------------------------------------------------------- > --> terus cek bagian 'Predicate Information', > akan ada kalimat 'access...'. > Itu akan menunjukkan kolom2 table apa saja yg bener2 > digunakan utk mengakses index. ini hasilnya beda dgn trace_sql secara actualnya....boong nih si ora... kemarin sih sempet diutak-atik indexnya, cuman swap posisi field2 nya aja, dari yg general ke spesifik.... cuman kok mbulet ya....kalo liat docs nya ora yg kayak begini...(khususnya point ke-3) 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. rgds J Send instant messages to your online friends http://asia.messenger.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/
