Modul Praktikum Data Mining "Index, View, dan Sequence"

Praktikum 6

INDEX, VIEW DAN SEQUENCE

Tujuan :

1.      Mengetahui dan memahami index,view dan sequence dalam Oracle

2.      Mengerti manfaat dan batasan penggunaan index, view dan sequence

3.      Mampu mengimplementasikan index, view dan sequence dalam Oracle

Alat dan Bahan :

1.      Buku literatur mengenai PL/SQL ORACLE, terutama yang menggunakan DBMS ORACLE 10g.

2.      Modul praktikum BASIS DATA LANJUT

3.      Modul praktikum DATA MINING DAN WAREHOUSING

Landasan Teori :

1.      INDEX

Index adalah objek schema yang berisi catatan dari nilai-nilai yang muncul pada satu kolom atau kombinasi kolom di index dari sebuah tabel. Index dibuat untuk mempercepat pengaksesan data pada suatu tabel. Index ini dibuat berdasarkan pada field - field dari sebuah tabel. Index bisa dibuat secara otomatis untuk constraint primary key atau unique key dan secara manual melalui CREATE INDEX statement.

a.      Membuat Index (CREATE INDEX)  Query :  

CREATE INDEX nama_index ON nama_tabel(nama_field1,nama_field2,….);

Contohnya :  

SQL> CREATE INDEX mahasiswa_idx ON mahasiswa(nim, nama, alamat );

b.      Memodifikasi Index (ALTER INDEX)  Query :  

ALTER INDEX nama_index [INITRANS integer][MAXTRANS integer] [STORAGE storage_clause]

Contoh :  

SQL> ALTER INDEX mahasiswa_idx INITRANS 10;

c.       Mengubah Nama Index (ALTER INDEX) 

Query :  

ALTER INDEX nama_index_lama RENAME TO nama_index_baru;

Contoh :  

SQL>              ALTER INDEX mahasiswa_idx RENAME TO mhs_idx;

d.      Menghapus Index (DROP INDEX)  Query :  

DROP INDEX  nama_index;

Contoh :  

SQL> DROP INDEX mahasiswa_idx;

2.      Segmen Index 

Segmen indeks dibuat ketika indeks diciptakan. Untuk setiap indeks nonpartisi akan terdapat satu segmen indeks sedang pada indeks terpartisi, setiap partisi memiliki satu segmen indeks. Pada saat indeks dibuat melalui perintah create index, proses server melakukan operasi sort nilai data yang diindeks sebelum berubah menjadi segmen indeks.

Segmen indeks tidak harus berada dalam satu tablespace yang sama dengan segmen  datanya. Script di bawah membuat tablespace khusus untuk menampung indeks yang terpisah dengan segmen data.

MEMBUAT SEGMEN INDEKS

SQL> CREATE TABLESPACE personal_index DATAFILE 2        ‘E:\ORACLE\ORADATA\T3TAN\personal_index.ORA’ 3        SIZE 12M REUSE AUTOEXTEND ON 4        NEXT 128K MAXSIZE 100M 5        EXTENT MANAGEMENT LOCAL; SQL> CREATE INDEX ferry.notes_id ON ferry.notes(A) 2        TABLESPACE personal_index PCTFREE 40 INITRANS 4 3        STORAGE (INITIAL 16384 NEXT 8192 PCTINCREASE 0); SQL> SET HEADING OFF SQL> COL hdg FOLD_BEFORE SQL> SELECT ‘Ukuran bytes : ’ hdg, bytes, 2        ‘Jumlah Blok    : ’ hdg, blocks, 3        ‘Jumlah Extents : ’ hdg, extents, 4        ‘Initial Extent : ’ hdg, initial_extents, 5        ‘Min Extent     : ’ hdg, min_extent, 6        ‘Max Extent     : ’ hdg, max_extent, 7        ‘Free List      : ’ hdg, freelist, 8        ‘Free List GrouP : ’ hdg, freelist_group, 9        ‘Header File    : ’ hdg, header_file, 10      ‘Header Block   : ’ hdg, header_block, 11      ‘Tipe Segmen    : ’ hdg, tipe_segmen, 12      FROM dba_segments WHERE segment_name=’NOTES_ID’; Ukuran bytes    : 589824 Jumlah Bit      : 72 Jumlah Extent   : 9 Initial Extent : 16384 Min Extent      : 1 Max Extent      : 2117483645 Freelist   : 1 Freelist Group : 1 Header File     : 19 Header Block    : 9 Tipe Segmen     : INDEX SQL> SET HEADING ON SQL> SELECT extent_id,block_id,bytes,blocks FROM dba_extents 2   WHERE segment_name= ‘NOTES_ID’ EXTENT_ID   LOCK_ID    BYTES  BLOCKS 0                      9     65536 8 1                      17    65536 8 2                      25    65536 8 3                      33    65536 8 4                      41    65536 8 5                      49    65536 8 6                      57    65536 8 7                      65    65536 8 8                      73    65536 8

Tablespace untuk indeks ditentukan melalui statement pembuatan indeks dan  parameter storage dapat disertakan untuk menetapkan karakteristik extent  dari suatu segmen indeks. Tampak bahwa indeks pada tabel notes dengan  kunci indeks kolom A mempersiapkan 9 extent dengan total 72 blok data. 

3.      Menentukan Indeks 

Developer membuat indeks agar unjuk kerja aplikasi lebih baik. Perintah create index  menghasilkan indeks dengan entry berupa nilai data yang diperoleh dari suatu kolom tunggal, gabungan beberapa kolom, ekspresi, dan fungsi.  

Ketika bekerja dengan indeks disarankan untuk mengacu pada kolom-kolom yang diindeks agar meningkatkan performansi join tabel, buatlah indeks dengan urutan kolom-kolom tabel yang tepat atau yang sering digunakan pada klausa where agar indeks digunakan untuk pencarian row. Pada pembuatan primary dan unique key secara otomatis akan dihasilkan indeks, begitu juga ketika primary dan unique key itu dihapus maka indeksnya secara otomatis dihapus. Untuk menghindari proses pembuatan indeks secara otomatis itu buatlah terlebih dahulu indeks non-unik berdasarkan primary key dan unique key. Selain itu buatlah juga indeks pada foreign key.

