Oracle Study---Oracle 11g 不可見索引案例

? ? ?Oracle 11g較之前的版本，推出了很多新功能，其中一項就是不可見索引（invisible index）。
? ? 從Oracle 11g開始，可以創建不可見索引（invisible index），優化器會忽略不可見的索引。
初始化參數optimizer_use_invisible_indexes決定是否使用invisible index，其默認值為false，即默認不使用invisible index。但如果在session級別或者system級別上將optimizer_use_invisible_indexes初始化參數設置為true，那么就可以使用invisible index。
與不可用索引不同，不可見索引在使用DML語句期間仍會得到維護。
Oracle引入不可見索引是有用途的，使索引不可見是使索引不可用或者刪除索引的一種替×××法。使用不可見的索引，可以完成如下操作：
1、在刪除索引之前測試對索引的刪除，是否會產生影響。
2、對應用程序的特定操作或模塊是使用臨時索引結構，這樣就不會影響整個應用程序。
當索引不可見時，優化器生成的執行計劃不會使用該索引。刪除索引時，可以先將索引修改為invisible，如果未發生性能下降的問題，則可以刪除該索引。在表上新建索引時，可以先創建一個最初不可見的索引，然后執行測試，看索引的效率怎么樣，最后確定是否使該索引可見，是否使用該索引。
可以查看dba_indexes、all_indexes、user_indexes視圖的visibility字段來確定該索引是visible還是invisible。

