虚拟索引

桃扇骨 2022-04-02 12:10 174阅读 0赞

虚拟索引  
  虚拟索引(virtual index)是指没有创建对应的物理实体的索引。虚拟索引的目的，是在不必耗cpu,耗IO以及消耗大量存储空间去实际创建索引的情况，来判断一个索引是否能够对sql优化起到作用。  
例如下面一个查询

SQL> create table tt as select OWNER,OBJECT\_NAME,OBJECT\_ID from dba\_objects;

表已创建。

SQL> select count(\*) from tt;  
     72480

SQL>  explain plan for select \* from tt where object\_id=54;

已解释。  
SQL> select \* from table(dbms\_xplan.display());

PLAN\_TABLE\_OUTPUT  
\--------------------------------------------------------------------------------

Plan hash value: 264906180

\--------------------------------------------------------------------------  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
\--------------------------------------------------------------------------  
|   0 | SELECT STATEMENT  |      |     4 |   384 |   112   (1)| 00:00:02 |  
|\*  1 |  TABLE ACCESS FULL| TT   |     4 |   384 |   112   (1)| 00:00:02 |  
\--------------------------------------------------------------------------  
下面创建一个虚拟索引，来看看优化器是否会使用object\_id上的索引，以及优化器的成本将发生怎样的变化：

SQL> alter session set "\_use\_nosegment\_indexes"=TRUE;

会话已更改。

SQL> create index id\_index on tt(object\_id) nosegment;

索引已创建。  
create index语句的nosegment选项表明这个索引时"虚拟索引"没有与之关联的实际索引段，如果优化器认为这个索引有用，参数\_use\_nosegment\_indexes将指示数据库可以在执行计划中使用这些索引。下面我们来看看，如果真的创建此索引，它是否值得使用。

SQL> explain plan for select \* from tt where object\_id=54;

已解释。

SQL> /  
Plan hash value: 911556678

\--------------------------------------------------------------------------------  
\--------  
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti  
me     |  
\--------------------------------------------------------------------------------  
\--------  
|   0 | SELECT STATEMENT            |          |     4 |   384 |     5   (0)| 00  
:00:01 |  
|   1 |  TABLE ACCESS BY INDEX ROWID| TT       |     4 |   384 |     5   (0)| 00  
:00:01 |  
|\*  2 |   INDEX RANGE SCAN          | ID\_INDEX |   241 |       |     1   (0)| 00  
:00:01 |  
\--------------------------------------------------------------------------------  
\--------

Predicate Information (identified by operation id):  
\---------------------------------------------------

2 - access("OBJECT\_ID"=54)

Note  
\-----  
   - dynamic sampling used for this statement (level=2)

已选择18行。

SQL>  
本例中，dbms\_xplan显示，这个索引确实被认为有用，优化器估算的成本从112下降到5，如果查询经常出现我们将建议创建此索引

查询虚拟索引：  
SQL> SELECT index\_owner, index\_name  
  2  FROM dba\_ind\_columns  
  3  WHERE index\_name NOT LIKE 'BIN$%'  
  4  MINUS  
  5  SELECT owner, index\_name  
  6  FROM dba\_indexes;  
SYS                            ID\_INDEX  
删除虚拟索引  
SQL> drop index id\_index;

索引已删除。