Kolom yang dipilih sebagai bagian dari indeks sebaiknya mengandung nilai data yang  unik  atau kolom yang sering digunakan dalam klausa where. Jika perbedaan nilai data dari suatu atau beberapa kolom sangat bervariasi,gunakan indeks B-Tree. Sedangkan untuk nilai data yang kurang bervariasi gunakan bitmap.

a.      Ekspresi Tunggal

Indeks ini menggunakan satu kolom sebagai kunci indeksnya. Misalnya untuk kemudahan akses data penduduk menurut nama penduduk dilakukan dengan perintah create index penduduk on kependudukan(upper(nama)).  

b.      Ekspresi Gabungan

Indeks ini dikenal juga sebagai concatenated atau composite index yaitu indeks  yang menggunakan beberapa kolom suatu tabel untuk membentuk indeks entry. Jumlah kombinasinya dapat dibuat hingga 32 kolom namun dalam prakteknya penggunaan lebih dari lima kolom jarang digunakan.

Anggaplah terdapat suatu tabel kependudukan yang terdiri dari kolom kabupaten,  kecamatan,  kelurahan,  serta  beberapa  kolom  untuk  entitas penduduk. Apabila tabel itu diindeks, kunci indeksnya bisa berupa CREATE INDEX pddk_ix ON penduduk (kab, kec, kel, nama). Dengan demikian susunan indeks entrinya diurutkan menurut kabupaten, kecamatan, kelurahan, dan nama penduduk. Untuk memanfaatkan indeks, query  harus  dilakukan  dengan memperhatikan susunan kondisi klausa where, misalnya SELECT * FROM penduduk WHERE kab=’PNK’ AND kec=’SEL’ AND kel=’BANGKA’. 

4.      Jenis-jenis Indeks

Untuk performansi query Oracle mendukung penerapan indeks B-Tree yang merupakan  indeks default, indeks bitmap untuk kumpulan key yang cardinality-nya rendah, indeks pada cluster B-Tree dan hash, indeks global dan lokal untuk partisi tabel, indeks reverse key pada aplikasi real application cluster (RAC), indeks function-based pada key yang berupa ekspresi atau fungsi, serta indeks domain untuk aplikasi atau cartridge.  

a.      Indeks B-Tree

Indeks ini menyimpan key dan rowid pada struktur B-Tree untuk menangani transaksi dengan intensitas tinggi dan cocok digunakan pada kolom-kolom tabel dengan  cardinality tinggi atau mengandung nilai data yang sangat beragam. Ketika transaksi berlangsung, nilai data pada tabel dan pohon indeks diperbarui. Apabila terjadi query, rowid yang digunakan untuk menemukan letak row data dalam tabel dicari pada indeks. Jadi pada indeks ini rowid untuk setiap key dari masing-masing row tabel akan disimpan dalam indeks. 

Perintah create index boy.kar_idx on boy.karyawan (nik) akan menghasilkan indeks BTree dan cocok untuk menangani transaksi OLTP karena update terhadap kolom yang  dindeks dapat berlangsung secara cepat melalui penerapan penguncian pada level row.

b.      Indeks Bitmap

Berbeda dengan indeks B-Tree yang secara default menyimpan rowid, indeks bitmap menyimpan suatu bitmap untuk setiap nilai kunci pada node leaf. Bitmap itu merupakan pengenal yang disusun oleh sejumlah bit dan dipetakan ke rowid. Jika bitnya  diset,  berarti baris dengan sejumlah rowid yang bersesuaian mengandung nilai key.  

Indeks bitmap sangat efektif untuk query yang mengandung banyak kondisi pada klausa where dengan and dan or karena operasi itu secara langsung membandingkan bitmap sebelum mengkonversi bitmap ke rowid. Indeks ini menggunakan space yang lebih kecil dan cocok untuk hardware dengan prosesor dan memori yang terbatas. Indeks bitmap cocok untuk menangani data berukuran besar dengan tingkat transaksi kecil atau pada lingkungan data warehouse serta kolom-kolom dengan cardinilty rendah. Namun mungkin saja DBA membuat indeks bitmap pada kolom dengan cardility yang tinggi untuk lingkungan data warehouse. Untuk data warehouse dengan star schema gunakan indeks bitmap join yang merupakan fungsionalitas baru di Oracle10g.

Jika kolom pada dimension table digunakan untuk membatasi data yang dipilih dari fact tabel (dengan foreign key) dan n dimension tabel (dengan primary key), indeks bitmap join bisa menghindari operasi join antartabel tersebut.

Cardinality rendah merupakan kolom dengan nilai data berulang atau kolom yang perbedaan nilai datanya sangat kecil dibandingkan jumlah row-nya. Cardinality dapat dilihat pada tabel karyawan berikut ini.  

NIK	Nama	Kelamin	Status	Dept
100	Goge	Laki-laki	Menikah	02
101	Titin	Perempuan	Janda	01
102	Beni	Laki-laki	Belum Menikah	03
103	Joel	Laki-laki	Duda	05
104	Susan	Perempuan	Menikah	03

Kolom kelamin, status, dan memiliki cardinality rendah karena itu tepat untuk menggunakan indeks bitmap misalnya create index boy.kar_depix bitmap on boy.karyawan(dept); sedangkan nik dan nama memiliki cardinality tinggi sehingga indeks B-Tree dapat diterapkan.

c. Indeks Reverse

Jika dibandingkan dengan indeks B-Tree, indeks reverse key membalik (reverse) byte setiap kolom yang diindeks (kecuali rowid) dan mempertahankan urutan kolomnya agar perubahan dapat disebar pada beberapa block indeks. Misalnya jika nilai suatu kolom yang diindeks adalah 1234 maka indeks reverse menggunakan angka 4321 agar pemutakhiran pohon indeks tersebar pada beberapa leaf blok. Oleh karena itu indeks ini cocok digunakan jika kolom-kolom yang diindeks memiliki nilai data yang berurutan atau mirip. Indeks ini digunakan pada real application cluster (RAC) di mana perubahan indeks dilakukan pada kumpulan blok leaf yang kecil. Dengan mereverse key yang diindeks maka insert akan tersebar pada berbagai leaf suatu pohon indeks. 

