Exforsys

H I D E

Home arrow Technical Training arrow Oracle 11g Tutorial

Table of Contents

 Oracle 11g Virtual Columns
 Oracle 11g Virtual Columns: Featured Activities

Oracle 11g Virtual Columns: Featured Activities

Page 2 of 2
Author: Saurabh Gupta     Published on: 30th May 2011

Oracle 11g Virtual Columns

Oracle 11g Virtual Columns: Featured Activities

Indexing on Virtual Columns

Virtual columns can be indexed like any other non virtual columns. The index created is always a function based index. If the index is B-tree index, it is recognized as FUNCTION-BASED NORMAL. For bitmap indexes, it is recognized as FUNCTION-BASED BITMAP.

Example [9]

Sample Code
  1. SQL> CREATE INDEX IDX_VIRT ON T_VIRT (C);
  2.  
  3. INDEX created.
Copyright exforsys.com


The index IDX_VIRT will behave as a function based index. Oracle internally indexes the expression associated with the value.

Constraints can be imposed upon Virtual Columns

Similar to normal columns, all types of constraints can be imposed upon virtual columns. The example below uses the virtual column ‘C’ to be the primary key of the table T_VIRT.

Example [10]

Sample Code
  1. SQL> ALTER TABLE T_VIRT ADD PRIMARY KEY (C) </p><br>
  2. TABLE altered.
  3. SQL> SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS
  4. WHERE TABLE_NAME = 'T_VIRT';
  5.  
  6. CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
  7. -------------------- -------------------- --------------------
  8. SYS_C0012705 T_VIRT C
Copyright exforsys.com


Virtual Column can participate in Table partitioning

Virtual Column based partitioning can be applied with all the partitioning techniques. In the illustration below, a table T_VIRT_PART is created using Interval Partitioning Technique.

Example [11]

Sample Code
  1. CREATE TABLE T_VIRT_PART (A NUMBER,
  2. B NUMBER,
  3. C AS (A+B))
  4. PARTITION BY RANGE (C)
  5. INTERVAL (10000)
  6. (PARTITION P0 VALUES LESS THAN (20000),
  7. PARTITION P1 VALUES LESS THAN (30000),
  8. PARTITION P2 VALUES LESS THAN (40000))
  9. /
  10.  
  11. TABLE created.
Copyright exforsys.com


Test data is inserted into the partitions.

Sample Code
  1. SQL> INSERT INTO T_VIRT_PART(A,B) VALUES (&N,&M);
  2. Enter value FOR n: 3829
  3. Enter value FOR m: 8292
  4. old 1: INSERT INTO T_VIRT_PART(A,B) VALUES (&N,&M)
  5. new 1: INSERT INTO T_VIRT_PART(A,B) VALUES (3829,8292)
  6.  
  7. 1 row created.
  8.  
  9. SQL> /
  10. Enter value FOR n: 9201
  11. Enter value FOR m: 492
  12. old 1: INSERT INTO T_VIRT_PART(A,B) VALUES (&N,&M)
  13. new 1: INSERT INTO T_VIRT_PART(A,B) VALUES (9201,492)
  14.  
  15. 1 row created.
  16.  
  17. SQL> /
  18. Enter value FOR n: 1739
  19. Enter value FOR m: 8293
  20. old 1: INSERT INTO T_VIRT_PART(A,B) VALUES (&N,&M)
  21. new 1: INSERT INTO T_VIRT_PART(A,B) VALUES (1739,8293)
  22.  
  23. 1 row created.
  24.  
  25. SQL> /
  26. Enter value FOR n: 11392
  27. Enter value FOR m: 10982
  28. old 1: INSERT INTO T_VIRT_PART(A,B) VALUES (&N,&M)
  29. new 1: INSERT INTO T_VIRT_PART(A,B) VALUES (11392,10982)
  30.  
  31. 1 row created.
  32.  
  33. SQL> /
  34. Enter value FOR n: 23992
  35. Enter value FOR m: 11392
  36. old 1: INSERT INTO T_VIRT_PART(A,B) VALUES (&N,&M)
  37. new 1: INSERT INTO T_VIRT_PART(A,B) VALUES (23992,11392)
  38.  
  39. 1 row created.
  40.  
  41. SQL> /
  42. Enter value FOR n: 33820
  43. Enter value FOR m: 4292
  44. old 1: INSERT INTO T_VIRT_PART(A,B) VALUES (&N,&M)
  45. new 1: INSERT INTO T_VIRT_PART(A,B) VALUES (33820,4292)
  46. 1 row created.
  47.  
  48. SQL> SELECT * FROM T_VIRT_PART;
  49. A B C
  50. ---------- ---------- ----------
  51. 3829 8292 12121
  52. 9201 492 9693
  53. 1739 8293 10032
  54. 11392 10982 22374
  55. 23992 11392 35384
  56. 33820 4292 38112
  57.  
  58. 6 rows selected.
