【DB笔试面试560】在Oracle中，虚拟列索引（Virtual Column Indexes）的作用是什么？
- 2019 年 10 月 10 日
- 筆記
♣
题目部分
在Oracle中，虚拟列索引（Virtual Column Indexes）的作用是什么？
♣
答案部分
在Oracle 11g之前的版本中，如果需要使用表达式或者一些计算公式，那么需要创建数据库视图；如果需要在这个视图上使用索引，那么会在表上创建基于函数的索引。虚拟列是Oracle 11g新引入的一项技术，虚拟列是一个表达式，在运行时计算，不存储在数据库中，不能更新虚拟列的值。使用虚拟列有如下好处：
① 可以收集虚拟列的统计信息，为CBO提供一定的采样分析。
② 可以在WHERE后面使用虚拟列作为选择条件。
③ 只在一处定义，不存储多余数据，查询时动态生成数据。
定义一个虚拟列的语法如下所示：
column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]
下面给出虚拟列及虚拟列索引的语法示例：
CREATE TABLE T_VC_20170518_LHR2(VC_ID NUMBER, VC_COUNT NUMBER, VC_ALL GENERATED ALWAYS AS ( VC_ID + VC_COUNT ) VIRTUAL); CREATE 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”后面的表达式计算的结果自动设置该字段的数据类型。
⑫ 表达式中的所有列必须在同一张表。
⑬ 虚拟列表达式不能使用其它虚拟列。
下面给出虚拟列及虚拟列索引的使用示例：
LHR@orclasm > CREATE TABLE T_VC_20170518_LHR( 2 VC_ID NUMBER, 3 VC_COUNT NUMBER, 4 VC_STATUS GENERATED ALWAYS AS 5 (CASE WHEN VC_COUNT <= 100 THEN 'GETTING LOW' 6 WHEN VC_COUNT > 100 THEN 'OKAY' 7 END) 8 ); Table created. LHR@orclasm > DESC T_VC_20170518_LHR Name Null? Type ----------------------------------------- -------- ---------------------------- VC_ID NUMBER VC_COUNT NUMBER VC_STATUS VARCHAR2(11) LHR@orclasm > insert into T_VC_20170518_LHR (VC_ID, VC_COUNT) values (1, 100); 1 row created. LHR@orclasm > SELECT * FROM T_VC_20170518_LHR; VC_ID VC_COUNT VC_STATUS ---------- ---------- ----------- 1 100 GETTING LOW LHR@orclasm > ALTER TABLE T_VC_20170518_LHR ADD VC_COMM GENERATED ALWAYS AS(VC_COUNT * 0.1) VIRTUAL; Table altered. LHR@orclasm > DESC T_VC_20170518_LHR Name Null? Type ----------------------------------------- -------- ---------------------------- VC_ID NUMBER VC_COUNT NUMBER VC_STATUS VARCHAR2(11) VC_COMM NUMBER LHR@orclasm > SELECT * FROM T_VC_20170518_LHR; VC_ID VC_COUNT VC_STATUS VC_COMM ---------- ---------- ----------- ---------- 1 100 GETTING LOW 10 LHR@orclasm > alter table T_VC_20170518_LHR modify VC_STATUS generated always as( 2 case when VC_COUNT <= 50 then 'NEED MORE' 3 when VC_COUNT >50 and VC_COUNT <=200 then 'GETTING LOW' 4 when VC_COUNT > 200 then 'OKAY' 5 end); Table altered. LHR@orclasm > update T_VC_20170518_LHR set VC_COUNT=100 where VC_STATUS='OKAY'; 0 rows updated. LHR@orclasm > set line 9999 LHR@orclasm > col data_type format a10 LHR@orclasm > select table_name,COLUMN_NAME,data_type,data_default,VIRTUAL_COLUMN from user_tab_cols where table_name='T_VC_20170518_LHR'; TABLE_NAME COLUMN_NAME DATA_TYPE DATA_DEFAULT VIR ------------------------------ ------------------------------ ---------- -------------------------------------------------------------------------------- --- T_VC_20170518_LHR VC_ID NUMBER NO T_VC_20170518_LHR VC_COUNT NUMBER NO T_VC_20170518_LHR VC_STATUS VARCHAR2 CASE WHEN "VC_COUNT"<=50 THEN 'NEED MORE' WHEN ("VC_COUNT">50 AND "VC_COUNT"<=2 YES T_VC_20170518_LHR VC_COMM NUMBER "VC_COUNT"*0.1 YES T_VC_20170518_LHR VC_COMM2 NUMBER "VC_COUNT"*0.2 YES LHR@orclasm > alter table T_VC_20170518_LHR add (VC_comm2 as(VC_COUNT * 0.2) VIRTUAL); Table altered. LHR@orclasm > create index VC_STATUS_ind on T_VC_20170518_LHR(VC_STATUS); Index created. LHR@orclasm > select table_name,index_name,INDEX_TYPE from user_indexes where table_name='T_VC_20170518_LHR'; TABLE_NAME INDEX_NAME INDEX_TYPE ------------------------------ ------------------------------ --------------------------- T_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程序员面试笔试宝典》，作者：李华荣。
