Oracle中的虚拟列索引-nosegment index

在生产环境中，创建索引时如果表比较大，可以会占用很多资源和需要分配存储。那么怎么能够确认创建的索引能够提升性能而且不占用资源和消耗空间？Oracle提供了一种技术，通过create index 的nosegment子句创建没有数据段的索引，因为无需创建物理索引索引避免消耗资源，任何类型的索引都可以创建虚拟索引。STA（SQL Tuning Advisor）和第三方工具应该都是通过创建虚拟索引的方式来调优。

虚拟索引使用场景：
需要创建一张大表的索引，又不想分配存储空间，确认优化器是否会选择该索引，那我们可以使用nosegment来创建索引并进行测试和查看执行计划等，如果确定这个索引能够提升性能，那么可以删除该索引，然后使用不含nosegment的子句创建索引重建该索引，或者创建不可见索引。（注：虚拟索引和不可见索引可以结和使用）

1、创建员工表
[oracle@db19do01 ~]$ sqlplus hr/Oracle_123@hrpdb
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 2 22:34:55 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Last Successful login time: Tue May 31 2022 20:52:29 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
HR@hrpdb >select count(*) from employees;
  COUNT(*)
----------
       107
Elapsed: 00:00:00.01
HR@hrpdb >alter session enable parallel dml;
Session altered.
Elapsed: 00:00:00.01
HR@hrpdb >alter session enable parallel ddl;
Session altered.
Elapsed: 00:00:00.00
HR@hrpdb >create /* parallel */ table employees2 as
  2  select * from employees;
Table created.
Elapsed: 00:00:00.12
2、在表employees2上通过常规方式给last_name列创建索引
HR@hrpdb >col segment_name for a20
HR@hrpdb >select segment_name,bytes from user_segments where segment_name='IDX_LASTNAME';
SEGMENT_NAME              BYTES
-------------------- ----------
IDX_LASTNAME              65536
Elapsed: 00:00:00.00
HR@hrpdb >
HR@hrpdb >col index_name for a20
HR@hrpdb >col index_type for a20
HR@hrpdb >select index_name,index_type from user_indexes where index_name='IDX_LASTNAME';
INDEX_NAME           INDEX_TYPE
-------------------- --------------------
IDX_LASTNAME         NORMAL
Elapsed: 00:00:00.00
HR@hrpdb >
3、删除索引IDX_LASTNAME
HR@hrpdb >drop index IDX_LASTNAME;
Index dropped.
Elapsed: 00:00:00.09
HR@hrpdb >
4、通过nosegment方式在employees2上创建索引
HR@hrpdb >create index idx_lastname on employees2(last_name) nosegment;
Index created.
Elapsed: 00:00:00.01
HR@hrpdb >
5、我们来查询idx_lastname该索引是否存在
因为已经创建的索引idx_lastname不占用空间索引user_segments不会看到该索引，因为优化器默认情况下也不会走该索引，user_indexes也没有该索引
HR@hrpdb >select segment_name,bytes from user_segments where segment_name='IDX_LASTNAME';
no rows selected
Elapsed: 00:00:00.00
HR@hrpdb >select index_name,index_type from user_indexes where index_name='IDX_LASTNAME';
no rows selected
查询user_objects和user_ind_columns来确认该索引存在
HR@hrpdb >col object_name for a20
HR@hrpdb >col object_type for a20
HR@hrpdb >select object_name, object_type from user_objects where object_name='IDX_LASTNAME';
OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
IDX_LASTNAME         INDEX
Elapsed: 00:00:00.00
HR@hrpdb >
6、通过在session级设置隐藏参数_use_nosegment_indexes让优化器识别该索引
HR@hrpdb >alter session set "_use_nosegment_indexes" = true;
查看执行计划
HR@hrpdb >explain plan for select LAST_NAME from EMPLOYEES2 where LAST_NAME='KING';
Explained.
Elapsed: 00:00:00.00
HR@hrpdb >select * from table(dbms_xplan.display);
![image.png](https://oss-emcsprod-public.modb.pro/image/editor/20221102-d8f61d07-8b0f-4a93-9475-03e490d28366.png)
7、使用虚拟索引测试完成，如果确认该索引可以提升性能，删除该索引重建即可
HR@hrpdb >drop index idx_lastname;
Index dropped.
Elapsed: 00:00:00.02
HR@hrpdb >create index idx_lastname on employees2(last_name);
Index created.
Elapsed: 00:00:00.01
HR@hrpdb >

小结：
通过create index nosegment子句创建虚拟索引，Oracle并不会像真正创建索引那样分配索引段，它只是创建一个索引的定义。不能像真正的索引那样，对虚拟索引的修执行修改或重建操作。这种类型的索引创建时，数据库只填充了少数几个数据字典表，也没有与索引相关的树。当想测试一个潜在索引的有效性时，我们尽可能创建不可见索引；当在大表上快速弄清楚优化器是否走该索引以及性能提升可以使用虚拟索引。