oracle使索引不可见,oracle 不可见索引

#########################一.不可见索引########################## --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;