Oracle 11g Virtual columns

I read an article regarding the virtual columns in oracle 11g . i wish to share this article in  this blog.

Thanks to Arup Nanda for his excellent presentation.

Acme’s database contains a table called SALES, as you saw earlier. The table has the following structure:

SALES_ID NUMBER
CUST_ID NUMBER
SALES_AMT NUMBER

Some users want to add a column called SALE_CATEGORY, which identifies the type of the sale: LOW, MEDIUM, HIGH and ULTRA, depending on the amount of sale and the customer in question. This column will help them identify the records for appropriate action and routing to the concerned employee for handling. Here are the logic for values in the column:

If sale_amt is more than: And sale_amt is less than or equal to: Then sale_category is:
0 1000 LOW
10001 100000 MEDIUM
100001 1000000 HIGH
1000001 Unlimited ULTRA

Although this column is a crucial business requirement, the development team does not want to change the code to create the necessary logic. Of course, you could add a new column in the table called sale_category, and write a trigger to populate the column using the logic shown above—a fairly trivial exercise. But performance issues would arise due to context switching from and into the trigger code.

In Oracle Database 11g, you do not need to write a single line of code in any trigger. All you have to do instead is add a virtual column. Virtual columns offer the flexibility to add columns that convey business sense without adding any complexity or performance impact.

Here’s how you would create this table:

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  );

Note lines 6-7; the column is specified as “generated always as”, meaning the column values are generated at runtime, not stored as part of the table. That clause is followed by how the value is calculated in the elaborate CASE statement. Finally, in line 15,”virtual” is specified to reinforce the fact that this is a virtual column. Now, if you insert some records:

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.

the virtual column values are all populated as usual. Even though this column is not stored, you can refer to it as any other column in the table. You can even create indexes on it.

SQL> create index in_sales_cat on sales (sale_category);

Index created.

The result will be a 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

You can even partition on this column, as you saw in the Partitioning installment of this series. You can’t, however, enter a value for this column. If you try to, you won’t get far:

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

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