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);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2318231875
--------------------------------------------------------------------------------
-| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |     8 |     1   (0)| 00:00:01|
|*  1 |  INDEX RANGE SCAN| IDX_LASTNAME |     1 |     8 |     1   (0)| 00:00:01|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
1 - access("LAST_NAME"='KING')
13 rows selected.
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
HR@hrpdb >
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並不會像真正建立索引那樣分配索引段，它只是建立一個索引

的定義。不能像真正的索引那樣，對虛擬索引的修執行修改或重建操作。這種型別的索引建立時，資料庫只填充了少數幾

個資料字典表，也沒有與索引相關的樹。當想測試一個潛在索引的有效性時，我們儘可能建立不可見索引；當在大表上快

速弄清楚最佳化器是否走該索引以及效能提升可以使用虛擬索引。

-the end-

-the end-