查询语句：
SELECT COUNT(DA.BH)
FROM JMJKDA DA
WHERE 1 = 1
AND TRUNC(MONTHS_BETWEEN(SYSDATE, DA.CSRQ) / 12) > 64
AND SSFWJG IN (SELECT CODE
FROM UNIT
WHERE CODE = '1B000000000000009880'
OR SJDW = '1B000000000000009880');
--------------------------------------------------------------------------------------------------------------------------------------------------------
在11g多次执行之后：
Execution Plan
----------------------------------------------------------
Plan hash value: 4014165961
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 68513 (1)| 00:13:43 |
| 1 | SORT AGGREGATE | | 1 | 67 | | |
|* 2 | HASH JOIN | | 13043 | 853K| 68513 (1)| 00:13:43 |
|* 3 | TABLE ACCESS FULL| UNIT | 11 | 440 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| JMJKDA | 208K| 5485K| 68509 (1)| 00:13:43 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SSFWJG"="CODE")
3 - filter("SJDW"='1B000000000000009880' OR
"CODE"='1B000000000000009880')
4 - filter(TRUNC(MONTHS_BETWEEN(SYSDATE@!,INTERNAL_FUNCTION("DA"."CSR
Q"))/12)>64)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10925 consistent gets
0 physical reads
0 redo size
531 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--------------------------------------------------------------------------------------------------------------------------------------------------------
在10g多次执行之后：
Execution Plan
----------------------------------------------------------
Plan hash value: 4014165961
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 55924 (2)| 00:11:12 |
| 1 | SORT AGGREGATE | | 1 | 67 | | |