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 Q1_2005 TIMESTAMP' 2005-04-01 00:00:00'
ORDERS_VCOL Q2_2005 TIMESTAMP' 2005-07-01 00:00:00'
ORDERS_VCOL Q3_2005 TIMESTAMP' 2005-10-01 00:00:00'
ORDERS_VCOL Q4_2005 TIMESTAMP' 2006-01-01 00:00:00'
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(8,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
