索引重建的資料來源（二）
對這個問題有了進一步的認識。
索引重建的資料來源：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索引
- 重建索引索引
- 索引重建索引
- 清理重建失敗的索引索引
- SQL Server 2014的重建索引SQLServer索引
- oracle重建索引（一）Oracle索引
- oracle重建索引（三）Oracle索引
- MSSQL Rebuild(重建)索引SQLRebuild索引
- 匿名類 與 索引重建索引
- 多資料來源與動態資料來源的權衡
- AppBoxFuture: 二級索引及索引掃描查詢資料APP索引
- Raid磁碟陣列重建後如何恢復原來的資料AI陣列
- Spring系列 之資料來源的配置 資料庫 資料來源 連線池的區別Spring資料庫
- 「Elasticsearch」ES重建索引怎麼才能做到資料無縫遷移呢？Elasticsearch索引
- MySQL（二）：快速理解MySQL資料庫索引MySql資料庫索引
- 教你如何在 elasticsearch 中重建索引Elasticsearch索引
- HugeGraph之索引重建和刪除索引
- 如何在Mac上重建Spotlight索引Mac索引
- sqlserver索引重建和索引重組的區別和操作方法SQLServer索引
- SpringBoot 的多資料來源配置Spring Boot
- 【TUNE_ORACLE】索引定期重建的利與弊Oracle索引
- 資料來源(DataSource)是什麼以及SpringBoot中資料來源配置Spring Boot
- SparkSQL外部資料來源SparkSQL
- 阿里DRUID資料來源阿里UI
- 多資料來源配置
- SpringBoot多資料來源Spring Boot
- TongWeb資料來源原理Web
- 重建索引報ORA-14086錯誤索引
- [譯]Swift 中的通用資料來源Swift
- springboot 配置DRUID資料來源的方法Spring BootUI
- SAP Spartacus SimpleResponsiveBannerComponent url 的資料來源
- SpringBoot資料訪問之Druid資料來源的使用Spring BootUI
- 資料泵重建使用者
- Spring多資料來源配置Spring
- SpringBoot整合Druid資料來源Spring BootUI
- weblogic配置JDBC資料來源WebJDBC
- springBoot 多資料來源配置Spring Boot
- pip更換資料來源
- springboot多資料來源配置Spring Boot