Oracle Sequence Basic

Oracle sequence (selanjutnya saya sebut sequence saja, untuk mempermudah) merupakan object dalam database yang berfungsi sebagai sequence number generator, nilai yang dihasilkan ini biasanya disimpan dalam kolom yang berfungsi sebagai primary key pada suatu table. Fungsionalitas sequence ini hampir sama seperti kolom autonumber di Microsoft Access hanya saja cara pemakaiannya berbeda, nanti pada pertengahan artikel ini akan saya sertakan bagaimana cara memiliki kolom autonumber di Oracle seperti yang dimiliki Microsoft Access dengan menggunakan sequence.

Developer yang tidak mengetahui adanya sequence atau karena hal lain yang membatasinya untuk tidak menggunakan sequence, menggantikannya dengan membuat sendiri sequence number (selanjutnya saya sebut m-sequence, untuk membedakannya dengan Oracle sequence) layaknya Oracle sequence. Teknik ini direalisasikan dengan menyimpan nilai maksimum dari m-sequence pada sebuah table. Saat aplikasi meminta nilai untuk m-sequence berikutnya, nilai terakhir yang disimpan dalam table tersebut dibaca kemudian ditambah satu (atau bilangan tertentu) dan disimpan kembali dalam table tersebut. Pada mekanisme ini, jika setelah table yang menyimpan nilai terakhir m-sequence ini di-update dan kemudian transaksi tidak segera diakhiri (commit atau rollback) maka aplikasi lain yang juga membutuhkan nilai m-sequence ini harus menunggu sampai transaksi yang memegang row level lock tersebut berakhir. Untuk aplikasi single user mekanisme ini tidak menimbulkan masalah, sedangkan dalam situasi multiuser, aplikasi yang seperti ini dikatakan tidak scalable. Pada m-sequence developer juga harus menangani sendiri mekanisme locking untuk mencegah terjadinya lost update, karena jika mekanisme locking tidak diimplementasikan dengan benar maka terdapat kemungkinan dua session yang me-request m-sequence secara bersamaan memperoleh nilai sama.

Sequence memiliki karakteristik berbeda dengan m-sequence yaitu dapat digunakan oleh aplikasi lain tanpa menunggu transaksi yang meng-update sequence tersebut berakhir, dengan kata lain memperkecil terjadinya window serialization yang pada akhirnya meningkatkan scalability. Selain itu mekanisme locking-nya juga ditangani secara internal oleh Oracle, jadi kita tidak perlu khawatir memperoleh nilai kembar. Berikut saya sertakan contoh membuat sequence lengkap dengan parameternya.

create sequence myblog_seq
  start with 1
  increment by 1
  maxvalue 99999
  minvalue 1
  nocycle
  cache 5
  noorder;

Seperti halnya object lain dalam database dimiliki oleh seorang owner, sequence object ini juga tidak terkecuali, sequence di atas dimiliki oleh current user dimana sequence tersebut saya create. Seorang user harus memiliki privilege create sequence untuk dapat membuat sequence pada schema-nya, untuk membuat sequence pada schema yang dimiliki user lain dibutuhkan privilege create any sequence. Sequence di atas, ascending sequence, akan menghasilkan sequence number mulai dari start with (1) sampai maxvalue (10000), dan selisih dari tiap nilai yang dihasilkan adalah sebesar increment by (1). Jika aplikasi meminta sequence number berikutnya setelah sequence tersebut mencapai maxvalue maka ada dua kemungkinan yang terjadi tergantung parameter sequence tersebut cycle atau nocycle. Jika cycle setelah mencapai maxvalue, sequence tersebut akan kembali menghasilkan nilai mulai minvalue, sebaliknya jika nocycle maka pesan error ORA-08004 yang dihasilkan. Perlu diketahui bahwa parameter increment by juga bisa bernilai negatif artinya sequence yang dihasilkannyapun bersifat desceding sequence.

