oracle虚拟索引的运用 - 数据库编程

在实际的调优工作，将执行计划中的大表的全表扫描变为索引扫描是一个重要的手段，当为大表做调优的时候，难免会建索引试试看，这个不是很好。最好是建一个虚拟索引，看看CBO的评估，这种做法更高效一些。下面我们来做个试验，然后用10046探究下其原理。

SQL> drop table test purge;

SQL> create table test as select * from dba_objects;

SQL> alter session set events '10046 trace name context forever ,level 12' ;
SQL> create index ind_test_id on test(object_id) nosegment;
SQL> alter session set events '10046 trace name context off' ;

SQL> alter session set "_use_nosegment_indexes"=true; --要设置一个隐含的参数

SQL> create index ind_test_id on test(object_id) nosegment;--建立虚拟索引

--这个并不是真实的执行计划，而是plan_table中的计划

SQL> explain plan for select * from test where object_id=1;
SQL> set linesize 1000
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 1064545891
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1416 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 8 | 1416 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST_ID | 200 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)

SQL> set autotrace traceonly
SQL> select * from test where object_id=1;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 1064545891
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1416 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 8 | 1416 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST_ID | 200 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
772 consistent gets
279 physical reads
0 redo size
992 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> set autotrace off
--以下看的是真实执行计划，从share_pool中获取的，显然是用不到索引。
SQL> alter session set statistics_level=all;
SQL> select * from test where object_id=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID btuhzhv88wwv3, child number 0
-------------------------------------
select * from test where object_id=1
Plan hash value: 1357081020
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------

oracle虚拟索引的运用(一)