Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.