ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引重建的数据源(二)

索引重建的数据源(二)

原创 Linux操作系统 作者:yangtingkun 时间:2011-12-04 22:30:30 0 删除 编辑

对这个问题有了进一步的认识。

索引重建的数据源: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将这个条件写到了优化器中,如果将优化器设置为RULEOracle同样可以做出相同的判断:

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10389910