MEMBUAT INDEKS REVERSE

SQL> CREATE INDEX sales_wiltglstok ON sales (wilayah, tgl, stok) 2    COMPRESS REVERSE;              SQL> ALTER INDEX sales_wiltglstok  REBUILD;          --Mengubah indeks reverse menjadi noreverse  SQL> ALTER INDEX sales_wiltglstok REBUILD NOREVERSE;

Perintah pertama membuat reverse indeks, sedangkan perintah kedua melakukan rebuild indeks. Apabila indeks ini akan diubah ke mode default, gunakan klausa NOREVERSE untuk menormalkan pola penyimpanan key pada pohon indeks. 

Pencarian data dengan range-scanning tidak dapat diterapkan pada reverse indeks karena kunci indeks tidak lagi disimpan secara berdekatan sehingga pengambilan data hanya dapat dilakukan melalui key yang ditentukan atau full-table scan.

d. Indeks Fungsi

Indeks ini menggunakan fungsi (funtion-based index) untuk mendefinisikan kunci indeksnya.

MEMBUAT INDEKS FUNGSI

SQL> CREATE INDEX nama_ix ON penduduk(UPPER(nama));     SQL> CREATE INDEX           idx1 ON stat_sales(funcsal);                          --Menghasilkan statistic index  SQL> ANALYZE INDEX idx1 VALIDATE STRUCTURE;

Perintah pertama menghasilkan indeks entry dengan mengkapitalkan nama penduduk melalui fungsi built in upper. Pada contoh kedua digunakan  fungsi PL/SQL. funcsal yang harus ditentukan deterministic dan parameter inisialisasi QUERY_REWRITE_ENABLED,  QUERY_REWRITE_INTEGRITY bernilai TRUE dan TRUSTED. Tabel stat_sales itu dapat dianalisa setelah indeks dibuat dan query harus tidak memerlukan nilai null.

5.      Menghindari Duplikasi Data

Indeks mengorganisasikan row sehingga kolom-kolom yang digunakan sebagai kunci indeks  menyimpan nilai kolom yang ditentukan dalam ekspresi indeksnya. Pada indeks dengan ekspresi tunggal maupun composite di atas, penataan entitas penduduk memungkinkan adanya indeks entri yang sama. Agar indeks memelihara keunikan penduduk sehingga tidak ada duplikasinya, gunakan klausa UNIQUE pada ekspresi indeksnya. Misalnya CREATE UNIQUE INDEX pddk_uq ON penduduk(ktp). Indeks ini memastikan tidak adanya duplikasi row sehingga bisa menjadi kandidat untuk primary key.

6.      Keputusan Rebuild Indeks

Pemeliharaan indeks untuk membuat ulang (rebuild) indeks dilakukan melalui perintah alter index…rebuild. Jika terjadi korupsi indeks, mungkin saja rebuild indeks tidak berhasil karena masih ada korupsi indeks setelah proses rebuild. Untuk kasus ini, drop index kemudian create index yang dihapus tersebut.

a.      Memeriksa Perlu Tidaknya Rebuild Index

Jika operasi DML sering dikerjakan, indeks suatu tabel mungkin tidak tersebar secara merata pada pohon indeks. Oleh karena itu perlu pengecekan untuk menentukan bilamana indeks perlu direbuild.

MEMERIKSA BRANCH LEVEL INDEKS B-TREE

--Ambil statistik indeks  SQL> ANALYZE INDEX tes_idx_idx COMPUTE STATISTICS          ;        Index Analyzed                --Cek BLevel  SQL> SELECT index_name,blevel,DECODE(blevel,0,’OK BLEVEL’,1, 2        ’OK BLEVEL’,2,’OK BLEVEL’,3,’OK BLEVEL’,4,’OK BLEVEL’, 3        ’BLEVEL HIGH’) keterangan FROM  dba_indexes WHERE owner=’BOY’ 4        ORDER BY bleave;              INDEX_NAME                  BLEVEL KETERANGAN BUDVERPORT_ORG_FK_I      0   OK BLEVEL SKS_C006134     0   OK BLEVEL BUDVERPORT_BUDVERIORT2_UK 1   OK BLEVEL BUDVERPORT_PL_TITLE_FK_I               1   OK BLEVEL BUDVERPORT_BV_FK_I                  2   OK BLEVEL BUDVERPORT_DIRCTE_FK_I                 3   OK BLEVEL S_WAREHOUSE_ID_FK                4   OK BLEVEL TES_IDX_IDX 5   BLEVEL HIGH A1_PP       BLEVEL HIGH A1_UK                   BLEVEL HIGH S_ITEM_ORDID_ PRODID_UK                         BLEVEL HIGH

BLEVEL pada data dictionary DBA_INDEXES adalah B-Tree level atau branch level yang menunjukkan kedalaman atau level indeks dari node root. Level nol menunjukkan node root dan node leaf yang sama. Jika nilai blevel lebih dari empat maka direkomendasikan untuk me-rebuild indeks. Nilai blevel diperoleh setelah indeks dianalisa sehingga nilai blevel yang kosong atau keterangan BLEVEL HIGH menunjukkan indeks yang belum dianalisa. Untuk itu indeks tes_idx_idx dengan blevel 5 perlu di-rebuild dengan perintah alter index tes_idx_idx rebuild.  

b.      Rebuild Indeks Online

Oracle10g mendukung rebuild indeks dan pembuatan statistiknya secara online dengan perintah ALTER INDEX nama_idx REBUILD COMPUTE STATISTICS ONLINE. Pada versi terdahulu proses itu melibatkan statement ALTER INDEX nama_idx REBUILD ONLINE dan ALTER INDEX nama_idx REBUILD COMPUTE STATISTICS. Mulai Oracle10g, proses tadi dapat dilakukan pada indeks reverse key, function-based maupun indeks reguler dan IOT.Peningkatan itu memungkinkan user untuk tetap mengakses indeks sementara rebuild dan statistik indeks dibuat. Opsi online memperbolehkan operasi DML pada tabel atau partisi berlangsung sementara pembuatan indeks dan statistik dikerjakan. Setelah rebuild selesai, indeks yang lama di-drop. Jika opsi online tidak disertakan maka tabel akan dikunci hingga proses rebuild indeks berakhir. Jika digunakan opsi online nologging maka informasi redo tidak dihasilkan. 