Persamaan dari m-sequence dan sequence adalah state dan karakteristik masing-masing sequence disimpan dalam sebuah table. Jika pada m-sequence table yang menyimpannya dibuat sendiri oleh developer maka pada sequence disimpan pada table dalam data dictionary yaitu sys.seq$. Untuk setiap perubahan dari sequence, Oracle akan meng-update nilai maksimum sequence tersebut pada table sys.seq$ dan segera meng-commit transaksinya tanpa harus mempengaruhi transaksi yang meminta sequence number tersebut. Mekanisme seperti ini dimungkinkan karena adanya autonomous transaction. Sebagai konsekuensinya jika transaksi yang meminta sequence number tadi me-rollback transaksinya, nilai maksimum sequence tersebut tidak berubah.

Oracle menyediakan mekanisme caching pada sequence dengan tujuan untuk mengurangi intensitas update pada table sys.seq$ yang pada akhirnya meningkatkan performance saat akses sequence tersebut.
Jika kita saat membuat sequence tidak menspesifikasikan parameter cache maka default-nya sequence tersebut akan di-cache dan nilai cache ini adalah 20 yang artinya ada 20 sequence number yang dipre-alokasikan dalam sequence cache, dan jika habis terpakai cache tersebut akan diisi kembali. Kalau kita menggunakan teknologi RAC maka tiap-tiap instance dalam RAC memiliki sequence cache-nya sendiri. Selain meningkatkan performance sequence, cache ini juga menimbulkan efek samping yaitu gap yang cukup signifikan pada sequence number yang dihasilkan. Sekali lagi saya tekankan secara eksplisit, sekalipun sequence tersebut tidak menggunakan cache (nocache) Oracle tidak menjamin tidak adanya gap, yang dijamin Oracle dengan sequence ini ialah tidak ada dua atau lebih user akan memperoleh hasil kembar dari sebuah sequence.

Selanjutnya saya akan membahas mengapa gap yang cukup signifikan ini bisa terjadi. Karena sequence cache disimpan dalam shared pool tepatnya libary cache maka saat instance shutdown, cache tersebut juga akan hilang. Sebab lainnya ialah, object dalam shared pool dimaintain dengan modified LRU algorithm termasuk di dalamnya sequence cache, dan jika sequence cache tersebut tidak sering diakses maka besar kemungkinan akan mengalami aged out. Berikut gambaran akses sequence myblog_seq yang telah saya create di atas dan di dalamnya terdapat aged out dan instance shutdown (Metalink Note:62002.1).

   app.    | number   | sequence |  cache   |
  access   | returned | current  |  current |
---------------------------------------------
1st access |     1    |     5    |     1    |
2nd access |     2    |     5    |     2    |
---------------------------------------------
               cache aged out
---------------------------------------------
3rd access |     6    |    10    |     6    |
4th access |     7    |    10    |     7    |
---------------------------------------------
              instance shutdown
---------------------------------------------
5th access |    11    |    15    |    11    |
6th access |    12    |    15    |    12    |
7th access |    13    |    15    |    13    |

Karena dua efek tersebut sequence number yang dihasilkan (lihat kolom 2) didalamnya terdapat gap tak beraturan. Khusus untuk kasus aged out, Oracle menyediakan solusi dengan menggunakan procedure keep dalam package dbms_shared_pool. Package ini perlu di-create terlebih dulu dengan menjalankan script dbmspool.sql sebagai user SYS, dan di Linux script ini berada di $ORACLE_HOME/rdbms/admin/. Berikut contoh penggunaan procedure ini pada sequence myblog_seq.

begin
  sys.dbms_shared_pool.keep('myblog_seq','q');
end;
/

