在Oracle中，虚拟列索引（Virtual Column Indexes）的作用是什么？

DB宝 2019-05-13

1095

♣题目部分在Oracle中，虚拟列索引（Virtual Column Indexes）的作用是什么？

     
♣答案部分

在Oracle 11g之前的版本中，如果需要使用表达式或者一些计算公式，那么需要创建数据库视图；如果需要在这个视图上使用索引，那么会在表上创建基于函数的索引。虚拟列是Oracle 11g新引入的一项技术，虚拟列是一个表达式，在运行时计算，不存储在数据库中，不能更新虚拟列的值。使用虚拟列有如下好处：
①　可以收集虚拟列的统计信息，为CBO提供一定的采样分析。
②　可以在WHERE后面使用虚拟列作为选择条件。
③　只在一处定义，不存储多余数据，查询时动态生成数据。
定义一个虚拟列的语法如下所示：
1column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]


下面给出虚拟列及虚拟列索引的语法示例：
1CREATE TABLE T_VC_20170518_LHR2(VC_ID   NUMBER, VC_COUNT  NUMBER, VC_ALL  GENERATED ALWAYS AS ( VC_ID + VC_COUNT ) VIRTUAL);
2CREATE INDEX VC_STATUS_IND2 ON T_VC_20170518_LHR2(VC_ALL);


虚拟列有如下特点：
①　在虚拟列的表达式中，可以包括同表的其它列、常量、SQL函数，甚至可以包括一些用户自定义的PL/SQL函数。
②　可以为虚拟列创建索引，称为虚拟列索引（实际上，Oracle为其创建的是函数索引），不能显式地为虚拟列创建函数索引。
③　可以通过视图DBA_TAB_COLS的DATA_DEFAULT列来查询虚拟列的表达式，当创建了虚拟列索引（其实是一种函数索引）后，在视图DBA_IND_EXPRESSIONS中不能查询索引列。
④　虚拟列的值并不是真实存在的，只有在用到时，才根据表达式计算出虚拟列的值，磁盘上并不存放虚拟列的数据。
⑤　由于虚拟列的值由Oracle根据表达式自动计算得出，所以，虚拟列可以用在SELECT，UPDATE，DELETE语句的WHERE条件中，但是不能用于DML语句。
⑥　可以基于虚拟列来做分区。
⑦　可以在虚拟列上创建约束（例如主键）。
⑧　只能在堆组织表（Heap-Organized Table，普通表）上创建虚拟列，不能在索引组织表、外部表、临时表上创建虚拟列。
⑨　虚拟列值只能是标量，不能是其它类型（例如集合、LOB、RAW等类型）。
⑩　可以把虚拟列当做分区关键字建立分区表，这是Oracle 11g的另一新特性称为虚拟列分区。
⑪　在已经创建的表中增加虚拟列时，若没有指定虚拟列的字段类型，则Oracle会根据关键字“GENERATED ALWAYS AS”后面的表达式计算的结果自动设置该字段的数据类型。
⑫　表达式中的所有列必须在同一张表。
⑬　虚拟列表达式不能使用其它虚拟列。
下面给出虚拟列及虚拟列索引的使用示例：
 1LHR@orclasm > CREATE TABLE T_VC_20170518_LHR(  
 2  2    VC_ID   NUMBER,  
 3  3    VC_COUNT  NUMBER,  
 4  4    VC_STATUS  GENERATED ALWAYS AS   
 5  5    (CASE  WHEN  VC_COUNT <= 100 THEN 'GETTING LOW'  
 6  6          WHEN  VC_COUNT > 100  THEN 'OKAY'  
 7  7    END)  
 8  8  );
 9Table created.
