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.
SQL > col DATA_TYPE for A30
SQL > col DATA_DEFAULT for A30
SQL > SELECT column_name, data_type, data_length, data_default, virtual_column
2 FROM user_tab_cols
3 WHERE table_name = 'ORDERS_VCOL'
COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_DEFAULT VIR
ORDER_ID NUMBER 22 NO
ORDER_DATE TIMESTAMP (6) WITH LOCAL TIME ZONE 11 NO
ORDER_MODE VARCHAR2 8 NO
CUSTOMER_ID NUMBER 22 NO
ORDER_STATUS NUMBER 22 NO
ORDER_TOTAL NUMBER 22 NO
SALES_REP_ID NUMBER 22 NO
PROMOTION_ID NUMBER 22 NO
VCOL_GMT TIMESTAMP (6) 11 SYS_EXTRACT_UTC ("ORDER_DATE") YES
9 rows selected.
SQL > create index index_vcol on orders_vcol (VCOL_GMT)
Index created.
SQL >
SQL > select index_name,index_type,funcidx_status from user_indexes where table_name='ORDERS_VCOL'
INDEX_NAME INDEX_TYPE FUNCIDX_
-
ORDERS_VPK NORMAL
INDEX_VCOL FUNCTION-BASED NORMAL ENABLED
SQL > SQL >
SQL >
SQL > select * from user_ind_expressions where index_name='INDEX_VCOL'
INDEX_NAME TABLE_NAME COLUMN_EXPRESSION COLUMN_POSITION
- --
INDEX_VCOL ORDERS_VCOL SYS_EXTRACT_UTC ("ORDER_DATE") 1
SQL > drop index INDEX_VCOL
Index dropped.
SQL >
SQL >
SQL > create bitmap index INDEX_VCOL on ORDERS_VCOL (VCOL_GMT)
Create bitmap index INDEX_VCOL on ORDERS_VCOL (VCOL_GMT)
*
ERROR at line 1:
ORA-25122: Only LOCAL bitmap indexes are permitted on partitioned tables
SQL >
SQL > SELECT table_name, partition_name, high_value, num_rows
2 FROM user_tab_partitions
3 where table_name='ORDERS_VCOL'
4 ORDER BY table_name, partition_name
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-
ORDERS_VCOL Q1mm 2005 TIMESTAMP' 2005-04-01 00VOV 0000'
ORDERS_VCOL Q2mm 2005 TIMESTAMP' 2005-07-01 00VlV 0000'
ORDERS_VCOL Q3 ORDERS_VCOL 2005 TIMESTAMP' 2005-10-01 00VOV 0000'
ORDERS_VCOL Q40002005 TIMESTAMP' 2006-01-01 00VOV 0000'
SQL >
SQL >
SQL > drop table ORDERS_VCOL
Table dropped.
SQL > CREATE TABLE orders_vcol
2 (order_id NUMBER (12)
3 order_date TIMESTAMP WITH LOCAL TIME ZONE
4 order_mode VARCHAR2 (8)
5 customer_id NUMBER (6)
6 order_status NUMBER (2)
7 order_total NUMBER (8pm 2)
8 sales_rep_id NUMBER (6)
9 promotion_id NUMBER (6)
10 vcol_gmt TIMESTAMP AS (SYS_EXTRACT_UTC (order_date))
11 virtual
12 CONSTRAINT orders_vpk PRIMARY KEY (order_id)
13)
Table created.
SQL >
SQL > create bitmap index INDEX_VCOL on ORDERS_VCOL (VCOL_GMT)
Index created.
SQL > select index_name,index_type,funcidx_status from user_indexes where table_name='ORDERS_VCOL'
INDEX_NAME INDEX_TYPE FUNCIDX_
-
ORDERS_VPK NORMAL
INDEX_VCOL FUNCTION-BASED BITMAP ENABLED
SQL > select * from user_ind_expressions where index_name='INDEX_VCOL'
INDEX_NAME TABLE_NAME COLUMN_EXPRESSION COLUMN_POSITION
- --
INDEX_VCOL ORDERS_VCOL SYS_EXTRACT_UTC ("ORDER_DATE") 1