Copyright exforsys.com


Now, we can query the table with their partition names.

Sample Code
  1. SQL> SELECT * FROM T_VIRT_PART PARTITION (P0);
  2. A B C
  3. ---------- ---------- ----------
  4. 3829 8292 12121
  5. 9201 492 9693
  6. 1739 8293 10032
  7.  
  8.  
  9. SQL> SELECT * FROM T_VIRT_PART PARTITION (P1);
  10. A B C
  11. ---------- ---------- ----------
  12. 11392 10982 22374
  13.  
  14. SQL> SELECT * FROM T_VIRT_PART PARTITION (P2);
  15. A B C
  16. ---------- ---------- ----------
  17. 23992 11392 35384
  18. 33820 4292 38112
Copyright exforsys.com


To demonstrate Interval Partitioning, we shall insert a data whose value falls out of specified partition range (High_Value). In this situation, oracle server implicitly creates a partition suffixed by SYS_.

Example [12]

Sample Code
  1. SQL> INSERT INTO T_VIRT_PART (A,B) VALUES (37282,17282);
  2. 1 row created.
  3.  
  4. SQL> SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, INTERVAL FROM
  5. USER_TAB_PARTITIONS WHERE TABLE_NAME = 'T_VIRT_PART';
  6.  
  7. TABLE_NAME PARTITION_NAME HIGH_VALUE INT
  8. -------------------- -------------------- -------------------- ---
  9. T_VIRT_PART P0 20000 NO
  10. T_VIRT_PART P1 30000 NO
  11. T_VIRT_PART P2 40000 NO
  12. T_VIRT_PART SYS_P41 60000 YES
  13.  
  14.  
  15. SQL> SELECT * FROM T_VIRT_PART PARTITION (SYS_P41);
  16. A B C
  17. ---------- ---------- ----------
  18. 37282 17282 54564
Copyright exforsys.com


Ads

Virtual Columns: Advantages

The strongest benefit from virtual columns is that they are physically stored in database but their values do not occupy the disk space. Values are generated at runtime through the derivative expression and still, statistics can be generated for the column.

In addition, virtual column gives benefit of indexing, partitioning and constraint imposition.

Read Next: Oracle 11g Collections


 
This tutorial is part of a Oracle 11g Tutorial tutorial series. Read it from the beginning and learn yourself.

Oracle 11g Tutorial

  1. Oracle 11g Result Cache
  2. Compound Triggers in Oracle 11g
  3. Oracle 11g Invisible Index
  4. Oracle 11g Virtual Columns
  5. Oracle 11g Collections
  6. Oracle Functions and Procedures
  7. Oracle 11g Exception Handling
  8. Oracle 11g Read Only Tables
  9. Oracle 11g Subprogram Overloading
  10. Conditional compilation in Oracle PL/SQL
  11. Oracle Pragma
  12. UTL_FILE
  13. Manage Oracle dependencies
  14. Oracle 11g SecureFiles
  15. Oracle VPD implementation
  16. PL/Scope and PL/SQL Hierarchical Profiler
  17. Oracle Partitioning Overview
  18. Oracle Analytic Enhancements
  19. Oracle XML Storage
 

Comments

 

Ads

Exforsys Connect

Ads

Exforsys e-Newsletter

ebook