Setelah eksekusi procedure ini sequence akan tetap di-cache dalam memory tanpa terpengaruh efek LRU. Untuk sequence yang tidak sering diakses, lebih baik tidak menggunakan cache dari pada harus menggunakan procedure keep untuk alokasi memory (sequence cache) yang jarang dipakai. Solusi ini tidak menyelesaikan masalah pada kasus instance shutdown. Kita dapat melihat sequence apa saja yang telah di-keep melalui perintah SQL berikut.

select owner, name
from v$db_object_cache
where type = 'SEQUENCE' and kept = 'YES';

OWNER      NAME
---------- --------------------
RK         MYBLOG_SEQ

Pada RAC, sequence dengan cache ini dapat menghasilkan nilai yang tidak berurutan dengan timing event request-nya. Contoh, user A pada instance rac1 meminta sequence number pada jam 07:00 selanjutnya user B pada instance rac2 juga meminta sequence number pada jam 07:01. Pada RAC, kejadian berikut mungkin terjadi dimana user A memperoleh nilai 11 untuk request-nya dan user B memperoleh nilai 7 untuk request-nya. Untuk menjaga keterurutan sequence ini Oracle menyediakan clausa order sebagai parameter saat developer membuat sequence, dengan demikian user A akan memperoleh nilai 7 and user B akan memperoleh nilai 8.

Apabila setelah di-create kita ingin mengubah nilai dari parameter sequence maka kita dapat menggunakan perintah alter sequence. Untuk meng-alter sequence yang berada di schema user lain dibutuhkan privilege alter any sequence.

Kombinasi parameter cache dan order ini berpengaruh terhadap performance sequence yang di-create. Saya tidak akan menjelaskan alasannya satu-persatu karena artikel ini hanya mengulas tentang basic sequence saja. Kombinasi dari kedua parameter tersebut dari yang terburuk sampai yang terbaik dari segi performance sebagai berikut :
1. nocache dan order
2. nocache dan noorder
3. cache dan order
4. cache dan noorder

Sampai sejauh ini saya belum menunjukkan bagaimana cara menggunakan sequence. Karena penggunaannya sangat mudah saya tidak akan membahasnya tersendiri, seperti yang telah saya singgung di awal artikel ini, saya akan menggunakan sequence untuk memperoleh fungsionalitas autonumber seperti pada Microsoft Access.

Misalkan saya mau menyimpan informasi judul setiap artikel blog ini ke dalam sebuah table dan menggunakan sequence number sebagai primary key-nya.

