基本做法参照BITI的文章,http://blog.itpub.net/category/330/831
可结果发现并不对.过程如下:
SQL> set autotrace on
SQL> drop index t_index;
索引已删除。
SQL> alter session set create_stored_outlines = my_demo;
会话已更改。
SQL> select count(object_id) from t;
COUNT(OBJECT_ID)
----------------
49811
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 159 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | TABLE ACCESS FULL| T | 49811 | 243K| 159 (2)| 00:00:02 |
---------------------------------------------------------------------------
统计信息
----------------------------------------------------------
175 recursive calls
0 db block gets
707 consistent gets
0 physical reads
0 redo size
418 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter session set create_stored_outlines = false;
会话已更改。
SQL> create index t_index on t(object_id);
索引已创建。
SQL> select count(object_id) from t;
COUNT(OBJECT_ID)
----------------
49811
执行计划
----------------------------------------------------------
Plan hash value: 789015712
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 5 | 27 (4)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 5 | |
|
| 2 | INDEX FAST FULL SCAN| T_INDEX | 49811 | 243K| 27 (4)| 00:00:01
|
--------------------------------------------------------------------------------
-
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
117 consistent gets
110 physical reads
0 redo size
418 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter session set use_stored_outlines = my_demo;
会话已更改。
SQL> select count(object_id) from t;
COUNT(OBJECT_ID)
----------------
49811
执行计划
----------------------------------------------------------
Plan hash value: 789015712
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 5 | 27 (4)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 5 | |
|
| 2 | INDEX FAST FULL SCAN| T_INDEX | 49811 | 243K| 27 (4)| 00:00:01
|
--------------------------------------------------------------------------------
-
Note
-----
- outline "SYS_OUTLINE_09061117273662511" used for this statement
统计信息
----------------------------------------------------------
9 recursive calls
3 db block gets
118 consistent gets
0 physical reads
520 redo size
418 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
说明:
我的数据库版本是10g,我发现10g在select count(*)时,不论是否建索引,也不论是否作统计数据收集,执行计划都是做全表扫描.所以只能换一种测试方法.存储做全表扫描的执行计划,在建立索引后,select count(索引列)也希望他走全表扫描来验证稳定执行计划.
但测试结果发现并非如预期一样.原因未知.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9375/viewspace-605931/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9375/viewspace-605931/