7.      Keputusan Mengubah Indeks

Perbedaan nilai data kolom (cardinality) yang diindeks juga dapat menjadi acuan untuk keputusan me-rebuild indeks atau mengubah jenis indeks.

CARDINALITY INDEKS

SQL> ANALYZE INDEX boy.tes_idx_idx VALIDATE STRUCTURE;

Index Analyzed   

SQL> SELECT del_lf_rows *100/DECODE (lf_rows, 0,1,lf_rows)

2        PCT_DELETED,(lf_rows - distinct keys) *100/  

3        DECODE(lf_rows,0,1,lf_rows)  DISTINCTIVENESS FROM index_stats 4 WHERE NAME=’&index_name’;

Enter value for index_name: TES_IDX_IDX

Old 6:      WHERE NAME=’&index_name’  

New 6:      WHERE NAME=’TES_IDX_IDX’  

PCT_DELETED DISTINCTIVENESS       

16.7724777 910.9142073       

Kolom pct_deleted menunjukkan persentase leaf (index entry) yang telah dihapus dan masih belum diisi. Semakin banyak persentasenya, pohon indeks menjadi tidak balance. Sebagai acuan jika pct_deleted bernilai di atas 20 persen, indeks itu perlu di-rebuild. Namun angka di atas 10 persen juga dapat dijadikan dasar untuk me-rebuild indeks lebih sering. 

Kolom distictiveness menunjukkan seberapa sering suatu nilai kolom yang diindeks berulang. Misalnya jika suatu tabel memiliki 10000 row dan ada 9000 variasi nilai untuk kolom yang diindeks maka berdasarkan formula script di atas diperoleh hasil 10. Angka ini menunjukkan distribusi yang baik untuk indeks. Jika untuk 10000 row hanya terdapat variasi dua nilai data maka diperoleh hasil 99,98. Ini berarti hanya sedikit variasi nilai data terhadap seluruh yang ada pada kolom yang diindeks. Kolom ini bukan merupakan calon untuk proses rebuild indeks tetapi sebaiknya dibuatkan indeks bitmap. 

8.      Sumber informasi 

Keberadaan  indeks  dapat  diketahui  dengan  mengakses  data  dictionary DBA_IND_CLOUMNS berikut ini:

MENGAMBIL INFORMASI INDEKS