create table myblog_post
(
  myblog#    number(5),
  title        varchar2(255),
  constraints    myblog_post_pk primary key (myblog#)
);

Selanjutnya dengan menggunakan before insert trigger saya mengisi kolom myblog# dengan sequence number sehingga saat insert record ke table tersebut kita tidak perlu lagi mengisi nilai kolom ini. Kali ini saya menggunakan trigger hanya untuk menunjukkan bahwa Oracle juga bisa mengimplementasikan autonumber seperti pada Microsoft Access meskipun dengan usaha yang lebih banyak dan biasanya teknik ini digunakan oleh aplikasi yang bersifat database independent. Baca artikel saya sebelumnya tentang trigger sebelum menggunakan trigger dalam aplikasi yang anda buat. Script berikut telah saya test di Oracle 10.2.0.1 dan Oracle 11.1.0.6. Conditional compilation yang saya pakai di trigger mulai tersedia pada Oracle release 10.1.0.4.

create or replace trigger myblog_post_bir
before insert on myblog_post
for each row
begin
  $if dbms_db_version.ver_le_10 $then
    select myblog_seq.nextval into :new.myblog#
    from dual;
  $else
    :new.myblog# := myblog_seq.nextval;
  $end
end;
/

insert into myblog_post (title) values ('Intro');
insert into myblog_post (title) values ('Activate Extended SQL Trace');
insert into myblog_post (title) values ('Reading TKPROF Output');
insert into myblog_post (title) values ('Don''t trigger to code Trigger');
commit;

insert into myblog_post (title) values ('Oracle Sequence Basic');
rollback;

insert into myblog_post (title) values ('Oracle Sequence Basic');
commit;

select * from myblog_post;

   MYBLOG# TITLE
---------- ------------------------------
         1 Intro
         2 Activate Extended SQL Trace
         3 Reading TKPROF Output
         4 Don't trigger to code Trigger
         6 Oracle Sequence Basic

select myblog_seq.currval from dual;

   CURRVAL
----------
         6

Atribut nextval di atas digunakan untuk memperoleh sekaligus mengalokasikan nilai sequence selanjutnya. Untuk mengetahui nilai terakhir yang dialokasikan sequence tersebut pada suatu session digunakan atribut currval. Jika eksekusi atribut currval ini tidak pernah didahului dengan eksekusi nextval untuk setiap sequence pada suatu session, maka akan muncul pesan error ORA-08002. Berikut perilaku yang perlu diperhatikan saat menggunakan atribut currval. (Pada tabel berikut, jika ada dua select yang berada sebaris maka select milik session A dieksekusi terlebih dulu)

Session A                            | Session B
-------------------------------------|-------------------------------------
select myblog_seq.nextval from dual; | select myblog_seq.nextval from dual;
                                     |
   NEXTVAL                           |    NEXTVAL
----------                           | ----------
         7                           |          8
-------------------------------------|-------------------------------------
select myblog_seq.nextval from dual; | Idle
                                     |
   NEXTVAL                           |
----------                           |
         9                           |
-------------------------------------|-------------------------------------
select myblog_seq.nextval from dual; | Idle
                                     |
   NEXTVAL                           |
----------                           |
        10                           |
-------------------------------------|-------------------------------------
select myblog_seq.currval from dual; | select myblog_seq.currval from dual;
                                     |
   CURRVAL                           |    CURRVAL
----------                           | ----------
        10                           |          8
-------------------------------------|-------------------------------------
Idle                                 | select myblog_seq.nextval from dual;
                                     |
                                     |    NEXTVAL
                                     | ----------
                                     |         11

Untuk mengetahui nilai yang akan dihasilkan suatu sequence tanpa harus mengeksekusi nextval ternyata tidak semudah yang saya bayangkan, dapat dilihat pada table di atas nilai yang ditampilkan oleh atribut currval ini hanya mengacu pada masing-masing session dimana nilainya tidak konsisten dan tidak dapat digunakan sebagai patokan untuk nilai yang akan dihasilkan oleh suatu sequence. Oracle mengeksternalisasi sys.seg$ menjadi data dictionary view dba_sequences/all_sequences/user_sequences dan melalui kolom last_number kita dapat mengetahui berapa nilai yang akan kita peroleh jika kita mengeksekusi nextval.

select last_number from user_sequences
where sequence_name = 'MYBLOG_SEQ';

LAST_NUMBER
-----------
         16

select myblog_seq.nextval from dual;

   NEXTVAL
----------
        12

Ternyata view ini juga memberikan informasi yang tidak tepat karena saya menspesifikasikan cache 5 saat membuat sequence ini. Namun jika kita tidak menggunakan cache untuk sequence tersebut maka ketiga dictionary view tadi akan memberikan informasi yang tepat untuk nilai sequence selanjutnya. Saat ini cara yang saya ketahui untuk sequence yang di-cache ialah melalui fixed view yang dimiliki oleh user SYS yaitu v$_sequences.

select nextvalue from v$_sequences
where sequence_name = 'MYBLOG_SEQ';

 NEXTVALUE
----------
        13

select myblog_seq.nextval from dual;

   NEXTVAL
----------
        13

Tetapi untunglah kita dapat memakai sequence dalam aplikasi tanpa perlu mengetahui berapa nilai selanjutnya yang akan dihasilkan oleh sequence tersebut selama parameter yang kita isikan saat create sequence tersebut benar. Jadi bahasan terakhir ini hanya untuk memuaskan rasa keingintahuan saya saja tentang Oracle sequence.