Virtual Columns Indexes on VC and Partitioning on VC
|
This article may require cleanup to meet OraFAQ's quality standards. Please improve this article if you can. |
|
Virtual Columns[edit]
The value of the virtual column is a derived expression.
–Can be derived from columns of the same table
–Can be derived from constants
–Can include SQL or user-defined PL/SQL functions
•You can create an index or partition on a virtual column
•Index Organized and External Tables can NOT have virtual columns
•You can NOT explicitly write to a virtual column
SQL> create table test 2 ( first_name varchar2(30), 3 salary number(6), 4 anual_sal generated always as (salary*12)); Table created.
Let see the describe for the table we just created with virtual column.
SQL> desc test Name Null? Type ----------------------------------------- -------- ------------ FIRST_NAME VARCHAR2(30) SALARY NUMBER(6) ANUAL_SAL NUMBER
I can't see any additional entry for virtual column.
SQL> insert into test
2 values
3 ('Michel',500);
insert into test *
ERROR at line 1:
ORA-00947: not enough values
As you noticed that I specify 2 values but table have 3 columns so for that we should explicitly mention the column name because we are not suppose to insert into virtual column.
SQL> insert into test
2 (first_name,salary)
3 values
4 ('Michel',500);
1 row created.
SQL> insert into test
2 (first_name,salary)
3 values
4 ('Ora',1500);
1 row created.
SQL> insert into test
2 (first_name,salary)
3 select first_name,salary from employees
4 where rownum<5;
4 rows created.
SQL> commit;
Commit complete.
SQL> select * from test;
FIRST_NAME SALARY ANUAL_SAL
---------- ---------- ----------
Michel 500 6000
Ora 1500 18000
Donald 2600 31200
Douglas 2600 31200
Jennifer 4400 52800
Michael 13000 156000
6 rows selected.
Indexes On Virtual Columns[edit]
As Oracle says we can create indexes on virtual columns let try to create one.
SQL> create index my_ind on test(anual_sal); Index created.
Let check whether oracle will use this index or not.
SQL> explain plan for 2 select * from test 3 where anual_sal=31200; Explained. SQL> select * from table (dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 43 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 43 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | MY_IND | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------
Oh yea Oracle works beautiful with indexes on virtual columns.The MY_IND is mine index name which I created on virtual column ANNUAL_SAL.
Now I’m going to check some other attributes for the virtual column.
SQL> alter table test drop column salary; alter table test drop column salary * ERROR at line 1: ORA-54031: column to be dropped is used in a virtual column expression
Awww I’m unable to drop the column salary because ANNUAL_SAL the virtual column based on this let 1st try to drop virtual column.
SQL> alter table test drop column anual_sal; Table altered. SQL> alter table test drop column salary; Table altered.
Now I’m again going to add salary column and then virtual column on the base of salary column.
SQL> desc test Name Null? Type ----------------------------------------- -------- ------------ FIRST_NAME VARCHAR2(30) SQL> alter table test add (salary number(6)); Table altered. SQL> alter table test add (anual_sal as (salary*12)); Table altered.
Partitions on Virtual Columns[edit]
SQL> drop table test ; Table dropped. SQL> purge tablespace users; Tablespace purged. SQL> create table test 2 (first_name varchar2(30), 3 salary number (6), 4 anual_sal generated always as (salary*12)) 5 partition by range (anual_sal) 6 ( 7 partition p1 values less than (15000)) 8 tablespace users; Table created.
Let check it in USER_TAB_PARTITIONS
SQL> select table_name,partition_name,tablespace_name 2 from user_tab_partitions 3* where table_name='TEST'; TABLE_NAME PARTITION_NAME TABLESPACE_NAME ----------- -------------- --------------- TEST P1 USERS
Let add another partition to existing table.
SQL> alter table test add partition p2 2 values less than (maxvalue); Table altered.
SQL> select table_name,partition_name,tablespace_name 2 from user_tab_partitions 3 where table_name='TEST'; TABLE_NAME PARTITION_NAME TABLESPACE_NAME ---------- -------------- --------------- TEST P1 USERS TEST P2 USERS
Let check the number of rows in each partition.
SQL> select table_name,partition_name,tablespace_name,num_rows 2 from user_tab_partitions 3 where table_name='TEST'; TABLE_NAME PAR TABLESPACE_NAME NUM_ROWS ---------- --- --------------- -------- TEST P1 USERS 0 TEST P2 USERS 0
Oh I should collect statistics on table TEST.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> grant execute on dbms_stats to hr;
Grant succeeded.
SQL> show user
USER is "HR"
SQL> exec dbms_stats.gather_table_stats('HR','TEST');
PL/SQL procedure successfully completed.
SQL> select table_name,partition_name,tablespace_name,num_rows
2 from user_tab_partitions
3 where table_name='TEST';
TABLE_NAME PAR TABLESPACE_NAME NUM_ROWS
---------- --- --------------- --------
TEST P1 USERS 1
TEST P2 USERS 107
Hope this topic would help you to create virtual columns indexes on virtual columns and partitioning on virtual columns.
