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 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;
