有时候不能太相信CBO啊。

最新推荐文章于 2022-07-04 23:39:38 发布

转载最新推荐文章于 2022-07-04 23:39:38 发布 · 58 阅读

本文通过对比两个具有相同记录数但不同建表语句的表(test和test1)，展示了索引创建方式对数据库查询性能的影响。具体分析了聚簇因子如何影响ORACLE数据库选择索引扫描还是全表扫描。

测试表一：test
记录数：  846928 八十多万

测试表二：test1
记录数：  846928 八十多万表记录数、表结构都与test表一样。

但是两个表建表语句不一样：
测试表一test表的建表语句：
create table test as select * from dba_objects order by object_id;
create index IND_OBJECT_ID on test(object_id);

测试表二test1表的建表语句：
create table test1 as select * from dba_objects order by object_name;
create index IND_OBJECT_ID_1 on test1(object_id);

由于建表语句的不同，导致聚簇因子差别很大。

测试表一test，IND_OBJECT_ID的聚簇因子为54159
测试表二test1，IND_OBJECT_ID_1的聚簇因子779654,很接近表中记录数

分析表：
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
                              TABNAME => 'TEST',
                              CASCADE => TRUE,
                              METHOD_OPT => 'FOR  all columns SIZE auto');
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
                              TABNAME => 'TEST1',
                              CASCADE => TRUE,
                              METHOD_OPT => 'FOR  all columns SIZE auto');
END;

测试开始：
执行查询，ORACLE对test表采用了索引扫描，对test1表采取了全表扫描。
SQL> set timing on
SQL> select * from (select * from test union select * from test1) where object_id=145;

已选择1112行。
已用时间:  00: 00: 08.30

执行计划
----------------------------------------------------------
Plan hash value: 291511340

--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |             | 40139 |  6938K|    |  5202 (2)| 00:01:03 |
| 1 |  VIEW                         |             | 40139 |  6938K|    |  5202 (2)| 00:01:03 |
| 2 | SORT UNIQUE                |             | 40139 |  3645K|  9920K|  5202  (66)| 00:01:03 |
| 3 | UNION-ALL                |             |    |    |    |          |       |
| 4 |    TABLE ACCESS BY INDEX ROWID| TEST       | 19979 |  1814K|    |  1368 (1)| 00:00:17 |
|*  5 |    INDEX RANGE SCAN       | IND_OBJECT_ID | 19999 |    |    | 47 (3)| 00:00:01 |
|*  6 |    TABLE ACCESS FULL       | TEST1       | 20160 |  1830K|    |  2966 (2)| 00:00:36 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("TEST"."OBJECT_ID"=145)
6 - filter("TEST1"."OBJECT_ID"=145)

统计信息
----------------------------------------------------------
      189  recursive calls
      0  db block gets
   12383  consistent gets
   11253  physical reads
      0  redo size
   76244  bytes sent via SQL*Net to client
   1199  bytes received via SQL*Net from client
      76  SQL*Net roundtrips to/from client
      6  sorts (memory)
      0  sorts (disk)
   1112  rows processed

[ 本帖最后由 wei-xh 于 2010-5-3 00:38 编辑 ]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-662233/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-662233/