Oracle 11g [ Virtual Column ]

Semisal sebuah tabel SALES dengan struktur sebagai berikut

SALES_ID NUMBER
CUST_ID NUMBER
SALES_AMT NUMBER

User akan menambah kolom SALE_CATEGORY, yang akan mengidentifikasi tipe penjualan : LOW, MEDIUM, HIGH dan ULTRA tergantung jumlah penjualan
kolom ini akan membantu mengidentifikasi record untuk analisa. Berikut logika untuk nilai kolomnya

If sale_amt is more than: And sale_amt is less than or equal to: Then sale_category is:

Apabila kita tidak akan merubah code dengan menambah kolom sale_category di tabel, dan membuat triger untuk membuat logika yang kita perlukan,
Di Oracle 11g kita tidak perlu membuat sebuah triger. Yang kita perlukan adalah menambah sebuah virtual column. Virtual Column menawarkan
fleksibilitas untuk menambah kolom yang memenuhi permintaan bisnis tanpa menambah kompleksitas atau mempengaruhi performance
Berikut Querynya

SQL> create table sales
2 (
3 sales_id number,
4 cust_id number,
5 sales_amt number,
6 sale_category varchar2(6)
7 generated always as
8 (
9 case
10 when sales_amt <= 10000 then ‘LOW’
11 when sales_amt > 10000 and sales_amt <= 100000 then ‘MEDIUM’
12 when sales_amt > 100000 and sales_amt <= 1000000 then ‘HIGH’
13 else ‘ULTRA’
14 end
15 ) virtual
16 );

Kita perhatikan pada baris 6-7 , kolom dispesifikasikan sebagai “generated always as”, berarti nilai kolom digenerate dalam runtime(saat diakses), tidak
sebagai bagian dari table. Klausa tsb diikuti bagaimana nilai akan dihitung dengan memakai pernyataan CASE. Dan baris 15 berarti menunjukkan bahwa
itu adalah sebuah kolom virtual. Sekarang jika kita insert sebuah data.

SQL> insert into sales (sales_id, cust_id, sales_amt) values (1,1,100);

1 row created.

SQL> insert into sales (sales_id, cust_id, sales_amt) values (2,102,1500);

1 row created.

SQL>insert into sales (sales_id, cust_id, sales_amt) values (3,102,100000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from sales;

SALES_ID CUST_ID SALES_AMT SALE_C
———- ———- ———- ——
1 1 100 LOW
2 102 1500 LOW
3 102 100000 MEDIUM

3 rows selected.

Nilai dalam virtual column di perlakukan seperti biasa.Meskipun kolom ini tidak disimpan, kita bisa mengakses kolom ini seperti kolom lain ditabel
Kita jg dapat membuat index didalamnya

SQL> create index in_sales_cat on sales (sale_category);

Index created.

Hasilnya adalah berupa function-based index

SQL> select index_type
2 from user_indexes
3 where index_name = ‘IN_SALES_CAT’;

INDEX_TYPE
—————————
FUNCTION-BASED NORMAL

SQL> select column_expression
2 from user_ind_expressions
3 where index_name = ‘IN_SALES_CAT’;

COLUMN_EXPRESSION
——————————————————————————–
CASE WHEN “SALES_AMT”<=10000 THEN ‘LOW’ WHEN (“SALES_AMT”>10000 AND “SALES_AMT”
<=100000) THEN CASE WHEN “CUST_ID”<101 THEN ‘LOW’ WHEN (“CUST_ID”>=101 AND “CUS
T_ID”<=200) THEN ‘MEDIUM’ ELSE ‘MEDIUM’ END WHEN (“SALES_AMT”>100000 AND “SALES
_AMT”<=1000000) THEN CASE WHEN “CUST_ID”<101 THEN ‘MEDIUM’ WHEN (“CUST_ID”>=101
AND “CUST_ID”<=200) THEN ‘HIGH’ ELSE ‘ULTRA’ END ELSE ‘ULTRA’ END

Kita juga dapat melakukan partisi pada kolom ini, yang tidak dapat kita lakukan adalah memasukkan nilai ke kolom ini, semisal

insert into sales values (5,100,300,’HIGH’,'XX’)
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns