【INDEX】注意:不可見索引在表DML操作過程中依然被維護
在文章《【INDEX】11g中利用不可見索引降低索引維護時對系統的衝擊》(http://space.itpub.net/519536/viewspace-662238)中介紹了關於11g的新特性“不可見索引”,這項新技術給出了在不刪除索引的情況下來判斷有無索引對SQL語句執行計劃的影響,以便輔助判斷索引是否可以被真正的刪除。在使用這項技術的過程中,需要注意一點:雖然索引被標識為“不可見”,這僅僅指的是索引本身在生成SQL執行計劃時不會考慮使用相應索引,但是,索引本身還是會隨表的變化而進行進行維護,這裡給出實驗來說明。
1.演示不可見索引使用方法
1)當使用普通索引時的SQL執行計劃
sys@ora11g> conn sec/sec
Connected.
sec@ora11g> create table t as select * from all_objects;
Table created.
sec@ora11g> create index i_t on t(object_id);
Index created.
sec@ora11g> set autot traceonly exp
sec@ora11g> select * from t where OBJECT_ID = 666;
Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 158 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 158 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=666)
Note
-----
- dynamic sampling used for this statement (level=2)
可見,此時SQL語句使用的是I_T這個索引構造的SQL執行計劃。
2)將索引調整為不可見索引後檢視SQL語句執行計劃
sec@ora11g> alter index i_t invisible;
Index altered.
sec@ora11g> select * from t where OBJECT_ID = 666;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 1738 | 285 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 11 | 1738 | 285 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=666)
Note
-----
- dynamic sampling used for this statement (level=2)
此時,雖然索引依然存在,但是構造SQL語句的執行計劃的時候不再考慮索引,這裡使用的是T表全表掃描方式獲取資料。
2.檢視索引的使用情況
sec@ora11g> analyze index I_T validate structure;
Index analyzed.
sec@ora11g> set autot off
sec@ora11g> select height, lf_blks, br_blks, btree_space from index_stats;
HEIGHT LF_BLKS BR_BLKS BTREE_SPACE
---------- ---------- ---------- -----------
2 158 1 1271396
此時索引使用了158個葉子塊。
3.向T表插入資料
sec@ora11g> insert into t select * from all_objects;
71463 rows created.
這裡先不提交事務。
4.重新獲取索引的使用情況
sec@ora11g> analyze index I_T validate structure;
Index analyzed.
sec@ora11g> select height, lf_blks, br_blks, btree_space from index_stats;
HEIGHT LF_BLKS BR_BLKS BTREE_SPACE
---------- ---------- ---------- -----------
2 321 1 2574744
可見,此時索引佔用了321個葉子塊,幾乎是原先的的2倍。
5.回滾事務後再次查詢索引使用情況
sec@ora11g> rollback;
Rollback complete.
sec@ora11g> analyze index I_T validate structure;
Index analyzed.
sec@ora11g> select height, lf_blks, br_blks, btree_space from index_stats;
HEIGHT LF_BLKS BR_BLKS BTREE_SPACE
---------- ---------- ---------- -----------
2 321 1 2574744
顯然,曾經被分配的索引塊和空間沒有被釋放(這與索引特點相關)。
6.小結
本文透過實驗給出了在表內容發生變化的過程中,不可見索引是隨著表的內容變化而變化的。這一點在使用不可見索引的過程中一定要多加註意。當使用不可見索引技術判斷一個索引的確不再需要的時候,請儘快找到合適的維護視窗將其刪除掉,否則會因多餘的索引導致DML操作效能低下。
Good luck.
secooler
11.10.28
-- The End --
1.演示不可見索引使用方法
1)當使用普通索引時的SQL執行計劃
sys@ora11g> conn sec/sec
Connected.
sec@ora11g> create table t as select * from all_objects;
Table created.
sec@ora11g> create index i_t on t(object_id);
Index created.
sec@ora11g> set autot traceonly exp
sec@ora11g> select * from t where OBJECT_ID = 666;
Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 158 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 158 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=666)
Note
-----
- dynamic sampling used for this statement (level=2)
可見,此時SQL語句使用的是I_T這個索引構造的SQL執行計劃。
2)將索引調整為不可見索引後檢視SQL語句執行計劃
sec@ora11g> alter index i_t invisible;
Index altered.
sec@ora11g> select * from t where OBJECT_ID = 666;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 1738 | 285 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 11 | 1738 | 285 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=666)
Note
-----
- dynamic sampling used for this statement (level=2)
此時,雖然索引依然存在,但是構造SQL語句的執行計劃的時候不再考慮索引,這裡使用的是T表全表掃描方式獲取資料。
2.檢視索引的使用情況
sec@ora11g> analyze index I_T validate structure;
Index analyzed.
sec@ora11g> set autot off
sec@ora11g> select height, lf_blks, br_blks, btree_space from index_stats;
HEIGHT LF_BLKS BR_BLKS BTREE_SPACE
---------- ---------- ---------- -----------
2 158 1 1271396
此時索引使用了158個葉子塊。
3.向T表插入資料
sec@ora11g> insert into t select * from all_objects;
71463 rows created.
這裡先不提交事務。
4.重新獲取索引的使用情況
sec@ora11g> analyze index I_T validate structure;
Index analyzed.
sec@ora11g> select height, lf_blks, br_blks, btree_space from index_stats;
HEIGHT LF_BLKS BR_BLKS BTREE_SPACE
---------- ---------- ---------- -----------
2 321 1 2574744
可見,此時索引佔用了321個葉子塊,幾乎是原先的的2倍。
5.回滾事務後再次查詢索引使用情況
sec@ora11g> rollback;
Rollback complete.
sec@ora11g> analyze index I_T validate structure;
Index analyzed.
sec@ora11g> select height, lf_blks, br_blks, btree_space from index_stats;
HEIGHT LF_BLKS BR_BLKS BTREE_SPACE
---------- ---------- ---------- -----------
2 321 1 2574744
顯然,曾經被分配的索引塊和空間沒有被釋放(這與索引特點相關)。
6.小結
本文透過實驗給出了在表內容發生變化的過程中,不可見索引是隨著表的內容變化而變化的。這一點在使用不可見索引的過程中一定要多加註意。當使用不可見索引技術判斷一個索引的確不再需要的時候,請儘快找到合適的維護視窗將其刪除掉,否則會因多餘的索引導致DML操作效能低下。
Good luck.
secooler
11.10.28
-- The End --
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/519536/viewspace-709886/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 不可見索引在表DML操作過程中依然被維護索引
- 不可見索引(invisible index)在表DML操作過程中依然被維護索引Index
- 【INDEX】11g中利用不可見索引降低索引維護時對系統的衝擊Index索引
- Oracle資料庫中的不可見索引 invisible indexOracle資料庫索引Index
- oracle dml與索引index(一)Oracle索引Index
- 11g新特性: 索引不可見(Index Invisible)索引Index
- update表中index索引列對原索引條目做什麼操作?Index索引
- 外來鍵沒有索引哪些DML操作會被阻塞索引
- 在範圍分割槽表上分割槽維護操作對索引狀態的影響索引
- 有關Oracle表分割槽進行(DML)維護後對索引的影響的分析Oracle索引
- 在範圍分割槽表上分割槽維護操作對索引狀態的影響(1)索引
- Oracle之不可見索引Oracle索引
- 【INDEX】索引失效或者不可用 UNUSABLEIndex索引
- 8.0新特性-不可見索引索引
- oracle分割槽及分割槽索引partition_partition index_維護(一)Oracle索引Index
- oracle分割槽及分割槽索引partition_partition index_維護(二)Oracle索引Index
- oracle分割槽及分割槽索引partition_partition index_維護(三)Oracle索引Index
- oracle分割槽及分割槽索引partition_partition index_維護(四)Oracle索引Index
- Oracle 索引的維護Oracle索引
- Sql Server去除表中的不可見字元SQLServer字元
- 資料庫維護常用操作命令1-表操作資料庫
- 在範圍分割槽表上分割槽維護操作對全域性及本地索引狀態的影響(2)索引
- 點陣圖索引(Bitmap Index)——點陣圖索引與資料DML鎖定索引Index
- Oracle資料庫中索引的維護 ztOracle資料庫索引
- Oracle資料庫中索引的維護(zt)Oracle資料庫索引
- Oracle資料庫中索引的維護(轉)Oracle資料庫索引
- create index...online操作過程中會申請持有哪些鎖Index
- 【INDEX】Oracle 索引常見知識梳理IndexOracle索引
- 伺服器在使用過程中如何保護資料伺服器
- 資料庫維護常用操作4--表空間操作資料庫
- 【索引】Oracle之不可見索引和虛擬索引的比對索引Oracle
- 關於不可見索引的學習索引
- Sql Server系列:索引維護SQLServer索引
- 索引優化和維護索引優化
- DML 語句處理過程
- oracle分割槽及分割槽索引partition_partition index_維護(五)_快捷方法Oracle索引Index
- Oracle資料庫中索引的維護 (轉帖)Oracle資料庫索引
- oracle全文索引之commit與DML操作Oracle索引MIT