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