1、創建索引
11:47:59 SCOTT@ enmo> create index emp1_name_ind on emp1(ename) tablespace indx;
Index created.
Elapsed: 00:00:00.07
11:50:13 SCOTT@ enmo> exec dbms_stats.gather_table_stats(user,'emp1',cascade=>true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
2、設置索引不可見
11:53:47 SCOTT@ enmo> ALTER INDEX emp1_name_ind invisible;
Index altered.
Elapsed: 00:00:00.05
3、在執行查詢時，優化器沒有選擇索引
11:56:17 SCOTT@ enmo> SELECT * FROM EMP1 WHERE ENAME='SCOTT';
? ? ?EMPNO ENAME ? ? ?JOB ? ? ? ? ? ? ?MGR HIREDATE ? ? ? ? SAL ? ? ? COMM ? ? DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
? ? ? 7788 SCOTT ? ? ?ANALYST ? ? ? ? 7566 19-APR-87 ? ? ? 3000 ? ? ? ? ? ? ? ? ? ?20
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2226897347
--------------------------------------------------------------------------
| Id ?| Operation ? ? ? ? | Name | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
--------------------------------------------------------------------------
| ? 0 | SELECT STATEMENT ?| ? ? ?| ? ? 1 | ? ?38 | ? ? 3 ? (0)| 00:00:01 |
|* ?1 | ?TABLE ACCESS FULL| EMP1 | ? ? 1 | ? ?38 | ? ? 3 ? (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

? ?1 - filter("ENAME"='SCOTT')

Statistics
----------------------------------------------------------
? ? ? ? ? 1 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ? ? 4 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ? 863 ?bytes sent via SQL*Net to client
? ? ? ? 415 ?bytes received via SQL*Net from client
? ? ? ? ? 2 ?SQL*Net roundtrips to/from client
? ? ? ? ? 0 ?sorts (memory)
? ? ? ? ? 0 ?sorts (disk)
? ? ? ? ? 1 ?rows processed
4、設置優化參數
11:58:33 SYS@ enmo> grant alter session to scott;

Grant succeeded.

11:56:34 SCOTT@ enmo> alter session set optimizer_use_invisible_indexes=true;
Session altered.

Elapsed: 00:00:00.00

11:59:45 SCOTT@ enmo> SELECT * FROM EMP1 WHERE ENAME='SCOTT';
? ? ?EMPNO ENAME ? ? ?JOB ? ? ? ? ? ? ?MGR HIREDATE ? ? ? ? SAL ? ? ? COMM ? ? DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
? ? ? 7788 SCOTT ? ? ?ANALYST ? ? ? ? 7566 19-APR-87 ? ? ? 3000 ? ? ? ? ? ? ? ? ? ?20
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1963203073
---------------------------------------------------------------------------------------------
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? ? ? ? ?| Rows ?| Bytes | Cost (%CPU)| Time ? ? |
---------------------------------------------------------------------------------------------
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ? ? ? ? | ? ? 1 | ? ?38 | ? ? 2 ? (0)| 00:00:01 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| EMP1 ? ? ? ? ?| ? ? 1 | ? ?38 | ? ? 2 ? (0)| 00:00:01 |
|* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| EMP1_NAME_IND | ? ? 1 | ? ? ? | ? ? 1 ? (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

? ?2 - access("ENAME"='SCOTT')

Statistics
----------------------------------------------------------
? ? ? ? ? 1 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ? ? 3 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ? 867 ?bytes sent via SQL*Net to client
? ? ? ? 415 ?bytes received via SQL*Net from client
? ? ? ? ? 2 ?SQL*Net roundtrips to/from client
? ? ? ? ? 0 ?sorts (memory)
? ? ? ? ? 0 ?sorts (disk)
? ? ? ? ? 1 ?rows processed
不可見索引被優化器啟用
5、將索引變為可見，則優化器使用索引訪問
11:59:56 SCOTT@ enmo> alter index emp1_name_ind visible;
Index altered.

Elapsed: 00:00:00.06

12:00:46 SCOTT@ enmo> alter session set optimizer_use_invisible_indexes=false;
Session altered.

Elapsed: 00:00:00.00

12:00:58 SCOTT@ enmo> select * from emp1 where ename='SCOTT';

? ? ?EMPNO ENAME ? ? ?JOB ? ? ? ? ? ? ?MGR HIREDATE ? ? ? ? SAL ? ? ? COMM ? ? DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
? ? ? 7788 SCOTT ? ? ?ANALYST ? ? ? ? 7566 19-APR-87 ? ? ? 3000 ? ? ? ? ? ? ? ? ? ?20
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1963203073
---------------------------------------------------------------------------------------------
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? ? ? ? ?| Rows ?| Bytes | Cost (%CPU)| Time ? ? |
---------------------------------------------------------------------------------------------
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ? ? ? ? | ? ? 1 | ? ?38 | ? ? 2 ? (0)| 00:00:01 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| EMP1 ? ? ? ? ?| ? ? 1 | ? ?38 | ? ? 2 ? (0)| 00:00:01 |
|* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| EMP1_NAME_IND | ? ? 1 | ? ? ? | ? ? 1 ? (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
? ?2 - access("ENAME"='SCOTT')

Statistics
----------------------------------------------------------
? ? ? ? 189 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ? ?26 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ? 867 ?bytes sent via SQL*Net to client
? ? ? ? 415 ?bytes received via SQL*Net from client
? ? ? ? ? 2 ?SQL*Net roundtrips to/from client
? ? ? ? ? 6 ?sorts (memory)
? ? ? ? ? 0 ?sorts (disk)
? ? ? ? ? 1 ?rows processed
6、使用hints，不可見索引也不能被啟用?
12:01:10 SCOTT@ enmo> ALTER INDEX emp1_name_ind invisible;
Index altered.

12:02:49 SCOTT@ enmo> select table_name,index_name,leaf_blocks,status,VISIBILITY FROM USER_INDEXES
12:06:03 ? 2 ?where table_name='EMP1';
TABLE_NAME ? ? ? ? ? ? ? ? ? ? INDEX_NAME ? ? ? ? ? ? ? ? ? ? LEAF_BLOCKS STATUS ? VISIBILIT
------------------------------ ------------------------------ ----------- -------- ---------
EMP1 ? ? ? ? ? ? ? ? ? ? ? ? ? EMP1_NAME_IND ? ? ? ? ? ? ? ? ? ? ? ? ? ?1 VALID ? ?INVISIBLE

12:26:11 SCOTT@ enmo> ?select /*+ index(emp1 emp1_name_ind) */ * from emp1 where ename='SCOTT'
? ? ?EMPNO ENAME ? ? ?JOB ? ? ? ? ? ? ?MGR HIREDATE ? ? ? ? SAL ? ? ? COMM ? ? DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
? ? ? 7788 SCOTT ? ? ?ANALYST ? ? ? ? 7566 19-APR-87 ? ? ? 3000 ? ? ? ? ? ? ? ? ? ?20
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2226897347
--------------------------------------------------------------------------
| Id ?| Operation ? ? ? ? | Name | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
--------------------------------------------------------------------------
| ? 0 | SELECT STATEMENT ?| ? ? ?| ? ? 1 | ? ?38 | ? ? 3 ? (0)| 00:00:01 |
|* ?1 | ?TABLE ACCESS FULL| EMP1 | ? ? 1 | ? ?38 | ? ? 3 ? (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
? ?1 - filter("ENAME"='SCOTT')

Statistics
----------------------------------------------------------
? ? ? ? ? 0 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ? ? 4 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ? 863 ?bytes sent via SQL*Net to client
? ? ? ? 415 ?bytes received via SQL*Net from client
? ? ? ? ? 2 ?SQL*Net roundtrips to/from client
? ? ? ? ? 0 ?sorts (memory)
? ? ? ? ? 0 ?sorts (disk)
? ? ? ? ? 1 ?rows processed

轉載于:https://blog.51cto.com/tiany/1900355

總結

以上是生活随笔為你收集整理的Oracle Study---Oracle 11g 不可见索引案例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。