索引重建的数据源（二）
对这个问题有了进一步的认识。
索引重建的数据源：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博客 ” ，链接：https://blog.itpub.net/4227/viewspace-712697/ ，如需转载，请注明出处，否则将追究法律责任。
