Boleh tau nilai dari kolom DISTINCT_KEYS dan NUM_ROWS di view user_indexes utk index idx_itm_cost_month.
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. - 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 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'; select * from table(dbms_xplan.display); --> terus cek bagian 'Predicate Information', akan ada kalimat 'access...'. Itu akan menunjukkan kolom2 table apa saja yg bener2 digunakan utk mengakses index. regards, tomi --- J <[EMAIL PROTECTED]> wrote: > dear all, > > kok index gw gak ngaruh ya...malah keliatannya lebih > parah (mbikin cost lebih muahal), kalo saya cek ke > sql2 berikutnya > ada yg kasih saran? .... > > 9i.2.0.1 > linux rh71 > disk raid-5 :( > > > =================== > sebelum di index: > =================== > > 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.00 0.00 0 > 0 0 0 > Execute 7128 1.84 1.85 0 > 0 0 0 > Fetch 7128 309.39 464.24 2803651 > 5125032 0 7128 > ------- ------ -------- ---------- ---------- > ---------- ---------- ---------- > total 14257 311.23 466.09 2803651 > 5125032 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 FULL ITEM_COST_MONTH > > > Rows Execution Plan > ------- > --------------------------------------------------- > 0 SELECT STATEMENT GOAL: ALL_ROWS > 7128 TABLE ACCESS GOAL: ANALYZED (FULL) OF > 'ITEM_COST_MONTH' > > > =================== > index: > =================== > create index idx_itm_cost_month on item_cost_month > (item_code,book_code,locn_code,year_month,cost_type,cost_method) > tablespace bpms_idx; > > =================== > setelah diindex: > =================== > > 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 2 0.00 0.00 0 > 0 0 0 > Execute 14256 3.70 4.27 0 > 0 0 0 > Fetch 14256 309.98 464.80 2803654 > 5153544 0 14256 > ------- ------ -------- ---------- ---------- > ---------- ---------- ---------- > total 28514 313.68 469.07 2803654 > 5153544 0 14256 > > Misses in library cache during parse: 2 > Optimizer goal: ALL_ROWS > Parsing user id: 65 (SAUDI6) (recursive depth: 1) > > Rows Row Source Operation > ------- > --------------------------------------------------- > 7128 TABLE ACCESS FULL ITEM_COST_MONTH > > > Rows Execution Plan > ------- > --------------------------------------------------- > 0 SELECT STATEMENT GOAL: ALL_ROWS > 7128 TABLE ACCESS GOAL: ANALYZED (BY INDEX > ROWID) OF > 'ITEM_COST_MONTH' > 0 INDEX (RANGE SCAN) OF > 'IDX_ITM_COST_MONTH' (NON-UNIQUE) > > > tia, > 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/
