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