Oracle nosegment 索引

我们知道Oracle数据库是用段来存储对象的，比如一个表就是一个段，一个分区就是一个段，一个索也是一个段。

而对于索引，最简单的存储形式就是Key和rowid，那么就一定会需要段空间；

在优化SQL的过程中，可能需要通过索引来评估执行计划是否合理。

但是如果表太大，或者业务系统太繁忙，没有办法直接创建索引， 就可以使用 nosegment 关键字来创建一个虚拟的索引。 

如果效果好， 删掉这个虚拟索引，再创建一个正经索引。 下面是我的实验效果：

1. 创建虚拟索引

CREATE INDEX IDX_PAR_SP_TAB_WTTIME ON C_ETL_PARAMETER_CONFIG(SPNAME,TABLENAME,WRITETIME,ENDDATE) NOSEGMENT ;

 

2. 生成执行计划

默认情况不会考虑虚拟索引生成执行计划， 需要修改隐藏参数

EXPLAIN PLAN FOR 
SELECT ENDDATE 
  FROM (SELECT ENDDATE
          FROM C_ETL_PARAMETER_CONFIG
         WHERE SPNAME = 'SP_C_CONTNO'
           AND TABLENAME = 'LCPOL'
         ORDER BY WRITETIME DESC)
 WHERE ROWNUM = 1;

Select * From Table(dbms_xplan.display());

Plan hash value: 169699969
 
--------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                        |     1 |     9 |    10  (10)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |                        |       |       |            |          |
|   2 |   VIEW                  |                        |    11 |    99 |    10  (10)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY|                        |    11 |   517 |    10  (10)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL   | C_ETL_PARAMETER_CONFIG |    11 |   517 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM=1)
   3 - filter(ROWNUM=1)
   4 - filter("TABLENAME"='LCPOL' AND "SPNAME"='SP_C_CONTNO')

 

 

3. 修改隐藏参数
修改后优化器就会考虑使用虚拟索引生成执行计划了，如果感觉效果还不错。则需要手动删掉这个索引。

ALTER SESSION SET "_use_nosegment_indexes"=TRUE; 

   
Plan hash value: 2165299408
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       |     1 |     9 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |                       |       |       |            |          |
|   2 |   VIEW                        |                       |     1 |     9 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN DESCENDING| IDX_PAR_SP_TAB_WTTIME |    11 |   517 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM=1)
   3 - access("SPNAME"='SP_C_CONTNO' AND "TABLENAME"='LCPOL')

 

4. 删索引之前可以看一下dba_indexes， 这种虚拟索引不记录到数据字典

select * from dba_indexes a where a.index_name = 'IDX_PAR_SP_TAB_WTTIME' ;

但是会记录到dba_objects 

select * from dba_objects b where b.OBJECT_NAME = 'IDX_PAR_SP_TAB_WTTIME' ;

 

如果好奇， 可以使用下面的SQL生成DDL，有nosegment关键字的就是虚拟索引

select dbms_metadata.get_ddl(object_type =>'INDEX' ,name => 'IDX_PAR_SP_TAB_WTTIME') FROM dual;