Oracle Database Discussions

Hi all

Please help me to choose the plan for query. Can one tell me which is best plan for query plan No 1 or plan no 2
Plan NO 1:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 442504905

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 325 | 13222 (1)| 00:02:39 |
| 1 | SORT ORDER BY | | 1 | 325 | 13222 (1)| 00:02:39 |
| 2 | HASH UNIQUE | | 1 | 325 | 13221 (1)| 00:02:39 |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 1 | 325 | 13220 (1)| 00:02:39 |
| 5 | NESTED LOOPS | | 1 | 224 | 13217 (1)| 00:02:39 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 6 | NESTED LOOPS | | 1 | 199 | 13216 (1)| 00:02:39 |
| 7 | NESTED LOOPS | | 1 | 144 | 13215 (1)| 00:02:39 |
|* 8 | HASH JOIN | | 1 | 93 | 13212 (1)| 00:02:39 |
|* 9 | TABLE ACCESS FULL | REC_EPS | 787 | 25184 | 742 (2)| 00:00:09 |
|* 10 | TABLE ACCESS FULL | RECEIPTDETAIL | 1059K| 61M| 12462 (1)| 00:02:30 |
|* 11 | TABLE ACCESS BY INDEX ROWID| LOTXLOCXID | 1 | 51 | 3 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | IDX_LLI_ISQ | 1 | | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | CODELKUP | 1 | 55 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_SYS_C004623 | 1 | | 0 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | RECEIPT | 1 | 25 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_SYS_C004755 | 1 | | 0 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 17 | INDEX RANGE SCAN | PK_SYS_C004767 | 1 | | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | SKU | 1 | 101 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

8 - access("RECEIPTDETAIL"."RECEIPTKEY"=SYS_OP_C2C("REC_EPS"."ASNKEY") AND
"RECEIPTDETAIL"."SKU"=SYS_OP_C2C("REC_EPS"."SKU"))
9 - filter(SYS_OP_C2C("REC_EPS"."ASNKEY")>=U'0' AND
SYS_OP_C2C("REC_EPS"."ASNKEY")<=U'ZZZZZ')

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10 - filter("RECEIPTDETAIL"."RECEIPTKEY">=U'0' AND "RECEIPTDETAIL"."RECEIPTKEY"<=U'ZZZZZ')
11 - filter("LOTXLOCXID"."LOC"=U'STAGE')
12 - access("RECEIPTDETAIL"."TOID"="LOTXLOCXID"."ID" AND
"RECEIPTDETAIL"."SKU"="LOTXLOCXID"."SKU" AND "LOTXLOCXID"."QTY">0)
14 - access("LISTNAME"=U'RECSTATUS' AND "CODELKUP"."CODE"="RECEIPTDETAIL"."STATUS")
16 - access("RECEIPTDETAIL"."RECEIPTKEY"="RECEIPT"."RECEIPTKEY")
filter("RECEIPT"."RECEIPTKEY">=U'0' AND "RECEIPT"."RECEIPTKEY"<=U'ZZZZZ')
17 - access("RECEIPTDETAIL"."SKU"="SKU"."SKU")

41 rows selected.
=================================================================

Plan No 2:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1174712643

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 325 | 18382 (1)| 00:03:41 |
| 1 | SORT ORDER BY | | 1 | 325 | 18382 (1)| 00:03:41 |
| 2 | HASH UNIQUE | | 1 | 325 | 18381 (1)| 00:03:41 |
|* 3 | HASH JOIN | | 1 | 325 | 18380 (1)| 00:03:41 |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 1 | 293 | 17637 (1)| 00:03:32 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 6 | NESTED LOOPS | | 1 | 192 | 17634 (1)| 00:03:32 |
| 7 | NESTED LOOPS | | 1 | 167 | 17633 (1)| 00:03:32 |
| 8 | NESTED LOOPS | | 2 | 224 | 17631 (1)| 00:03:32 |
|* 9 | TABLE ACCESS BY INDEX ROWID| LOTXLOCXID | 2 | 102 | 3 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_LOTXLOCXID_LOC | 2 | | 1 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID| RECEIPTDETAIL | 1 | 61 | 8814 (1)| 00:01:46 |
|* 12 | INDEX FULL SCAN | IDX_RD_QCREQ | 1 | | 8813 (1)| 00:01:46 |
| 13 | TABLE ACCESS BY INDEX ROWID | CODELKUP | 1 | 55 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_SYS_C004623 | 1 | | 0 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | RECEIPT | 1 | 25 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_SYS_C004755 | 1 | | 0 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 17 | INDEX RANGE SCAN | PK_SYS_C004767 | 1 | | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | SKU | 1 | 101 | 3 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | REC_EPS | 787 | 25184 | 742 (2)| 00:00:09 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("RECEIPTDETAIL"."RECEIPTKEY"=SYS_OP_C2C("REC_EPS"."ASNKEY") AND
"RECEIPTDETAIL"."SKU"=SYS_OP_C2C("REC_EPS"."SKU"))
9 - filter("LOTXLOCXID"."QTY">0)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10 - access("LOTXLOCXID"."LOC"=U'STAGE')
11 - filter("RECEIPTDETAIL"."RECEIPTKEY">=U'0' AND "RECEIPTDETAIL"."RECEIPTKEY"<=U'ZZZZZ')
12 - access("RECEIPTDETAIL"."SKU"="LOTXLOCXID"."SKU" AND
"RECEIPTDETAIL"."TOID"="LOTXLOCXID"."ID")
filter("RECEIPTDETAIL"."TOID"="LOTXLOCXID"."ID" AND
"RECEIPTDETAIL"."SKU"="LOTXLOCXID"."SKU")
14 - access("LISTNAME"=U'RECSTATUS' AND "CODELKUP"."CODE"="RECEIPTDETAIL"."STATUS")
16 - access("RECEIPTDETAIL"."RECEIPTKEY"="RECEIPT"."RECEIPTKEY")
filter("RECEIPT"."RECEIPTKEY">=U'0' AND "RECEIPT"."RECEIPTKEY"<=U'ZZZZZ')
17 - access("RECEIPTDETAIL"."SKU"="SKU"."SKU")
19 - filter(SYS_OP_C2C("REC_EPS"."ASNKEY")>=U'0' AND SYS_OP_C2C("REC_EPS"."ASNKEY")<=U'ZZZZZ')

44 rows selected.