Function Based Index

Jika sebuah fucntion diekseskusi pada kolom yg di index didalam sebuah where clause, dijamin index tersebut tidak akan berguna. Di oracle 8i memperkenalkan Function Based Index untuk mengatasi masalah ini.

Contoh kasus yg bisa kita pakai adalah penggunaan huruf besar dan huruf kecil dalam penyimpanan data. Jika penyimpanan data dengan menggunakan huruf yang tidak seragam (ada yg huruf besar dan ada yang huruf kecil) maka ini akan memperlambat pencarian data pada suatu table.

Sekarang kita lihat contoh pemakaiannya :

1. Membuat table Test

CREATE TABLE user_data (
 id          NUMBER(10)    NOT NULL,
 first_name  VARCHAR2(40)  NOT NULL,
 last_name   VARCHAR2(40)  NOT NULL);

BEGIN
  FOR cur_rec IN 1 .. 2000 LOOP
    IF MOD(cur_rec, 2) = 0 THEN
      INSERT INTO user_data
      VALUES (cur_rec, 'Ali' || cur_rec, 'Imran');
    ELSE
      INSERT INTO user_data
      VALUES (cur_rec, 'Jaya' || cur_rec, 'Hartono');
    END IF;
    COMMIT;
  END LOOP;
END;
/

ANALYZE TABLE user_data COMPUTE STATISTICS;
Sampai disini table dalam keadaan belum diindex jadi apapun querynya
akan melakukan full table scan.
SET AUTOTRACE ON
SELECT *
FROM   user_data
WHERE  Upper(first_name) = 'Ali2';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=10)
   1    0   TABLE ACCESS (FULL) OF 'USER_DATA' (Cost=1 Card=1 Bytes=10)

2. Buat Reguler Index
Sekarang kita buat index dan kita lihat efeknya pada table.

CREATE INDEX first_name_idx ON user_data (first_name);
ANALYZE TABLE user_data COMPUTE STATISTICS;

SET AUTOTRACE ON
SELECT *
FROM   user_data
WHERE  Upper(first_name) = 'Ali2';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=10)
   1    0   TABLE ACCESS (FULL) OF 'USER_DATA' (Cost=1 Card=1 Bytes=10)
Ternyata index tadi tidak dipakai sama sekali.

3. Buat Function Based Index
DROP INDEX first_name_idx;
CREATE INDEX first_name_idx ON user_data (UPPER(first_name));
ANALYZE TABLE user_data COMPUTE STATISTICS;

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

SET AUTOTRACE ON
SELECT *
FROM   user_data
WHERE  Upper(first_name) = 'Ali2';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=14)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'USER_DATA' (Cost=2 Card=1 Bytes=14)
   2    1     INDEX (RANGE SCAN) OF 'FIRST_NAME_IDX' (NON-UNIQUE) (Cost=1 Card=1)

Diatas kita lihat ditambahkan parameter agar server dapat menulis
ulang query atau dengan cara lain dengan menambahkan 2 parameter
tadi kedalam init.ora file.

~ by Ali Imran on March 5, 2010.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: