Partitioning Table: Definisi dan Contoh

Filosofi partisi adalah memecah tabel ke dalam beberapa segment (partisi atau subpartisi), di mana tabel konvensional hanya mempunyai satu segment.

Misalkan kita punya tabel PENJUALAN dengan 8 juta records, kita ingin query data untuk quartal pertama tahun ini. Pada tabel konvensional (non partition), query akan men-scan keseluruhan 8 juta records data tersebut karena berada dalam 1 segment. Nah, kalau tabel itu dipartisi (by range untuk kolom tanggal penjualan) maka query akan men-scan khusus segment di mana data itu berada; tidak semua 8 juta records data itu di-scan, sehingga proses query lebih cepat.

Manfaat lain dari partitioning adalah tiap-tiap segment (partisi atau subpartisi) bisa ditaruh di tablespace yang berbeda, sehingga kita mendapat manfaat dari spreading (menyebar) tablespace, yaitu penyebaran I/O dan mengurangi resiko loss data karena tablespace corrupt.

Ada 3 metode utama partisi, dan ada 2 macam composite (gabungan):

  1. Range partitioning
  2. List partitioning
  3. Hash partitioning
  4. Composite range-list partitioning
  5. Composite range-hash partitioning

Misalkan saya punya tabel penjualan yang punya kolom no_invoice, tgl_jual, dan area.

CREATE TABLE penjualan
    ( no_invoice 	NUMBER,
      tgl_jual    	DATE,
      area 		varchar2(10));

Dalam artikel ini saya juga akan memberi contoh macam-macam partisi yang bisa dilakukan pada tabel penjualan tersebut.

Range Partition

Pada range partition, data dikelompokkan berdasarkan range (rentang) nilai yang kita tentukan. Range partition ini cocok digunakan pada kolom yang nilainya terdistribusi secara merata. Contoh yang paling sering adalah kolom tanggal.

Berikut ini contoh membuat table PENJUALAN dengan partisi range pada kolom tgl_jual (untuk menegaskan bahwa ini adalah contoh range partition, tabel saya beri nama PENJUALAN_RANGE):

CREATE TABLE penjualan_range
( no_invoice  NUMBER,
tgl_jual    DATE NOT NULL,
area varchar2(10))
PARTITION BY RANGE (tgl_jual)
(
PARTITION jual_kw1 VALUES LESS THAN (TO_DATE('01-APR-2008','DD-MON-YYYY')) TABLESPACE users,
PARTITION jual_kw2 VALUES LESS THAN (TO_DATE('01-JUL-2008','DD-MON-YYYY')) TABLESPACE users,
PARTITION jual_kw3 VALUES LESS THAN (TO_DATE('01-OCT-2008','DD-MON-YYYY')) TABLESPACE users,
PARTITION jual_kw4 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')) TABLESPACE users
);

Dalam contoh di atas, spesifikasi tablespace ada pada tiap segment (partisi). Yang seperti ini (spesifikasi tablespace pada tiap segment) biasanya dilakukan kalau tiap segment (partisi) ditempatkan pada tablespace yang berbeda. Karena keterbatasan tablespace, dalam contoh ini saya taruh dalam satu tablespace yang sama, yaitu tablespace USERS.

List Partition

Pada list partition, data dikelompokkan berdasarkan nilainya. Cocok untuk kolom yang variasi nilainya tidak banyak. Saya masih menggunakan contoh table penjualan. Yang cocok dengan list partition adalah kolom area.

Berikut ini contoh membuat table PENJUALAN dengan partisi list pada kolom area (untuk membedakan dengan contoh range partition, tabel saya beri nama PENJUALAN_LIST):

CREATE TABLE penjualan_list
( no_invoice  NUMBER,
tgl_jual    DATE NOT NULL,
area varchar2(10))
PARTITION BY LIST (area)
(
PARTITION daerah_barat VALUES ('JAKARTA','MEDAN','BANDUNG') ,
PARTITION daerah_timur VALUES ('SEMARANG','SURABAYA','MAKASAR')
) TABLESPACE users;

Definisi tablespace bisa didefinisikan di level partisi ataupun tabel. Dalam contoh di atas, karena semua partisi ditaruh di tablespace yang sama, maka definisi tablespace cukup ditaruh di definisi tabel (tidak perlu di tiap partisi).

Hash Partition

Jika kita ingin melakukan partisisi namun tidak cocok dengan RANGE ataupun LIST, maka kita bisa menggunakan HASH partition. Penentuan “nilai mana di taruh di partisi mana” itu diatur secara internal oleh Oracle (berdasarkan hash value).

Kenapa kita memaksakan memakai partisi sementara tidak cocok dengan RANGE ataupun LIST? Lha, ya itu tadi, kita ingin mendapat manfaat dari filosofi PARTITIONING di mana data disebar ke segment-segment yang berbeda.

Untuk tabel penjualan, kolom yang cocok dengan HASH partitioning adalah kolom no_invoice. Berikut ini contohnya (tabel saya beri nama PENJUALAN_HASH):

CREATE TABLE penjualan_hash
( no_invoice  NUMBER,
tgl_jual    DATE NOT NULL,
area varchar2(10))
PARTITION BY HASH (no_invoice)
PARTITIONS 4 tablespace users;

Secara otomatis Oracle akan membuat 4 partisi (sesuai dengan nilai dari parameter PARTITIONS). Dalam contoh di atas, definisi tablespace ditaruh di definisi tabel, yang berarti semua partisi (segment) di taruh di tablespace yang sama (tablespace USERS).

Kita bisa menaruh definisi tablespace di tiap partisi bila kita ingin tiap partisi (segment) disimpan di tablespace yang berbeda. Karena keterbatasan tablespace, dalam contoh ini semua partisi ditaruh di tablespace Users:

CREATE TABLE penjualan_hash
( no_invoice  NUMBER,
tgl_jual    DATE NOT NULL,
area varchar2(10))
PARTITION BY HASH (no_invoice)
PARTITIONS 4 STORE IN (users, users, users, users);

Composite range-list partition

Dengan karakteristik yang seperti itu, tabel PENJUALAN bisa kita partisi pada kolom TGL_JUAL dan AREA. Berikut ini contohnya (untuk membedakan dengan contoh-contoh sebelumnya, tabel saya beri nama PENJUALAN_RANGE_LIST):

CREATE TABLE PENJUALAN_RANGE_LIST
( no_invoice  NUMBER,
tgl_jual    DATE NOT NULL,
area varchar2(10))
PARTITION BY RANGE (tgl_jual)
SUBPARTITION BY LIST (area)
(
PARTITION jual_kw1 VALUES LESS THAN (TO_DATE('01-APR-2008','DD-MON-YYYY')) TABLESPACE users
(SUBPARTITION kw1_barat VALUES ('JAKARTA','MEDAN','BANDUNG'),
SUBPARTITION kw1_timur VALUES ('SEMARANG','SURABAYA','MAKASAR')
),
PARTITION jual_kw2 VALUES LESS THAN (TO_DATE('01-JUL-2008','DD-MON-YYYY')) TABLESPACE users
(SUBPARTITION kw2_barat VALUES ('JAKARTA','MEDAN','BANDUNG'),
SUBPARTITION kw2_timur VALUES ('SEMARANG','SURABAYA','MAKASAR')
),
PARTITION jual_kw3 VALUES LESS THAN (TO_DATE('01-OCT-2008','DD-MON-YYYY')) TABLESPACE users
(SUBPARTITION kw3_barat VALUES ('JAKARTA','MEDAN','BANDUNG'),
SUBPARTITION kw3_timur VALUES ('SEMARANG','SURABAYA','MAKASAR')
),
PARTITION jual_kw4 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))
(SUBPARTITION kw4_barat VALUES ('JAKARTA','MEDAN','BANDUNG') TABLESPACE users ,
SUBPARTITION kw4_timur VALUES ('SEMARANG','SURABAYA','MAKASAR')  TABLESPACE users
)
);

Dalam contoh di atas, definisi tablespace ada yang di level PARTISI dan ada yang level di SUBPARTISI.

Composite range-hash partition

Kitapun bisa mempartisi tabel PENJUALAN pada kolom TGL_JUAL dan NO_INVOICE. Berikut ini contohnya (untuk membedakan dengan contoh-contoh sebelumnya, tabel saya beri nama PENJUALAN_RANGE_HASH):

CREATE TABLE penjualan_range_hash
( no_invoice  NUMBER,
tgl_jual    DATE NOT NULL,
area varchar2(10))
PARTITION BY RANGE (tgl_jual)
SUBPARTITION BY HASH (no_invoice)
SUBPARTITIONS 4 STORE IN (users, users, users, users)
(
PARTITION jual_kw1 VALUES LESS THAN (TO_DATE('01-APR-2008','DD-MON-YYYY')),
PARTITION jual_kw2 VALUES LESS THAN (TO_DATE('01-JUL-2008','DD-MON-YYYY')),
PARTITION jual_kw3 VALUES LESS THAN (TO_DATE('01-OCT-2008','DD-MON-YYYY')),
PARTITION jual_kw4 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))
);

Urutan tablespace
Berikut ini hirarki untuk alokasi tablespace:

  1. Kalau di definisi SUBPARTITION tidak disebutkan tablespacenya, maka SUBPARTITION akan ditaruh di tablespace yang didefinisikan di PARTITION.
  2. Kalau di definisi PARTITION tidak disebutkan tablespacenya, maka PARTITION akan ditaruh di tablespace yang didefinisikan di TABLE
  3. Kalau tablespacenya tidak disebutkan (baik di level SUPPARTITION, PARTITION, maupun TABLE) maka, semua segment (baik SUPPARTITION maupun PARTITION) ditaruh di default tablespace dari user (schema) yang bersangkutan.

VIEW (data dictionary)

Beberapa contoh VIEW yang sering dipakai untuk melihat informasi PARTISI dan SUBPARTISI adalah DBA_TABLES, DBA_TAB_PARTITIONS, DBA_TAB_SUBPARTITIONS, DBA_INDEXES, DBA_IND_PARTITIONS, DBA_IND_SUBPARTITIONS.

Untuk melihat VIEW-VIEW yang lain, bisa query ke DICT:

SQL> select table_name from dict
where table_name like '%PARTITION%' order by table_name;

Referensi
Oracle® Database Administrator’s Guide 10g Release 2 (10.2)
Managing Partitioned Tables and Indexes

Silahkan baca yang ini juga:

This entry was posted in Administration, Performance Tuning and tagged , , , , , . Bookmark the permalink.

18 Responses to Partitioning Table: Definisi dan Contoh

  1. caterin says:

    Pak Rohmad,
    saya mau bertanya.. bagaimana caranya mencari tahu tabel / objek apa yang sering diakses oleh user?

    dan juga, sebenarnya apa saja syarat dari tabel yang harus dipartitioning? Jika saya memiliki 1000 buah tabel, tidak perlu saya partitioning semuanya kan? thanks

  2. rohmadne says:

    Syarat table yang bisa dipartitioning: saya kira artikel di atas sudah cukup detail.

    Untuk mengetahui object (table) yang sering diakses yang memakan resource besar, gunakan statpack (untuk versi 9i) atau awrstat (untuk versi 10g).

    Berikut ini cara menjalankan script awrstat:
    SQL> connect system
    SQL> @?/rdbms/admin/awrrpt.sql

  3. caterin says:

    pak, sebenarnya apa sih tujuan utama dari partitioning? yang dari hasil riset saya sih, untuk meningkatkan performance. Tapi saya pernah dengar bahwa ada pengalaman bahwa setelah tabel dipartisi, untuk tabel OLTP malah menjadi lebih lambat proses insert/update/deletenya. tapi untuk proses select memang menjadi lebih cepat. menurut bapak bagaimana? thanks

  4. rohmadne says:

    Tujuan utama partitioning adalah untuk mempercepat Query dan mempermudah administrasi. Misalnya untuk clean up; truncate bisa dilakukan per partisi, di mana truncate lebih cepat dibandingkan delete). Partitioning ini juga sering digunakan untuk backup atau migrasi data pakai transportable tablespace.

    Apakah transaksi (insert/update/delete) menjadi lambat setelah tabel dipartisi? Secara teori sih tidak. Justru untuk delete dan update bisa lebih cepat karena data langsung diquery ke partisinya (tentu saja bila WHERE CLAUSE-nya berdasarkan kolom partisi).

    Memang pada query, delete, dan update akan lebih lambat bilsa WHERE CLAUSE-nya bukan kolom partisi karena akan ada aktifitas tambahan yaitu MERGE antar partisi.

  5. caterin says:

    oh gitu.. oke.. makasih yah, pak.. btw,apa fungsi dari index partitioning?

  6. rohmadne says:

    Tujuan index partitioning adalah sama dengan table partitioning, terutama untuk meningkatkan performa query.

  7. Karsanto says:

    Pak mao tanya nih.

    Konsep partitioning ini sama nda sama konsep limit. Aku pengen pake untuk pagging di aplikasi. Bisa pake partitioning ga yah?

  8. rohmadne says:

    Konsep limit yang dimaksud itu limit yang mana?
    Apakah limit di OS, atau yang mana?

  9. Dony Wid says:

    Pak Rohmad,.. berkaitan dengan pertanyaan Caterin, apa benar insert ga lebih lambat ketimbang proses insert di normal table,.. krn harus memfilter record mana yg harus di insert ke partition mana,… apa lg bila kita menggunakan yg type composite,.. pernah membuat semacam benchmark-nya ga Pa…??
    klo memang ga berpengaruh, it will help a lot in my system…
    thanx before…

  10. pur says:

    pak mau tanya,apakah setelah tabel dipartisi,apakah insert data baru bisa ditabel yang telah dipartisi? atau masuk di tabel yang lama?
    apakah bisa mempartisi tabel, dan partisi tabel tersebut ditempatkan pada storage yang berbeda???

  11. aulia says:

    Pak,..jago oracle ya?bantuin aku dong pecahin soal2..baru pemula nih,…thanks

  12. teguh says:

    pak rohmad, kebetulan saya pake database oracle, kebetulan data transaksi dari thn 2007&2008 mau saya partisi, tapi bagaimana caranya membuat partisi apabila tabel2nya sudah terbentuk…terimakasih

  13. Defry says:

    Dear Pak Rohmad

    Saya mencoba membuat partition table seperti contoh yang Bapak berikan :

    CREATE TABLE penjualan_range
    ( no_invoice NUMBER,
    tgl_jual DATE NOT NULL,
    area varchar2(10))
    PARTITION BY RANGE (tgl_jual)
    (
    PARTITION jual_kw1 VALUES LESS THAN (TO_DATE(’01-APR-2008′,’DD-MON-YYYY’)) TABLESPACE users,
    PARTITION jual_kw2 VALUES LESS THAN (TO_DATE(’01-JUL-2008′,’DD-MON-YYYY’)) TABLESPACE users,
    PARTITION jual_kw3 VALUES LESS THAN (TO_DATE(’01-OCT-2008′,’DD-MON-YYYY’)) TABLESPACE users,
    PARTITION jual_kw4 VALUES LESS THAN (TO_DATE(’01-JAN-2009′,’DD-MON-YYYY’)) TABLESPACE users
    );

    Namun, ada error ORA-14004

    Mohon pencerahannya

    Regards,
    Defry

  14. eko says:

    jika select dengan where,langsung maka query akan men-scan khusus segment di mana data itu berada,jika select * tanpa filter apakah tetap seperti select * pada tabel biasa?discan dr record awal sampai record terakhir??

  15. deva ronald says:

    kayaknya sy harus ungrade dul ni pak

  16. helga says:

    om mau nanya nih, kalau mau bikin index (selain Primary Key) di table yg dipartisi, apakah perlu dipartisi juga indexnya, atau tidak?

    trims

  17. Mei says:

    Pak, saya mau nanya
    bagaimana caranya melakukan indexing pada tabel yang sudah dipartisi…
    memangnya apa pengaruhnya jika dibuat indexing pada tabel yang telah dipartisi.
    Bukankah dengan melakukan partisi sudah membuat performance query itu meningkat?
    mohon pencerahannya pak
    :)

  18. seto says:

    pak saya mau bertanya,
    apakah yang dimaksud dengan table space dan cara pemmbuatanya ?
    thanks y pak….

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>