创建索引前后执行计划的变更和软硬解析的比较(二)
ips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
执行计划虽依然走全表扫描，但是执行了软解析，这里的主要原因是在共享池中存在同样的SQL语句的一个副本（两条语句一模一样）
[sql]
SQL> select object_id,OBJECT_NAME from tt where object_id=10;
OBJECT_ID OBJECT_NAME
---------- -------------------------------------------------
10 C_USER#
Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 632 | 156 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TT | 8 | 632 | 156 (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
779 consistent gets
0 physical reads
0 redo size
481 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select OBJECT_ID,OBJECT_NAME from tt where object_id=10;
OBJECT_ID OBJECT_NAME
---------- ----------------------------------------------
10 C_USER#
Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 632 | 156 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TT | 8 | 632 | 156 (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
779 consistent gets
0 physical reads
0 redo size
481 bytes sent via SQL*Net to client
400 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语句不同，便会执行硬解析。
ps：由于没有对表TT做分析，所以以上执行计划中的ROWS值为8而不是1（只要一条返回记录）；
继续测试：
在表TT的object_id列添加索引TT_IDX，并执行上面一样的SQL语句进行检索：
[sql]
SQL> create index tt_idx on tt(object_id);
Index created.
SQL>
[sql]
SQL> select object_id,object_name from tt where object_id=10;
OBJECT_ID OBJECT_NAME
---------- ----------------------------------------
10 C_USER#
Execution Plan
----------------------------------------------------------
Plan hash value: 2072537773
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------