10LHR@orclasm > DESC T_VC_20170518_LHR
11 Name                                      Null?    Type
12 ----------------------------------------- -------- ----------------------------
13 VC_ID                                              NUMBER
14 VC_COUNT                                           NUMBER
15 VC_STATUS                                          VARCHAR2(11)
16LHR@orclasm > insert into T_VC_20170518_LHR (VC_ID, VC_COUNT) values (1, 100);
171 row created.
18LHR@orclasm > SELECT * FROM T_VC_20170518_LHR;
19     VC_ID   VC_COUNT VC_STATUS
20---------- ---------- -----------
21         1        100 GETTING LOW
22LHR@orclasm > ALTER TABLE T_VC_20170518_LHR ADD  VC_COMM GENERATED ALWAYS AS(VC_COUNT * 0.1) VIRTUAL; 
23Table altered.
24LHR@orclasm > DESC T_VC_20170518_LHR
25 Name                                      Null?    Type
26 ----------------------------------------- -------- ----------------------------
27 VC_ID                                              NUMBER
28 VC_COUNT                                           NUMBER
29 VC_STATUS                                          VARCHAR2(11)
30 VC_COMM                                            NUMBER
31LHR@orclasm > SELECT * FROM T_VC_20170518_LHR;
32     VC_ID   VC_COUNT VC_STATUS      VC_COMM
33---------- ---------- ----------- ----------
34         1        100 GETTING LOW         10
35LHR@orclasm > alter table T_VC_20170518_LHR modify VC_STATUS generated always as(  
36  2  case when VC_COUNT <= 50 then 'NEED MORE'  
37  3  when VC_COUNT >50 and VC_COUNT <=200 then 'GETTING LOW'  
38  4  when VC_COUNT > 200 then 'OKAY'  
39  5  end);
40Table altered.
41LHR@orclasm > update T_VC_20170518_LHR set VC_COUNT=100 where VC_STATUS='OKAY';
420 rows updated.
43LHR@orclasm > set line 9999
44LHR@orclasm > col data_type format a10
45LHR@orclasm > select table_name,COLUMN_NAME,data_type,data_default,VIRTUAL_COLUMN from user_tab_cols where table_name='T_VC_20170518_LHR';
46TABLE_NAME                     COLUMN_NAME                    DATA_TYPE  DATA_DEFAULT                                                                     VIR
47------------------------------ ------------------------------ ---------- -------------------------------------------------------------------------------- ---
48T_VC_20170518_LHR              VC_ID                          NUMBER                                                                                      NO
49T_VC_20170518_LHR              VC_COUNT                       NUMBER                                                                                      NO
50T_VC_20170518_LHR              VC_STATUS                      VARCHAR2   CASE  WHEN "VC_COUNT"<=50 THEN 'NEED MORE' WHEN ("VC_COUNT">50 AND "VC_COUNT"<=2 YES
51T_VC_20170518_LHR              VC_COMM                        NUMBER     "VC_COUNT"*0.1                                                                   YES
52T_VC_20170518_LHR              VC_COMM2                       NUMBER     "VC_COUNT"*0.2                                                                   YES
53LHR@orclasm > alter table T_VC_20170518_LHR add  (VC_comm2  as(VC_COUNT * 0.2) VIRTUAL);
54Table altered.
55LHR@orclasm > create index VC_STATUS_ind on T_VC_20170518_LHR(VC_STATUS);
56Index created.
57LHR@orclasm > select table_name,index_name,INDEX_TYPE from user_indexes where table_name='T_VC_20170518_LHR';
58TABLE_NAME                     INDEX_NAME                     INDEX_TYPE
59------------------------------ ------------------------------ ---------------------------
60T_VC_20170518_LHR              VC_STATUS_IND                  FUNCTION-BASED NORMAL

& 说明：
有关虚拟列的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2139258/

其它索引
应用程序域索引（Application Domain Indexes）是由用户为一个特定的应用程序域中的数据创建的。其物理索引不需要使用传统的索引结构，可以存储为Oracle数据库表，或外部文件。应用程序域索引是一个特定于应用程序的自定义索引。
对于B-Tree簇索引（B-Tree Cluster Indexes）和哈希聚簇索引（Hash Cluster Indexes）本书不再详解，对此感兴趣的读者可以参考相关的官方文档。



本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。