Berikut tulisan yang bagus masalah index di oracle, aku kutip dari Evara Syamsiar
===========================================================
Pak yahya,...
Penyebab tidak terpakainya index ada banyak faktor yang mempengaruhi,
dan ini 100% bergantung dari spesifikasi server masing-masing. Salah
satu contoh penyebab tidak terpakainya index adalah statistics dari
table yang kedaluwarsa. Yang ini harus kita perhatikan betul-betul,
dan biasanya setiap periode tertentu kita harus melakukan analyze
table-table yang ada di database. Hal ini akan sangat membantu oracle
dalam membuat execution plan.
Dan, pak yahya tidak perlu khawatir karena tidak terpakainya index.
Karena belum tentu jika menggunakan index performance dapat meningkat
dengan drastis, dan belum tentu juga membaca secara full table scan
performancenya turun. Ini semua tergantung dari statistics masing-
masing table dan oracle sangat tahu betul bagaimana dia melakukan
pembacaan data tersebut.
Berikut ini demo, bagaimana oracle melakukan pembacaan menggunakan
index.
SQL> drop table t1;
Table dropped.
SQL> drop table t2;
Table dropped.
SQL> create table t1 as select * from all_objects;
Table created.
SQL> create table t2 as select * from all_objects;
Table created.
SQL> alter table t1
2 add constraints t1$pk primary key(object_id);
Table altered.
SQL> create index t2_index on t2(object_id,owner);
Index created.
SQL> analyze table t1 compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed.
SQL> analyze table t2 compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed.
Table T1 diatas memiliki primary key pada kolom OBJECT_ID, sedangkan
table T2 hanya memiliki index pada kolom (OBJECT_ID dan OWNER).
Sekarang kita lakukan query join seperti dibawah ini .
SQL> set autotrace traceonly;
SQL> select t1.*, t2.object_id, t2.owner
2 from t1, t2
3 where t1.object_id = t2.object_id
4 and t2.owner = 'SCOTT';
6 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 <-- br="" cost="" lihat="">
ini
1 0 NESTED LOOPS (Cost=16 Card=6 Bytes=630)
2 1 INDEX (FAST FULL SCAN) OF 'T2_INDEX' (NON-UNIQUE) (Cost=
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1 Card=1
Byt
4 3 INDEX (UNIQUE SCAN) OF 'T1$PK' (UNIQUE)
Statistics
----------------------------------------------------------
331 recursive calls
0 db block gets
174 consistent gets <-- br="" i="" logical="">
...
6 rows processed
SQL>
Perhatikan bahwa untuk mendapatkan 6 ROW ( scott mempunyai 6 object
saja ), oracle membaca menggunakan primary key pada table T1. Dan
logical I/O yang dihasilkan sebanyak 174 block dengan nilai cost = 16.
Sekarang bagaimana kalau saya query dengan owner user lain, misalnya
user EVARA seperti dibawah ini.
SQL> set autotrace traceonly;
SQL> select t1.*, t2.object_id, t2.owner
2 from t1, t2
3 where t1.object_id = t2.object_id
4 and t2.owner = 'EVARA';
102 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=52 <-- br="" cost="" lihat="">
ini
1 0 HASH JOIN (Cost=52 Card=103 Bytes=10815)
2 1 INDEX (FAST FULL SCAN) OF 'T2_INDEX' (NON-
UNIQUE)
3 1 TABLE ACCESS (FULL) OF 'T1' (Cost=40 Card=29518
Bytes=28
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
511 consistent gets <-- br="" i="" logical="">
1 physical reads
...
102 rows processed
Dan ternyata oracle tidak membaca primary key pada table T1, dia
memilih membaca full table scan. Apakah oracle melakukan kesalahan ?
jawabanya TIDAK. Sekali lagi 100% oracle bergantung dari spesifikasi
masing-masing server. Coba perhatikan parameter dibawah ini,
SQL> set autotrace off
SQL> show parameter index_cost
NAME TYPE
VALUE
---------------------------- ----------- -----------------------------
-
optimizer_index_cost_adj integer
100
Default parameter optimizer_index_cost_adj = 100, sampai oracle 9i
sekarang masing bernilai 100. Parameter ini menceritakan kepada
optimizer bahwa membaca menggunakan index atau menggunakan full table
scan cost-nya adalah sama, otomatis oracle akan memilih full table
scan.
Sekarang set nilai parameter = 30
SQL> alter session set optimizer_index_cost_adj=30;
Session altered.
SQL> select t1.*, t2.object_id, t2.owner
2 from t1, t2
3 where t1.object_id = t2.object_id
4 and t2.owner = 'EVARA';
102 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=41 <-- br="" cost="" lihat="">
ini
1 0 NESTED LOOPS (Cost=41 Card=103 Bytes=10815)
2 1 INDEX (FAST FULL SCAN) OF 'T2_INDEX' (NON-
UNIQUE)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2
Card=1
4 3 INDEX (UNIQUE SCAN) OF 'T1$PK'
(UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
314 consistent gets <-- br="" i="" logical="">
1 physical reads
...
102 rows processed
Lihat sekarang, oracle sudah membaca primary key lagi pada table T1.
Dan tentu saja untuk mendapatkan record sebanyak 102, cost = 41, dan
logical I/O = 314. performance lebih meningkat dibanding dengang saat
membaca menggunakan full table scan.
Apakah kita harus menurunkan nilai optimizer_index_cost_adj setiap
kali kita menemui bahwa ternyata index tidak terpakai ? saya menjawab
tidak selalu. Jika nilai parameter ini terlalu kecil, maka oracle
juga akan salah menentukan plan-nya.
Perhatikan query dibawah ini, saya akan select owner = 'SYSTEM'
sehingga didapatkan jumlah record yang lebih besar.
SQL> select t1.*, t2.object_id, t2.owner
2 from t1, t2
3 where t1.object_id = t2.object_id
4 and t2.owner = 'SYSTEM';
431 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=53 <-- br="" cost="" lihat="">
ini
1 0 HASH JOIN (Cost=53 Card=431
Bytes=45255)
2 1 INDEX (FAST FULL SCAN) OF 'T2_INDEX' (NON-
UNIQUE)
3 1 TABLE ACCESS (FULL) OF 'T1' (Cost=40 Card=29518
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
533 consistent gets <-- br="" i="" logical="">
3 physical reads
...
431 rows processed
Untuk mendapatkan 431 ROW milik user SYSTEM, oracle lebih memilih
membaca mengunakan full table scan. Mungkin anda akan bertanya loh,
kalau full table scan kan menjadi lambat. ( TIDAK ).
Sekarang saya turunkan parameter optimizer_index_cost_adj menjadi 5.
Ini akan menjadikan oracle semakin agresif dalam pembacaan mengunakan
index.
SQL> alter session set optimizer_index_cost_adj=5;
Session altered.
SQL> select t1.*, t2.object_id, t2.owner
2 from t1, t2
3 where t1.object_id = t2.object_id
4 and t2.owner = 'SYSTEM';
431 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 <-- br="" cost="" lihat="">
ini
1 0 NESTED LOOPS (Cost=26 Card=431 Bytes=45255)
2 1 INDEX (FULL SCAN) OF 'T2_INDEX' (NON-
UNIQUE)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1'
(Cost=2
4 3 INDEX (UNIQUE SCAN) OF 'T1$PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1009 consistent gets <-- br="" i="" logical="">
0 physical
reads
...
431 rows processed
SQL>
Dan sekarang berhasil, oracle membaca menggunakan primary key pada
table T1. Tetapi justru ini adalah kesalahan oracle dalam menentukan
execution plan. Lihat nilai cost diatas, membaca menggunakan index
COST menjadi 26 nilai ini turun dibandingkan pada saat membaca
menggunakan full table scan. Sekarang lihat nilai logical I/O-
nya ...? kini menjadi 1009. Nilai ini justru naik. Ketika masih
membaca menggunakan full table scan logical I/O = 533 sedangkan jika
membaca menggunakan index malah naik menjadi 1009. Nilai cost turun
sedang nilai logical I/O naik, berarti oracle salah menentukan
plannya.
Oleh karena itu belum tentu membaca menggunakan index menjadi
alternatif pembacaan data paling cepat. Dan belum tentu membaca
menggunakan full table scan merupakan metode pembacaan paling lambat.
Semua tergantung dari statistics table masing-masing, dan tergantung
dari spesifikasi databasenya.
Mungkin pak yahya bisa di share cara create indexnya dan hasil dari
execution plannya. Dan hati - hati dalam men-setting nilai parameter
optimizer_index_cost_adj , karena oracle bisa sangat agresif sekali
dan kadang oracle justru salah menilai pembuatan plan.
Semoga ini bisa membantu.
Terima kasih
Evara Samsyiar
www.indodba.net
==================================================================
