Oracle虚拟索引的运用

  在做SQL调优的时候,有的时候需要加一个索引,测试下对性能提升有没有帮组,如果此时这张表非常大,建索引将会非常之麻烦,这种场景虚拟索引就该登场了。下面来做个试验:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> drop table test purge;
SQL> create table test as select * from user_objects;

SQL> select count(1),
           count(distinct object_name) c_oname,
           count(distinct object_type) s_otype
      from test;
  COUNT(1)    C_ONAME    S_OTYPE
---------- ---------- ----------
     11500      11430         14

SQL> create index ind_test_name on test(object_name) nosegment;

SQL> create index ind_test_otype on test(object_type) nosegment;

SQL> exec dbms_stats.gather_table_stats(user,‘test‘,cascade=>true);

SQL> --必须设置隐含参数”_use_nosegment_indexes”=true(默认为false)后,CBO才能使用虚拟索引

SQL> alter session set "_use_nosegment_indexes"=true;

SQL> set autotrace trace exp

SQL> select * from test where object_name=‘TEST‘;

执行计划
----------------------------------------------------------
Plan hash value: 3675505035
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    86 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |     1 |    86 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST_NAME |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_NAME"=‘TEST‘)

SQL> select * from test where object_type=‘TEST‘;
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   821 | 70606 |    36   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |   821 | 70606 |    36   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_TYPE"=‘TEST‘)

SQL> set autotrace off


SQL> select index_name from user_indexes s where s.table_name =‘TEST‘;
未选定行

SQL> col object_name format a15;
SQL> select object_name,object_id,object_type  from user_objects s
     where s.object_name in (‘IND_TEST_NAME‘, ‘IND_TEST_OTYPE‘);
OBJECT_NAME      OBJECT_ID OBJECT_TYPE
--------------- ---------- -------------------
IND_TEST_NAME       670666 INDEX
IND_TEST_OTYPE      670667 INDEX

SQL> select segment_name,s.bytes from user_segments s where s.segment_name
    in (‘IND_TEST_NAME‘, ‘IND_TEST_OTYPE‘);
未选定行


原理:加了虚拟索引后,同时开启_use_nosegment_indexes,CBO在评估执行计划的时候会把虚拟索引当做是一个真实的索引,有点欺骗CBO的意思。不能在user_indexes和user_segments中找到,但在user_objects可以找到。说明虚拟说只是加了数据字典,并没有产生实际的数据,那它也应该不能像真实的索引哪有操作,再做一个试验:

SQL> alter index ind_test_name rebuild;
alter index ind_test_name rebuild
*
第 1 行出现错误:
ORA-08114: 无法改变假索引

最后:虚拟索引只适合做性能调优,加了之后记得要删除。

Oracle虚拟索引的运用,布布扣,bubuko.com

Oracle虚拟索引的运用

上一篇:VS2013 编译程序时报无法加载PDB文件错误解决方案


下一篇:解决time命令输出信息的重定向问题