Hi all,
I have a correlated query which performs badly and not to sure why. Here is the code:
*Explain Plan:*
PLAN_TABLE_OUTPUT
Plan hash value: 1791134653
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 262 | | 16216 (7)| 00:03:15 |
|* 1 | FILTER | | | | | | |
| 2 | HASH GROUP BY | | 1 | 262 | | 16216 (7)| 00:03:15 |
|* 3 | HASH JOIN | | 13708 | 3507K| 3456K| 16212 (7)| 00:03:15 |
|* 4 | HASH JOIN | | 13708 | 3293K| | 7579 (8)| 00:01:31 |
|* 5 | TABLE ACCESS FULL| SKRUDAP | 13708 | 388K| | 622 (10)| 00:00:08 |
| 6 | TABLE ACCESS FULL| SZRAPPL_ADT | 1197K| 247M| | 6914 (8)| 00:01:23 |
| 7 | TABLE ACCESS FULL | SZRAPPL_ADT | 1197K| 18M| | 6791 (6)| 00:01:22 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SATURN_SZRAPPL_ADT2"."SZRAPPL_ADT_TIMESTAMP"=MIN("A"."SZRAPPL_ADT_TIME
STAMP"))
3 - access("A"."SZRAPPL_ID"="SATURN_SZRAPPL_ADT2"."SZRAPPL_ID" AND
"A"."SZRAPPL_CHOICE_TYPE_NO"="SATURN_SZRAPPL_ADT2"."SZRAPPL_CHOICE_TYPE_NO")
4 - access("SKRUDAP"."SKRUDAP_APPLICANT_NO"="SATURN_SZRAPPL_ADT2"."SZRAPPL_APPLICA
NT_NO" AND "SKRUDAP"."SKRUDAP_CHOICE_NO"="SATURN_SZRAPPL_ADT2"."SZRAPPL_CHOICE_TYPE_N
O")
5 - filter("SKRUDAP"."SKRUDAP_TERM_CODE_ENTRY"='201100')
If I changed the Line which joins the timestamp to the MIN(timestamp) of the sub query to.....
AND nvl(SATURN_SZRAPPL_ADT2.SZRAPPL_ADT_TIMESTAMP, to_date('30/12/3999','DD/MM/YYYY')) = (select nvl(min(A.szrappl_adt_timestamp),to_date('30/12/3999','DD/MM/YYYY'))
then I get much a much much faster response. By taking swapping that line, this is what the explain plan looks like:
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 246 | 104K (1)| 00:20:50 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 13708 | 3293K| 7702 (10)| 00:01:33 |
|* 3 | TABLE ACCESS FULL | SKRUDAP | 13708 | 388K| 622 (10)| 00:00:08 |
| 4 | TABLE ACCESS FULL | SZRAPPL_ADT | 1197K| 247M| 7038 (9)| 00:01:25 |
| 5 | SORT AGGREGATE | | 1 | 16 | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| SZRAPPL_ADT | 1 | 16 | 10 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | SZRAPPL_ADT_IDX_1 | 7 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NVL("SATURN_SZRAPPL_ADT2"."SZRAPPL_ADT_TIMESTAMP",TO_DATE(' 3999-12-30
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))= (SELECT
NVL(MIN("A"."SZRAPPL_ADT_TIMESTAMP"),TO_DATE(' 3999-12-30 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')) FROM "SATURN"."SZRAPPL_ADT" "A" WHERE "A"."SZRAPPL_ID"=:B1 AND
"A"."SZRAPPL_CHOICE_TYPE_NO"=:B2))
2 - access("SKRUDAP"."SKRUDAP_APPLICANT_NO"="SATURN_SZRAPPL_ADT2"."SZRAPPL_APPLICANT_NO"
AND "SKRUDAP"."SKRUDAP_CHOICE_NO"="SATURN_SZRAPPL_ADT2"."SZRAPPL_CHOICE_TYPE_NO")
3 - filter("SKRUDAP"."SKRUDAP_TERM_CODE_ENTRY"='201100')
6 - filter("A"."SZRAPPL_CHOICE_TYPE_NO"=:B1)
7 - access("A"."SZRAPPL_ID"=:B1)