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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13750068/viewspace-2144144/，如需轉載，請註明出處，否則將追究法律責任。

Indexing on Virtual Columns

相關文章