索引重建的資料來源（二）
對這個問題有了進一步的認識。
索引重建的資料來源：http://yangtingkun.itpub.net/post/468/457384
上一篇文章測試的結果認為DDL也是基於CBO的，但是今天發現問題並非如此。Oracle在評估REBUILD索引時並不是根據統計資訊，而是根據資料字典中非索引欄位的長度：
SQL> create table t_rebuild (id number, flag char(1));
Table created.
SQL> insert into t_rebuild select rownum, 'a' from dba_objects;
15695 rows created.
SQL> commit;
Commit complete.
SQL> create index ind_t_rebuild_id on t_rebuild(id);
Index created.
SQL> explain plan for alter index ind_t_rebuild_id rebuild;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3014377519
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 82 |
1066 | 2 (0)| 00:00:01 |
| 1 |
INDEX BUILD NON UNIQUE| IND_T_REBUILD_ID | |
| | |
| 2 |
SORT CREATE INDEX | | 82 |
1066 | | |
| 3 |
TABLE ACCESS FULL |
T_REBUILD | 82 |
1066 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
10 rows selected.
SQL> alter table t_rebuild modify (flag char(2));
Table altered.
SQL> explain plan for alter index ind_t_rebuild_id rebuild;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3014377519
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 2288 | 29744 | 7
(0)| 00:00:01 |
| 1 |
INDEX BUILD NON UNIQUE| IND_T_REBUILD_ID | |
| | |
| 2 |
SORT CREATE INDEX | | 2288 | 29744 | | |
| 3 |
TABLE ACCESS FULL |
T_REBUILD | 2288 | 29744 | 7
(0)| 00:00:01 |
-------------------------------------------------------------------------------------------
10 rows selected.
SQL> alter table t_rebuild modify (flag char(3));
Table altered.
SQL> explain plan for alter index ind_t_rebuild_id rebuild;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 43729923
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 4738 | 61594 | 13
(0)| 00:00:01 |
| 1 |
INDEX BUILD NON UNIQUE| IND_T_REBUILD_ID | |
| | |
| 2 |
SORT CREATE INDEX | | 4738 | 61594 | |
|
| 3 |
INDEX FAST FULL SCAN| IND_T_REBUILD_ID | |
| | |
-------------------------------------------------------------------------------------------
10 rows selected.
隨著非索引列的長度增加，重建索引的執行計劃由全表掃描變成了索引快速全掃。
整個過程並沒有收集過表或索引的統計資訊，但是執行計劃已經發生了改變，下面嘗試關閉統計資訊動態收集，以及設定表和列屬性的方式影響執行計劃：
SQL> alter session set optimizer_dynamic_sampling = 0;
Session altered.
SQL> explain plan for alter index ind_t_rebuild_id rebuild;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 43729923
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 4738 | 61594 | 13
(0)| 00:00:01 |
| 1 |
INDEX BUILD NON UNIQUE| IND_T_REBUILD_ID | |
| | |
| 2 |
SORT CREATE INDEX | | 4738 | 61594 | | |
| 3 |
INDEX FAST FULL SCAN| IND_T_REBUILD_ID | |
| | |
-------------------------------------------------------------------------------------------
10 rows selected.
SQL> exec dbms_stats.set_table_stats(user, 'T_REBUILD', numrows => 1, numblks => 1, avgrlen => 2)
PL/SQL procedure successfully completed.
SQL> explain plan for alter index ind_t_rebuild_id rebuild;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 43729923
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 1 |
2 | 2 (0)| 00:00:01 |
| 1 |
INDEX BUILD NON UNIQUE| IND_T_REBUILD_ID | |
| | |
| 2 |
SORT CREATE INDEX | | 1 |
2 | | |
| 3 |
INDEX FAST FULL SCAN| IND_T_REBUILD_ID | |
| | |
-------------------------------------------------------------------------------------------
10 rows selected.
SQL> exec dbms_stats.set_column_stats(user, 'T_REBUILD', 'FLAG', avgclen => 1)
PL/SQL procedure successfully completed.
SQL> explain plan for alter index ind_t_rebuild_id rebuild;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 43729923
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 1 |
2 | 2 (0)| 00:00:01 |
| 1 |
INDEX BUILD NON UNIQUE| IND_T_REBUILD_ID | |
| | |
| 2 |
SORT CREATE INDEX | | 1 |
2 | | |
| 3 |
INDEX FAST FULL SCAN| IND_T_REBUILD_ID | |
| | |
-------------------------------------------------------------------------------------------
10 rows selected.
很明顯DDL執行計劃的確定其實和統計資訊沒有什麼關係，而完全是根據資料字典確定的。因此這實際上也是一種RULE，只不過Oracle將這個條件寫到了最佳化器中，如果將最佳化器設定為RULE，Oracle同樣可以做出相同的判斷：
SQL> alter session set optimizer_mode = rule;
Session altered.
SQL> drop table t_rebuild purge;
Table dropped.
SQL> create table t_rebuild (id number, flag char(1));
Table created.
SQL> insert into t_rebuild select rownum, 'a' from dba_objects;
15695 rows created.
SQL> commit;
Commit complete.
SQL> create index ind_t_rebuild_id on t_rebuild(id);
Index created.
SQL> explain plan for alter index ind_t_rebuild_id rebuild;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 3014377519
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | ALTER INDEX STATEMENT | |
| 1 |
INDEX BUILD NON UNIQUE| IND_T_REBUILD_ID |
| 2 |
SORT CREATE INDEX | |
| 3 |
TABLE ACCESS FULL |
T_REBUILD |
---------------------------------------------------
Note
-----
- rule based optimizer used (consider
using cbo)
14 rows selected.
SQL> alter table t_rebuild modify flag char(3);
Table altered.
SQL> explain plan for alter index ind_t_rebuild_id rebuild;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 43729923
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | ALTER INDEX STATEMENT | |
| 1 |
INDEX BUILD NON UNIQUE| IND_T_REBUILD_ID |
| 2 |
SORT CREATE INDEX | |
| 3 |
INDEX FAST FULL SCAN| IND_T_REBUILD_ID |
---------------------------------------------------
Note
-----
- rule based optimizer used (consider
using cbo)
14 rows selected.
不過由於很多DDL操作對於的表或物件本身就沒有統計資訊，完全使用CBO是不現實的，也是不準確的，所以採用這種基於規則的執行計劃也是有道理的。不過事實上，對於DDL而言，有多種執行計劃可選擇的其實也並不多。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-712697/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 索引重建的資料來源索引
- oracle重建索引（二）Oracle索引
- 索引的重建命令索引
- 資料庫索引《二》資料庫索引
- 【轉載】資料庫索引重建參考依據資料庫索引
- oracle重建索引Oracle索引
- oracle 索引分析及索引重建Oracle索引
- 多資料來源與動態資料來源的權衡
- Oracle表與索引的分析及索引重建Oracle索引
- oracle重建索引（一）Oracle索引
- oracle重建索引（三）Oracle索引
- sqlserver 全部索引重建SQLServer索引
- 淺談索引系列之索引重建索引
- Oracle表與索引的分析及索引重建(轉）Oracle索引
- jndi資料來源
- oracle批量重建索引方法Oracle索引
- Spring系列 之資料來源的配置 資料庫 資料來源 連線池的區別Spring資料庫
- 資料來源連線資料庫資料庫
- SparkSQL外部資料來源SparkSQL
- TongWeb資料來源原理Web
- jndi配置資料來源
- 多資料來源配置
- Oracle 表的移動和索引的重建Oracle索引
- oracle 索引重建提示指令碼Oracle索引指令碼
- 批量重建不可用索引索引
- MySQL（二）：快速理解MySQL資料庫索引MySql資料庫索引
- Spring Boot 動態資料來源（Spring 註解資料來源）Spring Boot
- Raid磁碟陣列重建後如何恢復原來的資料AI陣列
- sql優化實戰：從1353秒到135秒（刪除索引+修改資料+重建索引）SQL優化索引
- 「Elasticsearch」ES重建索引怎麼才能做到資料無縫遷移呢？Elasticsearch索引
- 使用RESETLOGS重建控制檔案恢復資料庫（二）資料庫
- [譯]Swift 中的通用資料來源Swift
- flashback_transaction_query的資料來源！
- Tomcat資料來源的問題Tomcat
- 多個資料來源的問題
- GridView用資料來源控制元件和用DataTable作為資料來源的不同View控制元件
- 機器學習資源收集、索引機器學習索引
- SQL Server 2014的重建索引SQLServer索引