SQL> explain plan for 2 SELECT /*+ gather_plan_statistics */ DISTINCT HOB.PO_HEADER_ID,OOH.ORDER_NUMBER,POH.ATTRIBUTE1 3 OC_NO 3 FROM 4 OE_ORDER_HEADERS OOH,OE_ORDER_LINES OOL,HWKR_OM_B2B_RELATION_V HOB, 5 PO_HEADERS POH WHERE OOH.HEADER_ID = OOL.HEADER_ID AND HOB.PO_HEADER_ID = 6 POH.PO_HEADER_ID AND OOL.FLOW_STATUS_CODE NOT IN ( 'ENTERED','CANCELLED' ) 7 AND OOL.LINE_ID = HOB.OE_LINE_ID (+) AND OOH.ORDER_NUMBER = 1067045; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -------------- | Id | Operation | Name | Rows | Bytes |TempSpc | Cost (%CPU)| ---------------------------------------------------------------------------------------------------- -------------- | 0 | SELECT STATEMENT | | 1 | 64 | | 169K (4)| | 1 | HASH UNIQUE | | 1 | 64 | | 169K (4)| | 2 | NESTED LOOPS | | 1 | 64 | | 169K (4)| | 3 | NESTED LOOPS | | 1 | 64 | | 169K (4)| |* 4 | HASH JOIN | | 1 | 52 | | 169K (4)| | 5 | NESTED LOOPS | | 1 | 40 | | 12 (0)| | 6 | NESTED LOOPS | | 13 | 40 | | 12 (0)| |* 7 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_HEADERS_ALL | 1 | 16 | | 5 (0)| |* 8 | INDEX RANGE SCAN | OE_ORDER_HEADERS_U2 | 2 | | | 3 (0)| |* 9 | INDEX RANGE SCAN | OE_ORDER_LINES_N1 | 13 | | | 2 (0)| |* 10 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_LINES_ALL | 1 | 24 | | 7 (0)| | 11 | VIEW | HWKR_OM_B2B_RELATION_V | 587K| 6879K| | 169K (4)| | 12 | UNION-ALL | | | | | | | 13 | HASH UNIQUE | | 1 | 101 | | 155K (4)| | 14 | NESTED LOOPS | | 1 | 101 | | 155K (4)| | 15 | NESTED LOOPS | | 1 | 101 | | 155K (4)| | 16 | NESTED LOOPS | | 1 | 75 | | 155K (4)| | 17 | NESTED LOOPS | | 1 | 63 | | 155K (4)| |* 18 | HASH JOIN | | 1 | 55 | 21M | 155K (4)| |* 19 | HASH JOIN | | 484K| 15M| 10M | 111K (4)| |* 20 | TABLE ACCESS FULL | PO_REQUISITION_HEADERS_ALL | 484K| 4729K| | 4073 (5)| |* 21 | TABLE ACCESS FULL | OE_ORDER_LINES_ALL | 3160K| 72M| | 105K (4)| |* 22 | TABLE ACCESS FULL | PO_REQUISITION_LINES_ALL | 3712K| 74M| | 41524 (3)| |* 23 | TABLE ACCESS BY INDEX ROWID| OE_ORDER_HEADERS_ALL | 1 | 8 | | 2 (0)| |* 24 | INDEX UNIQUE SCAN | OE_ORDER_HEADERS_U1 | 1 | | | 1 (0)| | 25 | TABLE ACCESS BY INDEX ROWID | PO_REQ_DISTRIBUTIONS_ALL | 1 | 12 | | 3 (0)| |* 26 | INDEX RANGE SCAN | PO_REQ_DISTRIBUTIONS_N1 | 1 | | | 2 (0)| |* 27 | INDEX RANGE SCAN | PO_DISTRIBUTIONS_N2 | 1 | | | 2 (0)| | 28 | TABLE ACCESS BY INDEX ROWID | PO_DISTRIBUTIONS_ALL | 1 | 26 | | 3 (0)| PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- |* 29 | HASH JOIN | | 587K| 23M| 12M | 13857 (4)| |* 30 | TABLE ACCESS FULL | OE_ORDER_HEADERS_ALL | 680K| 5316K| | 12032 (4)| | 31 | TABLE ACCESS FULL | OE_DROP_SHIP_SOURCES | 587K| 19M| | 1151 (4)| |* 32 | INDEX UNIQUE SCAN | PO_HEADERS_U1 | 1 | | | 1 (0)| |* 33 | TABLE ACCESS BY INDEX ROWID | PO_HEADERS_ALL | 1 | 12 | | 2 (0)| ---------------------------------------------------------------------------------------------------- -------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("LINE_ID"="HOB"."OE_LINE_ID") 7 - filter(NVL("ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(USE RENV('CLIENT_INFO'), 1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99))) 8 - access("ORDER_NUMBER"=1067045) 9 - access("HEADER_ID"="HEADER_ID") 10 - filter("FLOW_STATUS_CODE"<>'ENTERED' AND "FLOW_STATUS_CODE"<>'CANCELLED' AND NVL("ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(USE RENV('CLIENT_INFO'), 1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99))) 18 - access("PRHA"."REQUISITION_HEADER_ID"="PRLA"."REQUISITION_HEADER_ID" AND "PRLA"."ORG_ID"="OOLA"."ORG_ID" AND "PRLA"."ITEM_ID"="OOLA"."INVENTORY_ITEM_ID") 19 - access("PRHA"."INTERFACE_SOURCE_LINE_ID"="OOLA"."LINE_ID") 20 - filter("PRHA"."INTERFACE_SOURCE_LINE_ID" IS NOT NULL) 21 - filter(NVL("OOLA"."CANCELLED_FLAG",'N')='N') 22 - filter("PRLA"."ITEM_ID" IS NOT NULL) 23 - filter(NVL("OOHA"."CANCELLED_FLAG",'N')='N') 24 - access("OOLA"."HEADER_ID"="OOHA"."HEADER_ID") 26 - access("PRDA"."REQUISITION_LINE_ID"="PRLA"."REQUISITION_LINE_ID") 27 - access("PDA"."REQ_DISTRIBUTION_ID"="PRDA"."DISTRIBUTION_ID") filter("PDA"."REQ_DISTRIBUTION_ID" IS NOT NULL) 29 - access("ODSS"."HEADER_ID"="OOHA"."HEADER_ID") 30 - filter(NVL("OOHA"."CANCELLED_FLAG",'N')='N') 32 - access("HOB"."PO_HEADER_ID"="PO_HEADER_ID") 33 - filter(NVL("ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(USE RENV('CLIENT_INFO'), 1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99))) Note ----- - 'PLAN_TABLE' is old version 74 rows selected.