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
----- Original Message -----
From: "J" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Thursday, October 05, 2006 8:54 AM
Subject: Re: [indo-oracle] index doesn't help
>> 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
[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/