SQL> SELECT index_name, index_type, status FROM dba_indexes 2   WHERE owner=’BOY’; INDEX_NAME                     INDEX_TYPE           STATUS PEG_DEP_REVERSE    NORMAL/REV   VALID KAR_DEP_BITMAP    BITMAP   VALID KOTA_PENDUDUK_NDX   CLUSTER   VALID NAMA_IX                       FUNTION-BASED NORMAL VALID PK_PELATIHAN    IOT-TOP   VALID PROD_IDX     NORMAL   N/A SYS_C002976                   NORMAL               VALID SYS_I00000033455C00002$$        LOB            VALID TES_IDX_IDX                   NORMAL               VALID … SQL> SELECT index_name,table_name,column_name FROM dba_ind_columns 2    WHERE index_owner=’BOY’ ORDER BY table_name; INDEX_NAME                TABLE_NAME      COLUMN_NAME SYS_C002985              DAFKURSUS       SYS_NC0000600007$ SYS_C002986              DAFKURSUS       SYS_NC_OID$ SYS_IOT_TOP_33369         EMPSUS_TAB      NESTED_TABLE_ID SYS_IOT_TOP_33369         EMPSUS_TAB      NO PEG_DEP_REVERSE           PEGAWAI         DEPT_NO KAR_DEP_BITMAP            KARYAWAN  DEPT_NO SYS_C003094  PRODUKSI_RANGE NIK PROD_IDX    PRODUKSI_RANGE TGL TED_IDX_IDX              TES_IDX         A1 … SQL> SELECT o.object name FROM sys.dba_objects o WHERE  2        owner=‘BOY’ AND o.object_id IN (SELECT i.obj# FROM  3        sys.ind$ I WHERE BITAND(i.property,4)=4); OBJECT_NAME  PEG_DEP_REVERSE

9.      Partisi Indeks

Seperti  halnya  table,  indeks  dapat  juga  dipartisi.  Table  terpartisi  dapat menggunakan indeks terpatisi maupun indeks non-partisi. Demikian pula sebaliknya suatu table nonpartisi dapat menggunakan indeks terpartisi maupun indeks non-partisi.

a.      Indeks Global

Indeks global dapat dipartisi secara range dan cocok digunakan untuk mengakses row secara OLTP. Pada partisi indeks global terdapat batas partisi misalnya maxvalue. Penambahan partisi pada indeks global tidak bisa dilakukan karena partisi tertinggi telah dibatasi dengan maxvalue. Untuk tujuan itu, partisi harus dibagi melalui statement alter index…split partition.

MEMBUAT INDEKS GLOBAL

SQL> CREATE INDEX prod_idx ON produksi_range(tgl)  2        GLOBAL PARTITION BY RANGE(tgl)(PARTITION prod1_idx VALUES 3        LESS THAN( TO_DATE(’01-11-2003’,’DD-MM-YYYY’)),PARTITION  4     prod2_idx VALUES LESS THAN (MAXVALUE));     Index created

Setiap partisi indeks itu diberi nama dan disimpan pada tablespace indeks default. Agar pemutakhiran indeks global selalu dilakukan, sertakan klausa update global indexes pada setiap statement yang melibatkan operasi DDl pada partisi.  

b.      Indeks Lokal 

Partisi indeks local umumnya digunakan pada lingkungan data warehouse atau decision support system (DSS) dimana setiap partisi table berhubungan dengan satu partisi indeks lokal. Untuk lingkungan OLTP dapat digunakan indeks local yang unik dengan ketentuan bahwa partition key suatu table harus merupakan kunci bagi indeks tersebut.

Suatu partisi indeks lokal bersifat independen sehingga status unusable suatu indeks lokal tidak mempengaruhi status indeks lokal lainnya. Penambahan partisi pada indeks lokal tidak bisa dibuat secara eksplisit tetapi dihasilkan ketika suatu partisi baru ditambahkan. Demikian pula sebaliknya, drop partisi dari indeks lokal hanya dapat dilakukan ketika partisi tablenya di-drop. Indeks lokal dibentuk menurut struktur tabel dasarnya dan bersifat equipartitioned sehingga indeks lokal dipartisi menurut kolom yang sama dengan tabel dasarnya dan menggunakan jumlah partisi atau subpartisi yang sama pula.

10.  Mengidentifikasi Indeks Unused

Indeks mempercepat pembacaan data dengan mengambil rowid dari phon indeks untuk selanjutnya mengambil data di tabel. Jika pada table terjadi perubahan data dari kolom yang diindeks maka pohon indeks harus dimutakhirkan. Suatu table dapat memiliki beberapa indeks dan mungkin saja dari sekian indeks itu terdapat indeks yang sebenarnya tidak digunakan. 

Oracle10g dapat mendeteksi bilamana suatu indeks sedang digunakan atau tidak diperlukan berdasarkan waktu yang digunakan. Indeks yang tidak digunakan harus di-drop karena menambah overhead. Untuk mengamati indeks gunakan perintah alter indeks nama_indeks monitoring usage. Setelah anda yakin bahwa dalam selang waktu tertentu operasi yang melibatkan indeks pasti sudah dilakukan, hentikan pemantauan indeks dengan alter indeks nama_indeks nomonitoring usage. 

MEMANTAU INDEKS UNUSED

--Buat table  SQL> CREATE TABLE kanwil (kode NUMBER(5),nama VARCHAR2(10));  SQL> INSERT INTO kanwil VALUES (1,’KANWIL I’);  SQL> INSERT INTO kanwil VALUES (2,’KANWIL II’);  SQL> INSERT INTO kanwil VALUES (3,’KANWIL III’);  SQL> INSERT INTO kanwil VALUES (4,’KANWIL IV’);  SQL> COMMIT;  --Buat indeks primary key  SQL> ALTER TABLE kanwil ADD(CONSTRAINT kanwil_pk  2    PRIMARY KEY (kode)); Table altered. --Monitoring indeks belum bekerja SQL> SELECT index_name,monitoring,used,start_monitoring,  2    end_monitoring FROM v$object_usage; No row selected  --Aktifkan monitoring indeks  SQL> ALTER INDEX kanwil_pk MONITORING USAGE; Index altered.  --Monitoring indeks diaktifkan  SQL> SELECT index_name,monitoringused,start_monitoring,  2    end_monitoring FROM v$object_usage; INDEX_NAME  MONITORING USED START_MONITORING    END_MONITORING KANWIL_PK YES  NO 01/31/2004 01:30:16 --Buat table PLAN_TABLE jika belum ada. SQL> @C:\ora9i\rdbms\admin\ut1xplan.sql --Tracing rencana eksekusi SQL> SET AUTOTRACE ON EXPLAIN SQL> SELECT * FROM kanwil WHERE kode =1; KODE   NAMA 1    KANWIL I Execution Plan 0        SELECT STATEMENT Optimizer=CHOOSE 1        0    TABLE ACCESS (BY INDEX ROWID) OF ‘KANWIL’ 2        1    INDEX (UNIQUE SCAN) OF ‘KANWIL_PK’ (UNIQUE)  SQL> SET AUTOT OFF --Jalankan query  SQL> SELECT * FROM kanwil WHERE kode =1; SQL> SELECT index_name,monitoring,used,start_monitoring,  2    end_monitoring FROM v$object t_usage; INDEX_NAME MONITORING USED START_MONITORING     END_MONITORING KANWIL_PK YES  YES 01/31/2004 01:45:47 --Akhiri monitoring indeks  SQL> ALTER INDEX kanwil_pk NOMONITORING USAGE;  Indes altered.  SQL> SELECT index_name,monitoring,used,start_monitoring,  2    end_monitoring FROM v$object_usage; INDEX_NAME MONITORING USED START_MONITORING     END_MONITORING ---- KANWIL_PK NO    YES 01/31/2004 01:45:47 01/31/2004 01:47:07

View dictionary V$OBJECT_USAGE berisi informasi indeks yang dimonitor untuk mengetahui indeks yang telah digunakan. Jika kolom used bernilai yes maka indeks pernah digunakan selama selang waktu tertentu berdasarkan kolom start_monitoring dan end_monitoring. Kolom monitoring bernilai yes jikamonitoring indeks sedang  berlangsung dan berakhir setelah perintah monitor menghentikan monitor indeks  dijalankan. 

Untuk memantau indeks dalam ruang lingkup database maka perintah alter index…monitoring usage harus dilakukan bagi setiap nama indeks. Untuk itu perlu dibuat script yang menghasilkan statement tersebut dengan membaca data dictionary  DBA_INDEXES  baik  script  untuk  memulai  maupun menghentikan monitoring indeks.

MEMERIKSA INDEKS UNUSED SCOPE DATABASE

--Buat file script untuk start monitor indeks SQL> SPOOL D:\STARTMONITOR.SQL SQL> SELECT ’ALTER INDEX||OWNER||‘.’||INDEX_NAME||’MONITORING 2    USAGE;’FROM DBA INDEXES WHERE OWNER NOT IN (‘SYS’,’SYSTEM’); SQL> SPOOL D:\STOPMONITOR.SQL SQL> SELECT ’ALTER INDEX||OWNER||‘.’||INDEX_NAME||’NOMONITORING 2    USAGE;’FROM DBA INDEXES WHERE OWNER NOT IN (‘SYS’,’SYSTEM’); SQL> SPOOL OFF  --Edit file startmonitor.sql seperlunya dan jalankan monitor   SQL> @D:\startmonitor  --Edit file stopmonitor.sql seperlunya dan hentikan monitoring --indeks setelah periode tertentu  SQL> @D:\stopmonitor  --Periksa index yg tidak pernah digunakan selama periode SQL> SELECT d.owner, v.index_name FROM dba_indexes d,  2    v$object_usage v WHERE v.used=’NO AND  3    d.index_name=v.index_name; Setelah pemantauan berakhir query dictionaru DBA_INDEXES dan V$OBEJCT_USAGE untuk mengetahui nama indeks yang tidak pernah  digunakan melalui kolom unused dan view V$OBEJCT_USAGE.

11. VIEW 

View adalah sebuah virtual tabel yang dibangun dari satu atau beberapa tabel yang sudah ada, baik berdasarkan kondisi tertentu ataupun tidak. Secara fisik view tidak menyimpan record seperti pada tabel, tetapi ia menyimpan data berupa pointer yang menunjukkan ke record yang bersangkutan di dalam tabel.

Sumber data view dapat berasal dari table atau view lain. Mirip dengan table, Anda dapat  melakukan update, delete, dan insert pada view sehingga perubahan itu akan direfleksikan pada base tabelnya. Berbeda dengan table, view tidak menyimpan data, view hanya menyimpan definisi query pada data dictionary dan tidak memerlukan ruang penyimpanan data. Penerapan view dapat diaplikasikan pada situasi berikut :

 Membatasi akses sesuai otoritas user  

 Memudahkan pemahaman tehadap kolom penampung data yang mungkin berbeda dengan definisi kolom pada table dasar

 Menyederhanakan pandangan user terhadap data  

 Menangani data kompleks  

 Memudahkan  penggunaan  quey  yang  berulang  karena  disimpan sebagai stored query  

a.      Membuat View (CREATE VIEW)

Query :  

CREATE [or replace] [force] [noforce] VIEW nama_view  [(nama_field1, …)]  AS SUBQUERY [with check option]

or replace : mendefinisikan kembali view yang sudah ada noforce : view hanya akan dibuat jika tabel induk telah dibuat.  force : view dapat dibuat walau tabel induk belum dibuat.  

with check option : view akan menvalidasi data yang diinsert atau diupdate ke view

SQL> CREATE OR REPLACE VIEW mahasiswa_view (nim,nama,alamat) AS 2        SELECT nim,nama,alamat FROM mahasiswa  3        WHERE alamat != ,, ”with check option;

Contoh :  

b.      Memodifikasi View (ALTER VIEW)

Alter view digunakan untuk mengkompilasi ulang sebuah view.  Query :  

ALTER VIEW nama_view COMPILE;

Contoh :  

SQL> ALTER VIEW mahasiswa_view COMPILE:

c.       Menghapus View (DROP VIEW)

Query :  

DROP VIEW nama_view;

Contoh :  

SQL> DROP VIEW mahasiswa_view;

d.      View Read-Only 

View yang ditujukan hanya untuk dibaca saja, dibuat dengan menyertakan klausa WITH READ ONLY. Sebagai gambarannya, berikut ini didefinisikan objek table dan view yang diciptakan dalam satu kali transaksi melalui create schema.  

MEMBUAT VIEW READ ONLY

SQL> CREATE SCHEMA AUTHORIZATION boni  2        CREATE TABLE boy.dept ( 3        no NUMBER(4) NOT NULL PRIMARY KEY, 4        nama VARCHAR2 (20) NOT NULL 5        CREATE TABLE boy.karyawan ( 6   nik NUMBER(4) NOT NULL, 7        dept_no NUMBER(4) NOT NULL, 8        nama VARCHAR2(20), 9        PRIMARY KEY(nik), 10      FOREIGN KEY(dep_no) REFERENCES boy.dept(no) 11      ON DELETE CASCADE 12      CREATE VIEW dep_karyawan_rw AS SELECT a.nik,a.nama  13 AS "Nama Karyawan",a.dept_no,b.nama "Department" FROM 14      boy.karyawan a, boy.dept b WHERE a.dept_no = b.no 15      CREATE VIEW dep_karyawan_rro AS SELECT a.nik,a.nama AS "Nama  16      Karyawan",a.dept_no,b.nama "Department" FROM boy.karyawan a,  17  boy.dept b WHERE a.dept_no = b.no WITH READ ONLY; Schema created.                SQL> INSERT INTO boy.dept VALUES (100,'SDM');  SQL> INSERT INTO boy.dept VALUES (100,'Produksi'); SQL> INSERT INTO boy.dept VALUES (100,'Pemasaran'); SQL> INSERT INTO             boy.karyawan VALUES (100,'SDM'); SQL> INSERT INTO             boy.karyawan VALUES (100,'SDM');  SQL> INSERT INTO             boy.karyawan VALUES (100,'SDM'); SQL> INSERT INTO boy.karyawan VALUES (100,'SDM'); SQL> COMMIT  Commit complete.

View dep_karyawan_ro merupakan view read-only yang menggunakan table karyawan dan dept. View ini hanya dapat dibaca saja dan menghindari manipulasi data dengan delete, insert, atau update melalui view.

MEMANIPULASI PADA VIEW READ ONLY

SQL> S           ELECT nik,"Nama Karyawan","Departmen" FROM dep_karyawan_ro; NIK    Nama KARYAWAN Departmen 1        Isman           SDM 2        Nova            SDM 3        Donda           Produksi 4        Rino            Pemasaran    SQL> INSERT INTO             dep_karyawan_ro (nik,dept_no,"Nama Karyawan") 2    VALUES (5,2.0,'Didik'); ORA-017333: virtual column not allowed here SQL> SELECT            column_name, updatable FROM user_updatable_columns 2     WHERE table_name = 'DEP_KARYAWAN_RO'; COLUMN_NAME   NIK              NO Nama_karyawan NO DEPT_NO         NO Department      NO

View dep_karyawan_ro didefinisikan dengan empat kolom dimana referensi terhadap kolom karyawan.nama dialisakan menjadi “Departmen”. Ini berarti referensi terhadap kolom itu bersifat case sensitive karena dinyatakan dalam tanda petik “”, pada statement kedua tampak bahwa insert tidak dapat dilakukan ORA17333 dan itu dibuktikan melalui dictionary USER_UPDATABLE_COLUMNS yang melaporkan bahwa kolom-kolom view itu tidak dapat diupdate.

e. View Updatable  

Meskipun view tidak menyimpan data seperti table, perubahan terhadap base table dapat dilakukan melalui view seperti ditunjukkan pada script berikut : 

MANIPULASI PADA VIEW UPDATABLE

SQL> SELECT column_name,updatable FROM user_updatable_columns 2    WHERE table_name = 'DEP_KARYAWAN_RW'; COLUMN NAME     UPD NIK              YES Nama Karyawan      YES Dept_NO            YES Departmen        NO SQL> INSERT INTO dep_karyawan_rw (nik,dept_no,"Nama Karyawan") 2     VALUES (5,200,'Didik'); 1  ROW CREATED    SQL> SELECT            * FROM dep_karyawan_rw; NIK Nama Karyawan Dep_no   Departmen 1        Isaman          100  SDM 2        Nova            100  SDM 3        Donda           200  Produksi 4        Rino            300  Pemasaran 5        Didik           200  Produksi

View dep_karyawan_rw memiliki tiga kolom yang dapat diupdate dan melalui statement insert di atas, penambahan data pada view tersebut akan direfleksikan pada tabel karyawan.

12. Materialized View 

Materialized view (MV) merupakan objek schema yang berisi hasil query. Tabel-tabel yang digunakan pada query dapat berupa hasil, view atau MV lain yang disebut sebagai  tabel master (replikasi) atau tabel detail (data warehouse) dan informasinya tersedia  pada dictionary ALL_MVIEWS, DBA_MVIEWS, dan UESR_MVIEWS.

Materialized view atau snapshot ini digunakan pada database terdistribusi untuk membuat aplikasi dengan sinkronisasi data pada berbagai site maupun untuk data warehouse yang mempersiapkan dan menyimpan data agregat. MV meningkatkan kecepatan akses query melalui perkalkulasian join dan operasi agregat sebelum menjalankan dan menyimpan hasilnya pada database. Ketika query terhadap MV dilakukan, query optimizer akan mengetahui bilamana MV yang ada dapat digunakan dan segera mengakses MV, bukan ke table detail (query rewrite)  

a. Membuat Materialized View 

Privilege pembuatan MV haus di-grant secara langsung jadi tidak melalui role. Untuk membuat MV pada Schema user diperlukan priviledge system create materialized view dan create table atau create any table serta previlege system select any table. Sedangkan pembuatan MV pada schema user lain memerlukan previledge system create any materialized view. 

Untuk dapat membuat MV yang berisi summary jumlah penduduk setiap kota dapat dibuat dengan cara berikut ini :

MEMBUAT MATERIALIZED VIEW

SQL> CREATE MATERIALIZED VIEW ivana.SNAP_KOTA 2       BUILD IMMEDIATE REFRESH FORCE   3       OF DEMAND AS      4       SELECT k.namakota, COUNT(p.nama) jml_penduduk   5       FROM boy.kota k, boy.penduduk p  6    WHERE k.nokota=p.nokota 7    GROUP BY k.namakota; Materialize view created SQL> SELECT * FROM ivana.Snap_kota;   NAMAKOTA JML_PENDUDUK  Pontianak 700000  mempawak   1200000  sintang 500000

Perintah diatas menghasilkan data dalam MV (build immediate) yang menggunakan metode refresh force untuk memilih pemutakhiran data secara incremental atau komplit MV_sap_kot itu tidak bisa digunakan untuk query rewrite. Agar kemampuan itu tersedia maka opsi with query rewrite enabled harus dipilih dengan syarat owner memiliki privileged system query rewrite. Materialized view juga dapat dibuat melalui OEM Console  

Untuk memeriksa integritas struktur materialized view seperti halnya table,index, atau  cluster gunakan perintah analyze. Misalnya analyze table snap_kota validate structure cascade. Jika objek tidak valid, lakukan refresh MV secara komplit namun jika masih tidak valid lakukan drop dan buat ulang materiliazednya.  

Perintah alter materialized view snap_kota compile melakukan vaildasi MV secara eksplist dan digunakan pada situasi dimana telah terjadi perintah drop atau alter terhadap objek-objek yang digunakan oleh MV. Perintah alter ini juga digunakan  untuk  mengubah  karakteristik  MV  seprti  metode  dan mengaktifkan query rewrite.

b.      Materialized View Read/Write

MV dapat ditujukan hanya untuk dibaca saja sehingga menghindari perubahan MV dan data pada tabel master. Sebagai contoh daftar kota yang terdapat pada tabel master kota dapat dibuatkan MV-nya dengan nama snap_kota sehingga user dapat mengakses data melalui MV itu. 

MATERIALIZED VIEW UNTUK READ/WRITE

SQL> CREATE MATERIALIZED VIEW ivana.SNAP_KOTA BULID IMMEDIATE 2     REFRESH FORCE ON DEMAND AS SELECT *FROM boy.kota; Materialized view created.       SQL> INSERT INTO             ivana.SNAP_KOTA VALUES (157000,'Poso','Large'); ORA-01732:data manipulation operation not legal on      this view  SQL> DROP MATERIALIZED VIEW ivana.SNAP_KOTA; MATERIALIZED view dropped.     SQL> CREATE MATERIALIZED VIEW        ivana.SNAP_KOTA BULID IMMEDIATE 2        REFRESH FORCE ON DEMAND FOR UPDATE AS 3        SELECT * FROM boy.Kota; Materilaized view created. SQL> INSERT INTO             ivana.SNAP_KOTA VALUES (157000,'Poso','Large'); 1  ROW CREATED.

Untuk membuat MV yang dapat diupdate, tambahkan klausa for update padadefinisi MV.

c.       Refresh Data 

Data dalam MV diperbaharui jika ada perubahan pada tabel master. Pemutakhirannya dapat dilakukan secara incremental (fast refresh) atau memutakhirkan semua data (complete) atau memilih cara refresh yang tersedia (force). Jika MV menggunakan metode fast refresh, materialized view log akan merekam perubahan terhadap tabel master. MV dapat di refresh secara periodic (automatically on), sesuai keperluan (on demand) atau jika MV itu berada pada database yang sama dengan tabel masternya, refresh terjadi setelah commit (on commit).

Untuk dapat me-refresh MV secara on commit diperlukan privilege system on commit refresh atau dengan privilege objek on commit refresh pada setiap tabel master yang tidak dimiliki user. Jika tabel detail atau master mempunyai primary key, gunakan opsi primary key, sebaiknya gunakan metode refresh yang menggunakan rowed. 

d.      Materialized View Log 

Materialized view log adalah objek schema yang mencatat perubahan yang terjadi pada tabel sehingga memungkinkan tabel master diperbaharui secara incremental (fast refresh). Berikut ini dibuat MV log untuk data warehouse dengan fast refresh yang menggunakan rowed.  

MEMBUAT MATERIALIZED VIEW LOG

SQL> CREATE MATERIALIZED VIEW LOG ON boy.kota WITH SEQUENCE,  2     ROWID (nokota,namakota,kecamatan) INCLUDING NEW VALUES;   Materialized view log created.    SQL> CREATE MATERILIZED VIEW LOG ON      boy.penduduk WITH SEQUENCE, 2    ROWID (noktp, nama, nokota) INCLUDING NEW VALUES; Materialized view log created.

Untuk mendukung fast refresh pada MV tampak bahwa definisi MV log menyertakan klausa ROWID yang disertai including new values untuk merekam nilai data yang lama dan baru pada log.

e.      Data Warehouse 

Materialized view digunakan untuk organisasi data pada warehouse, misalnya dalam pembuatan ringkasan penduduk per kota melalui script berikut ini :  

MATERIALIZED VIEW UNTUK DATA WAREHOUSE

SQL> CREATE MATERIALIZED VIEW boy.jiwa_kota BUILD IMMEDIATE 2        REFRESH FAST ENABLE QUERY REWRITE AS SELECT k.namakota, 3        k.kecamatan, COUNT(p.nama) AS Jiwa FROM boy.kota k,  4        boy.penduduk p WHERE k.nokota = p.nokota GROUP BY   5      k.namakota, k.kecamatan;  Materialized view created.

Statement  diatas  membuat  MV  jiwa_kota  dengan  menghitung  jumlah penduduk dalam satu kota dan kecamatan dengan operasi join. MV segera dipopulasikan karena menggunakan metode bulid immediate dan tersedia untuk digunakan melalui query rewrite. Metode fast refresh bisa dilakukan karena MV log telah dibuat untuk tabel kota dan penduduk.

13. SEQUENCE 

Sequence digunakan untuk membangkitkan serangkaian nilai serial yang unik.  a. Membuat Sequence (CREATE SEQUENCE) 

Query :  

CREATE SEQUENCE nama_sequence [INCREMENT BY integer] [ START WITH integer][MAXVALUE integer|NOMAXVALUE] [ MINVALUE integer|NOMINVALUE][CYCLE|NOCYCLE][CHACE|NOCHACE]

INCREMENT BY	Berfungsi untuk mendefinisikan jumlah incrementasi setiap kali    terjadi penyisipan record.
START	WITH berfungsi untuk mendefisikan bilangan awal yang dibangkitkan.
NOMAXVALUE	Tidak ada batas maximum bilangan sequence yang digenerate.
MAXVALUE	Mendefinisikan maximum bilangan sequence yang digenerate.
CYCLE	Mendefinisikan bahwa jika bilangan sequence telah maximum, maka nilai akan diulang dari awal lagi
NOCYCLE	Tidak ada pengulangan bilangan bila telah sampai nilai maximum
CACHE	Bilangan sequence akan ditampung di buffer
NOCACHE	Bilangan sequence tidak akan ditampung di buffer.

 

Contoh : 

SQL> CREATE SEQUENCE seq_bulan INCREMENT BY 1 START WITH 1  2    MAXVALUE 12;

b.      Memodifikasi Sequence (ALTER SEQUENCE)  Query :  

ALTER SEQUENCE nama_sequence [INCREMENT BY integer] [START WITH integer][MAXVALUE integer|NOMAXVALUE]  [MINVALUE integer|NOMINVALUE][CYCLE|NOCYCLE][CHACE|NOCHACE]

Contoh : 

SQL> ALTER SEQUENCE seq_bulan INCREMENT BY 2;

c.       Menghapus Sequence (DROP SEQUENCE) 

Query :  

DROP SEQUENCE nama_sequence;

Contoh : 

DROP SEQUENCE        seq_bulan;

Tugas Pendahuluan :

1.      Sebutkan dan jelaskan keuntungan penggunaan Index.

2.      Apakah semakin banyak index yang kita pakai maka semakin baik pula pengaruhnya terhadap performansi DBMS? 

3.      Apakah syarat-syarat yang harus dipenuhi sehingga sebuah View dapat dinyatakan sebagai view updatable? 

4.      Buatlah contoh view updatable berdasarkan studi kasus yang ada di modul 1!

5.      Jelaskan fungsi/kegunaan dari sequence jika dikaitkan dengan suatu tabel!

Percobaan :

1.      Lakukan IMPORT atas database yang telah dibuat pada modul praktikum sebelumnya.

2.      Lakukan analisis terhadap seluruh tabel yang ada untuk menentukan index yang akan dibuat kemudian buatlah index pada tabel-tabel tersebut dan berikan alasan kenapa index tersebut harus dibuat.

3.      Buatlah view yang isinya merupakan hasil outer join dari tabel buku dan jenis dimana tabel buku menjadi acuannya.

4.      Lakukan analisis terhadap tabel-tabel yang ada untuk menentukan sequence yang akan dibuat kemudian buatlah sequence pada tabel-tabel tersebut dan berikan alasan kenapa sequence tersebut harus dibuat!