数据库版本:10.2.0.1
C:\Documents and Settings\an.chen>sqlplus ca/ca001
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 6月 11 19:45:15 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> set autotrace on
SQL> CREATE OR REPLACE OUTLINE my_demo ON select count(object_id) from t;
大纲已创建。
SQL>
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 |
---------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
690 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
0 sorts (memory)
0 sorts (disk)
1 rows processed
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
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
117 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
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> ALTER SESSION SET USE_STORED_OUTLINES = TRUE;
会话已更改。
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 |
---------------------------------------------------------------------------
Note
-----
- outline "MY_DEMO" used for this statement
统计信息
----------------------------------------------------------
33 recursive calls
125 db block gets
711 consistent gets
1 physical reads
560 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
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ index(t_index) +/ count(object_id) from t;
select /*+ index(t_index) +/ count(object_id) from t
*
第 1 行出现错误:
ORA-01742: 备注错误终止
SQL> select /*+ index(t_index) */ 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
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
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> CREATE OR REPLACE OUTLINE my_demo ON select /*+ index(t_index) */ count(object_id) from t;
大纲已创建。
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
|
--------------------------------------------------------------------------------
-
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
118 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
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
解释说明:
我在表T未建索引之前先存储了他的执行计划,毫无疑问,应该是全表扫描.
然后创建一个索引.
这时再执行select count(object_id) from t;执行计划就变为快速全索引扫描.
这时如果使用存储的执行计划,再查询时仍然是执行全表扫描.
最后修改存储执行计划为快速全索引扫描.再执行同样的语句,执行计划也作了相应的改变.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9375/viewspace-605949/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9375/viewspace-605949/