下面继续一些辅助实验:
1)看看,收集表的统计信息,但不收集直方图的时候,是什么情况。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
3 TABNAME => 'TEST',
4 ESTIMATE_PERCENT => NULL,
5 METHOD_OPT => 'for all columns SIZE 1',
6 CASCADE => TRUE);
7 END;
8 /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 01.44
SQL> select * from test where object_name='wxh';
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24897 | 1774K| 196 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST | 24897 | 1774K| 196 (3)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='wxh')
SQL> select * from test where object_name='test';
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24897 | 1774K| 196 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST | 24897 | 1774K| 196 (3)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='test')
从结果来看,这个才符合我第一次的想法。如果不存在直方图信息,对于数据倾斜列,ORACLE就可能产生不了高效的执行计划。
2)收集表的统计信息,也收集直方图的时候。ORACLE产生了精确的执行计划。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
3 TABNAME => 'TEST',
4 ESTIMATE_PERCENT => NULL,
5 METHOD_OPT => 'for all columns SIZE auto',
6 CASCADE => TRUE);
7 END;
8 /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 01.15
SQL> select * from test where object_name='wxh';
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49784 | 3549K| 197 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST | 49784 | 3549K| 197 (3)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='wxh')
SQL> select * from test where object_name='test';
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 688048857
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 657 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 9 | 657 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_NAME | 9 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='test')
[本帖最后由 wei-xh 于 2010-6-17 16:45 编辑]