对这个问题有了进一步的认识。
索引重建的数据源：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而言，有多种执行计划可选择的其实也并不多。