#########################一.不可见索引##########################


--1.新建不可见索引表




create table t1
(
sid int not null ,
sname varchar2(10)
)
tablespace test;




--循环导入数据
declare
        maxrecords constant int:=100000;
        i int :=1;
    begin
        for i in 1..maxrecords loop
          insert into t1 values(i,'ocpyang');
        end loop;
    dbms_output.put_line(' 成功录入数据！ ');
    commit;
    end; 
/






exec dbms_stats.gather_table_stats(user,'T1');


SQL> set autotrace on
SQL> select * from t1 where sid=2001;


       SID SNAME
---------- ----------
      2001 ocpyang




执行计划
----------------------------------------------------------
Plan hash value: 3617692013


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |   103   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    13 |   103   (1)| 00:00:02 |
--------------------------------------------------------------------------


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


   1 - filter("SID"=2001)


Note
-----
   - SQL plan baseline "SQL_PLAN_d1c0fjvcgamm5dbd90e8e" used for this statement




统计信息
----------------------------------------------------------
          1  recursive calls
          1  db block gets
        272  consistent gets
          0  physical reads
          0  redo size
        598  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL>










create index index_01 on t1(sid) invisible;




SQL> select * from t1 where sid=2001;


       SID SNAME
---------- ----------
      2001 ocpyang




执行计划
----------------------------------------------------------
Plan hash value: 3617692013


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |   103   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    13 |   103   (1)| 00:00:02 |
--------------------------------------------------------------------------


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


   1 - filter("SID"=2001)


Note
-----
   - SQL plan baseline "SQL_PLAN_d1c0fjvcgamm5dbd90e8e" used for this statement




统计信息
----------------------------------------------------------
          1  recursive calls
          1  db block gets
        272  consistent gets
          0  physical reads
          0  redo size
        598  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL>




虽然新建了索引，但是数据库并没有使用.




select index_name,visibility from dba_indexes where visibility='INVISIBLE';


INDEX_NAME                     VISIBILIT
------------------------------ ---------
INDEX_01                       INVISIBLE






--2.让优化器使用不可见索引




show parameters invisible;


NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
optimizer_use_invisible_indexes      boolean     FALSE


默认情况下，优化器将不使用不可见索引.




--设置让优化器使用不可见索引


alter session set optimizer_use_invisible_indexes=true;  --对当前会话生效


alter system set optimizer_use_invisible_indexes=true;  --对系统所有会话生效


set autotrace on exp;






select /* index(index_01) */ * from t1 where sid=2001;




       SID SNAME
---------- ----------
      2001 ocpyang




执行计划
----------------------------------------------------------
Plan hash value: 1514635137


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


--------


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


me     |


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


--------


|   0 | SELECT STATEMENT    |     |     1 |    13 |     2   (0)| 00:00:01 |


|   1 |  TABLE ACCESS BY INDEX ROWID| T1  |     1 |    13 |     2   (0)| 00:00:01 |


|*  2 |   INDEX RANGE SCAN | INDEX_01 | 1 |  | 1 (0)| 00  :00:01 |  --表明使用索引


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






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


   2 - access("SID"=2001)




set autotrace off;