CIRQIT@XENON> sta inline.sql Execution Plan ---------------------------------------------------------- Plan hash value: 2056288832 -------------------------------------------------------------------------------- -------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- -------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8317 | 166 (5)| 00:00:02 | | 1 | SORT ORDER BY | | 1 | 8317 | 166 (5)| 00:00:02 | | 2 | NESTED LOOPS SEMI | | 1 | 8317 | 165 (5)| 00:00:02 | | 3 | NESTED LOOPS | | 1 | 8307 | 163 (5)| 00:00:02 | | 4 | NESTED LOOPS | | 1 | 8276 | 162 (5)| 00:00:02 | | 5 | NESTED LOOPS | | 1 | 8197 | 160 (5)| 00:00:02 | | 6 | NESTED LOOPS | | 1 | 8169 | 159 (5)| 00:00:02 | |* 7 | HASH JOIN | | 1 | 8118 | 157 (5)| 00:00:02 | |* 8 | HASH JOIN | | 1 | 8096 | 126 (5)| 00:00:02 | |* 9 | HASH JOIN | | 1 | 6072 | 94 (5)| 00:00:02 | |* 10 | HASH JOIN | | 1 | 4048 | 63 (5)| 00:00:01 | | 11 | VIEW | | 1 | 2024 | 31 (4)| 00:00:01 | | 12 | SORT ORDER BY | | 1 | 148 | 31 (4)| 00:00:01 | | 13 | NESTED LOOPS | | 1 | 148 | 30 (0)| 00:00:01 | | 14 | NESTED LOOPS | | 1 | 127 | 29 (0)| 00:00:01 | | 15 | NESTED LOOPS | | 1 | 106 | 27 (0)| 00:00:01 | | 16 | TABLE ACCESS BY INDEX ROWID | PBVORG | 1 | 36 | 2 (0)| 00:00:01 | |* 17 | INDEX RANGE SCAN | PBVORG_PARENT_INDX | 1 | | 1 (0)| 00:00:01 | |* 18 | TABLE ACCESS BY INDEX ROWID | ESTIMATE | 1 | 70 | 25 (0)| 00:00:01 | |* 19 | INDEX RANGE SCAN | JOB_STAGE_CLNT_DATE_REQ_IN DX | 26 | | 4 (0)| 00:00:01 | |* 20 | TABLE ACCESS BY INDEX ROWID | ORG_CUSTOM_FIELD_MAP | 1 | 21 | 2 (0)| 00:00:01 | |* 21 | INDEX RANGE SCAN | ORG_CUSTOM_FIELD_MAP_PK | 1 | | 1 (0)| 00:00:01 | |* 22 | INDEX UNIQUE SCAN | ESTIMATE_CUSTOM_FIELD_PK | 1 | 21 | 1 (0)| 00:00:01 | | 23 | VIEW | | 1 | 2024 | 31 (4)| 00:00:01 | | 24 | SORT ORDER BY | | 1 | 148 | 31 (4)| 00:00:01 | | 25 | NESTED LOOPS | | | | | | | 26 | NESTED LOOPS | | 1 | 148 | 30 (0)| 00:00:01 | | 27 | NESTED LOOPS | | 1 | 127 | 29 (0)| 00:00:01 | | 28 | NESTED LOOPS | | 1 | 106 | 27 (0)| 00:00:01 | | 29 | TABLE ACCESS BY INDEX ROWID| PBVORG | 1 | 36 | 2 (0)| 00:00:01 | |* 30 | INDEX RANGE SCAN | PBVORG_PARENT_INDX | 1 | | 1 (0)| 00:00:01 | |* 31 | TABLE ACCESS BY INDEX ROWID| ESTIMATE | 1 | 70 | 25 (0)| 00:00:01 | |* 32 | INDEX RANGE SCAN | JOB_STAGE_CLNT_DATE_REQ_IN DX | 26 | | 4 (0)| 00:00:01 | |* 33 | INDEX RANGE SCAN | ESTIMATE_CUSTOM_FIELD_PK | 1 | 21 | 2 (0)| 00:00:01 | |* 34 | INDEX UNIQUE SCAN | ORG_CUSTOM_FIELD_MAP_PK | 1 | | 0 (0)| 00:00:01 | | 35 | TABLE ACCESS BY INDEX ROWID | ORG_CUSTOM_FIELD_MAP | 1 | 21 | 1 (0)| 00:00:01 | | 36 | VIEW | | 1 | 2024 | 31 (4)| 00:00:01 | | 37 | SORT ORDER BY | | 1 | 148 | 31 (4)| 00:00:01 | | 38 | NESTED LOOPS | | | | | | | 39 | NESTED LOOPS | | 1 | 148 | 30 (0)| 00:00:01 | | 40 | NESTED LOOPS | | 1 | 127 | 29 (0)| 00:00:01 | | 41 | NESTED LOOPS | | 1 | 106 | 27 (0)| 00:00:01 | | 42 | TABLE ACCESS BY INDEX ROWID | PBVORG | 1 | 36 | 2 (0)| 00:00:01 | |* 43 | INDEX RANGE SCAN | PBVORG_PARENT_INDX | 1 | | 1 (0)| 00:00:01 | |* 44 | TABLE ACCESS BY INDEX ROWID | ESTIMATE | 1 | 70 | 25 (0)| 00:00:01 | |* 45 | INDEX RANGE SCAN | JOB_STAGE_CLNT_DATE_REQ_IN DX | 26 | | 4 (0)| 00:00:01 | |* 46 | INDEX RANGE SCAN | ESTIMATE_CUSTOM_FIELD_PK | 1 | 21 | 2 (0)| 00:00:01 | |* 47 | INDEX UNIQUE SCAN | ORG_CUSTOM_FIELD_MAP_PK | 1 | | 0 (0)| 00:00:01 | | 48 | TABLE ACCESS BY INDEX ROWID | ORG_CUSTOM_FIELD_MAP | 1 | 21 | 1 (0)| 00:00:01 | | 49 | VIEW | | 1 | 2024 | 31 (4)| 00:00:01 | | 50 | SORT ORDER BY | | 1 | 148 | 31 (4)| 00:00:01 | | 51 | NESTED LOOPS | | | | | | | 52 | NESTED LOOPS | | 1 | 148 | 30 (0)| 00:00:01 | | 53 | NESTED LOOPS | | 1 | 127 | 29 (0)| 00:00:01 | | 54 | NESTED LOOPS | | 1 | 106 | 27 (0)| 00:00:01 | | 55 | TABLE ACCESS BY INDEX ROWID | PBVORG | 1 | 36 | 2 (0)| 00:00:01 | |* 56 | INDEX RANGE SCAN | PBVORG_PARENT_INDX | 1 | | 1 (0)| 00:00:01 | |* 57 | TABLE ACCESS BY INDEX ROWID | ESTIMATE | 1 | 70 | 25 (0)| 00:00:01 | |* 58 | INDEX RANGE SCAN | JOB_STAGE_CLNT_DATE_REQ_IN DX | 26 | | 4 (0)| 00:00:01 | |* 59 | INDEX RANGE SCAN | ESTIMATE_CUSTOM_FIELD_PK | 1 | 21 | 2 (0)| 00:00:01 | |* 60 | INDEX UNIQUE SCAN | ORG_CUSTOM_FIELD_MAP_PK | 1 | | 0 (0)| 00:00:01 | | 61 | TABLE ACCESS BY INDEX ROWID | ORG_CUSTOM_FIELD_MAP | 1 | 21 | 1 (0)| 00:00:01 | | 62 | VIEW | | 1 | 22 | 31 (4)| 00:00:01 | | 63 | SORT ORDER BY | | 1 | 148 | 31 (4)| 00:00:01 | | 64 | NESTED LOOPS | | | | | | | 65 | NESTED LOOPS | | 1 | 148 | 30 (0)| 00:00:01 | | 66 | NESTED LOOPS | | 1 | 127 | 29 (0)| 00:00:01 | | 67 | NESTED LOOPS | | 1 | 106 | 27 (0)| 00:00:01 | | 68 | TABLE ACCESS BY INDEX ROWID | PBVORG | 1 | 36 | 2 (0)| 00:00:01 | |* 69 | INDEX RANGE SCAN | PBVORG_PARENT_INDX | 1 | | 1 (0)| 00:00:01 | |* 70 | TABLE ACCESS BY INDEX ROWID | ESTIMATE | 1 | 70 | 25 (0)| 00:00:01 | |* 71 | INDEX RANGE SCAN | JOB_STAGE_CLNT_DATE_REQ_IN DX | 26 | | 4 (0)| 00:00:01 | |* 72 | INDEX RANGE SCAN | ESTIMATE_CUSTOM_FIELD_PK | 1 | 21 | 2 (0)| 00:00:01 | |* 73 | INDEX UNIQUE SCAN | ORG_CUSTOM_FIELD_MAP_PK | 1 | | 0 (0)| 00:00:01 | | 74 | TABLE ACCESS BY INDEX ROWID | ORG_CUSTOM_FIELD_MAP | 1 | 21 | 1 (0)| 00:00:01 | |* 75 | TABLE ACCESS BY INDEX ROWID | PRODUCT_ORDER | 1 | 51 | 2 (0)| 00:00:01 | |* 76 | INDEX RANGE SCAN | PRODUCT_ORDER_KEY_INDX | 1 | | 1 (0)| 00:00:01 | |* 77 | TABLE ACCESS BY INDEX ROWID | PBVORG | 1 | 28 | 1 (0)| 00:00:01 | |* 78 | INDEX UNIQUE SCAN | PBVORG_PK | 1 | | 0 (0)| 00:00:01 | |* 79 | TABLE ACCESS BY INDEX ROWID | ESTIMATE | 1 | 79 | 2 (0)| 00:00:01 | |* 80 | INDEX UNIQUE SCAN | ESTIMATE_PK | 1 | | 1 (0)| 00:00:01 | | 81 | TABLE ACCESS BY INDEX ROWID | CIRQIT_USER | 1 | 31 | 1 (0)| 00:00:01 | |* 82 | INDEX UNIQUE SCAN | CIRQIT_USER_PK | 1 | | 0 (0)| 00:00:01 | |* 83 | INDEX RANGE SCAN | ORDER_STATUS_PK | 5220 | 52200 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------- -------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 7 - access("I"."ID"="H"."ID") 8 - access("H"."ID"="G"."ID") 9 - access("G"."ID"="E"."ID") 10 - access("E"."ID"="F"."ID") 17 - access("C"."PARENT_ORG_ID"=2485) 18 - filter("B"."ESTIMATE_CONTACT_USER_ID"='p.dreyer@cancer') 19 - access("B"."ORG_ID"="C"."ORG_ID") filter(TRUNC(INTERNAL_FUNCTION("B"."CLIENT_DATE_REQUESTED"))>=TO_DATE(' 2 011-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 20 - filter(TRUNC("D"."DISPLAY_ORDER"/100,0)>1) 21 - access("B"."ORG_ID"="D"."ORG_ID" AND "D"."CUSTOM_FIELD_NAME" LIKE 'ACSAcc ount%') filter("D"."CUSTOM_FIELD_NAME" LIKE 'ACSAccount%') 22 - access("A"."ESTIMATE_NBR"="B"."ESTIMATE_NBR" AND "A"."VERSION_NBR"="B"."V ERSION_NBR" AND "A"."CUSTOM_FIELD_NAME"="D"."CUSTOM_FIELD_NAME") filter("A"."CUSTOM_FIELD_NAME" LIKE 'ACSAccount%') 30 - access("C"."PARENT_ORG_ID"=2485) 31 - filter("B"."ESTIMATE_CONTACT_USER_ID"='p.dreyer@cancer') 32 - access("B"."ORG_ID"="C"."ORG_ID") filter(TRUNC(INTERNAL_FUNCTION("B"."CLIENT_DATE_REQUESTED"))>=TO_DATE(' 2 011-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 33 - access("A"."ESTIMATE_NBR"="B"."ESTIMATE_NBR" AND "A"."VERSION_NBR"="B"."V ERSION_NBR" AND "A"."CUSTOM_FIELD_NAME" LIKE 'ACSActivity%') filter("A"."CUSTOM_FIELD_NAME" LIKE 'ACSActivity%') 34 - access("B"."ORG_ID"="D"."ORG_ID" AND "A"."CUSTOM_FIELD_NAME"="D"."CUSTOM_ FIELD_NAME") filter("D"."CUSTOM_FIELD_NAME" LIKE 'ACSActivity%') 43 - access("C"."PARENT_ORG_ID"=2485) 44 - filter("B"."ESTIMATE_CONTACT_USER_ID"='p.dreyer@cancer') 45 - access("B"."ORG_ID"="C"."ORG_ID") filter(TRUNC(INTERNAL_FUNCTION("B"."CLIENT_DATE_REQUESTED"))>=TO_DATE(' 2 011-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 46 - access("A"."ESTIMATE_NBR"="B"."ESTIMATE_NBR" AND "A"."VERSION_NBR"="B"."V ERSION_NBR" AND "A"."CUSTOM_FIELD_NAME" LIKE 'ACSFund%') filter("A"."CUSTOM_FIELD_NAME" LIKE 'ACSFund%') 47 - access("B"."ORG_ID"="D"."ORG_ID" AND "A"."CUSTOM_FIELD_NAME"="D"."CUSTOM_ FIELD_NAME") filter("D"."CUSTOM_FIELD_NAME" LIKE 'ACSFund%') 56 - access("C"."PARENT_ORG_ID"=2485) 57 - filter("B"."ESTIMATE_CONTACT_USER_ID"='p.dreyer@cancer') 58 - access("B"."ORG_ID"="C"."ORG_ID") filter(TRUNC(INTERNAL_FUNCTION("B"."CLIENT_DATE_REQUESTED"))>=TO_DATE(' 2 011-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 59 - access("A"."ESTIMATE_NBR"="B"."ESTIMATE_NBR" AND "A"."VERSION_NBR"="B"."V ERSION_NBR" AND "A"."CUSTOM_FIELD_NAME" LIKE 'ACSBusPurpose%') filter("A"."CUSTOM_FIELD_NAME" LIKE 'ACSBusPurpose%') 60 - access("B"."ORG_ID"="D"."ORG_ID" AND "A"."CUSTOM_FIELD_NAME"="D"."CUSTOM_ FIELD_NAME") filter("D"."CUSTOM_FIELD_NAME" LIKE 'ACSBusPurpose%') 69 - access("C"."PARENT_ORG_ID"=2485) 70 - filter("B"."ESTIMATE_CONTACT_USER_ID"='p.dreyer@cancer') 71 - access("B"."ORG_ID"="C"."ORG_ID") filter(TRUNC(INTERNAL_FUNCTION("B"."CLIENT_DATE_REQUESTED"))>=TO_DATE(' 2 011-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 72 - access("A"."ESTIMATE_NBR"="B"."ESTIMATE_NBR" AND "A"."VERSION_NBR"="B"."V ERSION_NBR" AND "A"."CUSTOM_FIELD_NAME" LIKE 'ACSAmount%') filter("A"."CUSTOM_FIELD_NAME" LIKE 'ACSAmount%') 73 - access("B"."ORG_ID"="D"."ORG_ID" AND "A"."CUSTOM_FIELD_NAME"="D"."CUSTOM_ FIELD_NAME") filter("D"."CUSTOM_FIELD_NAME" LIKE 'ACSAmount%') 75 - filter("A"."CURRENT_STATUS_NAME"<>'CANC' AND "A"."CURRENT_STATUS_NAME"<>' OREJ') 76 - access("I"."ESTIMATE_NBR"="A"."ESTIMATE_NBR" AND "I"."VERSION_NBR"="A"."V ERSION_NBR") filter("E"."ESTIMATE_NBR"="A"."ESTIMATE_NBR" AND "E"."VERSION_NBR"="A"."V ERSION_NBR" AND "F"."ESTIMATE_NBR"="A"."ESTIMATE_NBR" AND "F"."VERSION_NBR"="A"."V ERSION_NBR" AND "G"."ESTIMATE_NBR"="A"."ESTIMATE_NBR" AND "G"."VERSION_NBR"="A"."V ERSION_NBR" AND "H"."ESTIMATE_NBR"="A"."ESTIMATE_NBR" AND "H"."VERSION_NBR"="A"."V ERSION_NBR") 77 - filter("D"."PARENT_ORG_ID"=2485) 78 - access("A"."CLIENT_ORG_ID"="D"."ORG_ID") 79 - filter("B"."ESTIMATE_CONTACT_USER_ID"='p.dreyer@cancer' AND TRUNC(INTERNAL_FUNCTION("B"."CLIENT_DATE_REQUESTED"))>=TO_DATE(' 2 011-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 80 - access("A"."ESTIMATE_NBR"="B"."ESTIMATE_NBR" AND "A"."VERSION_NBR"="B"."V ERSION_NBR") 82 - access("C"."USER_ID"="B"."SPEC_CONTACT_USER_ID") 83 - access("Y"."ORDER_NBR"="A"."ORDER_NBR" AND "Y"."STATUS_NAME"='OACT') CIRQIT@XENON> spool off