今天给公司的人培训的时候,遇到一个比较尴尬的事情。
我创建了一个测试表test.表字段object_name的分布如下:
SQL> select object_name,count(*) from test group by object_name;
OBJECT_NAME COUNT(*)
---------------------------------------- ----------
wxh 49784
test 9
然后再object_name上创建了一个普通索引
SQL>create index ind_name on test(object_name);
删除表上的统计信息:
SQL> BEGIN
2 DBMS_STATS.DELETE_TABLE_STATS(OWNNAME => 'SCOTT', TABNAME => 'TEST');
3 END;
4 /
PL/SQL 过程已成功完成。
查看相关的几个统计视图,表的,列的,索引的,列直方图的,统计信息已经都没了。
然后执行查询:
SQL> select * from test where object_name='wxh';
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44151 | 7631K| 196 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST | 44151 | 7631K| 196 (3)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='wxh')
Note
-----
- dynamic sampling used for this statement
SQL> select * from test where object_name='test';
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 688048857
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1593 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 9 | 1593 | 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')
Note
-----
- dynamic sampling used for this statement
这里输出的结果和我的初衷是不一样的:
我以为由于表列缺少直方图信息,ORACLE会都采取索引扫描或者都采取全表扫描。可是输出的结果,ORACLE竟然都产生了正确的执行计划。
[ 本帖最后由 wei-xh 于 2010-6-17 16:45 编辑 ]
我创建了一个测试表test.表字段object_name的分布如下:
SQL> select object_name,count(*) from test group by object_name;
OBJECT_NAME COUNT(*)
---------------------------------------- ----------
wxh 49784
test 9
然后再object_name上创建了一个普通索引
SQL>create index ind_name on test(object_name);
删除表上的统计信息:
SQL> BEGIN
2 DBMS_STATS.DELETE_TABLE_STATS(OWNNAME => 'SCOTT', TABNAME => 'TEST');
3 END;
4 /
PL/SQL 过程已成功完成。
查看相关的几个统计视图,表的,列的,索引的,列直方图的,统计信息已经都没了。
然后执行查询:
SQL> select * from test where object_name='wxh';
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44151 | 7631K| 196 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST | 44151 | 7631K| 196 (3)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='wxh')
Note
-----
- dynamic sampling used for this statement
SQL> select * from test where object_name='test';
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 688048857
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1593 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 9 | 1593 | 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')
Note
-----
- dynamic sampling used for this statement
这里输出的结果和我的初衷是不一样的:
我以为由于表列缺少直方图信息,ORACLE会都采取索引扫描或者都采取全表扫描。可是输出的结果,ORACLE竟然都产生了正确的执行计划。
[ 本帖最后由 wei-xh 于 2010-6-17 16:45 编辑 ]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-665556/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22034023/viewspace-665556/