索引虚拟oracle virtual index

这几周一直在研究索引虚拟之类的问题,现在正好有机会和大家共享一下.

    虚拟索引不耗消CPU以及IO,还有存储空间，用来判断创立的索引是不是可被用，面下举例说明：

    

SQL> create table tb1 as select owner,object_name,object_id from dba_objects;    --创立测试表

Table created.

SQL> select count(*) from tb1;

  COUNT(*)

----------

     50518

SQL> explain plan for select * from tb1 where object_id=108;           

Explained.

SQL> select * from table(dbms_xplan.display);               ---未创立虚拟索引前的执行计划

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3226679318

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     4 |   384 |    78   (2)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TB1  |     4 |   384 |    78   (2)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

   1 - filter("OBJECT_ID"=108)

Note

-----

   - dynamic sampling used for this statement

17 rows selected.

SQL> alter session set "_use_nosegment_indexes"=true;     --改修关相参数

Session altered.

SQL> create index tb1_object_id_idx on tb1(object_id) nosegment;    --创立虚拟索引

Index created.

SQL>  explain plan for select * from tb1 where object_id=108;      --创立虚拟索引侯的执行计划

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 959502086

-------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                   |     4 |   384 |     5   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TB1               |     4 |   384 |     5   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TB1_OBJECT_ID_IDX |   219 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------

   2 - access("OBJECT_ID"=108)

Note

-----

   - dynamic sampling used for this statement

18 rows selected.

    每日一道理
“多难兴才”曾一度被人定为规律。请看：屈原被放逐而作《离骚》；司马迁受宫刑而作《史记》；欧阳修两岁丧父笃学而成才；曹雪芹举家食粥而写出了不朽的《红楼梦》；越王勾践卧薪尝胆而雪洗国耻；韩信遭胯下辱而统率百万雄兵……他们都是在与逆境搏斗中成为伟人的！

    

    

注意：此索引在dba_indexes中是没法查到的，要需查询dba_ind_columns

    

SQL> create index TB1_OBJECT_ID_IDX on tb1(object_id);  --虽然是虚拟索引，但是还是不能创立同名的索引，要需除删改虚拟索引                                                                                                   --创立实在索引

create index TB1_OBJECT_ID_IDX on tb1(object_id)

             *

ERROR at line 1:

ORA-00955: name is already used by an existing object

    

    

SQL>  select index_owner,index_name from dba_ind_columns where lower(index_name)='tb1_object_id_idx';

INDEX_OWNER                    INDEX_NAME

------------------------------ ------------------------------

SYS                            TB1_OBJECT_ID_IDX

    

    

SQL> drop index tb1_object_id_idx;     ---找出并除删该虚拟索引

Index dropped.

文章结束给大家分享下程序员的一些笑话语录： 不会，Intel会维持高利润，也会维持竞争局面，国外的竞争不是打死对方的那种。你看日本有尼康，佳能，索尼，都做相机，大家都过得很滋润。别看一堆厂，其实真正控制的是后面的那几个财团——有些竞争对手，后面其实是一家人。