Oracle 11g came up with concept of Virtual Column which is created with expression. Which does not stores data physically rather it calculates at run time and give us output when we fetch using query.

We saw different types of partitioning in previous posts, today we will see how we can partition the table using virtual column.

To see Types of Partitioning :

Let’s create a table using virtual partitioning :

SQL> CREATE TABLE TRAN_ALL_DETAILS
  2  (TRAN_ID NUMBER,
 ACCOUNT_NUMBER NUMBER(6),
 TYPE_OF_AC CHAR(2),
 AMOUNT NUMBER(8,2),
 PART_KEY AS (TYPE_OF_AC||SUBSTR(ACCOUNT_NUMBER,1,3)))
 PARTITION BY LIST (PART_KEY)
 (PARTITION P1 VALUES('SB100','SB200'),
  PARTITION P2 VALUES('SB300','CA100'));  3    4    5    6    7    8    9  

Table created.

SQL> 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.