Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH PL as
- (select project_id, activity_id, merchandise_amt,
- account, business_unit, po_id, line_nbr, sched_nbr, deptid
- from SYSADM.PS_PO_LINE_DIST_FS
- ),
- PH AS
- (
- SELECT PO_ID, BUSINESS_UNIT, VENDOR_ID, VENDOR_SETID, PO_DT
- FROM SYSADM.PS_PO_HDR
- ),
- VL AS
- (
- SELECT merchandise_amt, voucher_id, business_unit,
- business_unit_po, po_id, line_Nbr, sched_nbr
- FROM SYSADM.PS_VOUCHER_LINE
- ),
- V AS
- (
- SELECT accounting_dt, invoice_id, business_unit, voucher_id
- FROM SYSADM.PS_VOUCHER
- ),
- PV AS
- (
- SELECT VENDOR_ID, NAME1, SETID
- FROM SYSADM.PS_VENDOR
- ),
- RL AS
- (
- SELECT MERCHANDISE_AMT, business_unit, po_id, line_Nbr, sched_Nbr, business_unit_recv
- FROM SYSADM.PS_PO_RECV_LN_VW
- ),
- DL2 AS
- (
- SELECT project_id, activity_id, monetary_amount, account, business_unit, voucher_id, deptid, po_id
- FROM SYSADM.PS_DISTRIB_LINE
- ),
- VL2 AS
- (
- SELECT PO_ID, BUSINESS_UNIT, VOUCHER_ID, VOUCHER_LINE_NUM
- FROM SYSADM.PS_VOUCHER_LINE
- ),
- V2 AS
- (
- SELECT voucher_id, accounting_dt, invoice_id, business_unit, po_id, vendor_id, vendor_setid, entry_status, close_status
- FROM SYSADM.PS_VOUCHER
- ),
- PV2 AS
- (
- SELECT vendor_id, vendor_name_short, setid
- FROM SYSADM.PS_VENDOR
- )
- select (CASE
- when pl.project_id in ('CHEM','CAUS','FUEL') then 'TANK'
- when pl.project_id in ('CHOP','HOPP','ROLL','DECK','LIFT') then 'DRY'
- else '(No Barge Type)'
- end) as BARGE_TYPE,
- NVL (TRIM (PL.PROJECT_ID), '(No Project ID)') AS PROJECT_ID,
- NVL (TRIM (PH.PO_ID), '(No PO ID)') AS PO_ID,
- NVL (TRIM (PL.ACTIVITY_ID), '(No
- Activity ID)') AS ACTIVITY_ID,
- PL.MERCHANDISE_AMT AS PO_LINE_AMT,
- RL.MERCHANDISE_AMT AS REC_LN_AMT,
- VL.MERCHANDISE_AMT AS VOUCHER_LN_AMT,
- VL.VOUCHER_ID,
- V.ACCOUNTING_DT,
- TO_CHAR (V.ACCOUNTING_DT, 'MM') AS ACCOUNTING_MO,
- TO_CHAR (V.ACCOUNTING_DT, 'YYYY') AS ACCOUNTING_YR,
- PV.VENDOR_ID,
- PV.NAME1 AS VENDOR_NAME,
- PL.ACCOUNT,
- V.INVOICE_ID,
- V.BUSINESS_UNIT
- FROM
- (
- (
- (
- (
- (
- PH LEFT OUTER JOIN PL ON PH.BUSINESS_UNIT = PL.BUSINESS_UNIT AND PH.PO_ID = PL.PO_ID
- )
- LEFT OUTER JOIN RL ON RL.BUSINESS_UNIT = PL.BUSINESS_UNIT
- AND RL.PO_ID = PL.PO_ID
- AND RL.LINE_NBR = PL.LINE_NBR
- AND RL.SCHED_NBR = PL.SCHED_NBR
- )
- LEFT OUTER JOIN VL ON VL.BUSINESS_UNIT = RL.BUSINESS_UNIT_RECV
- AND VL.BUSINESS_UNIT_PO = RL.BUSINESS_UNIT
- AND VL.PO_ID = RL.PO_ID
- AND VL.LINE_NBR = RL.LINE_NBR
- AND VL.SCHED_NBR = RL.SCHED_NBR
- )
- LEFT OUTER JOIN PV ON PV.VENDOR_ID = PH.VENDOR_ID AND PV.SETID = PH.VENDOR_SETID
- )
- LEFT OUTER JOIN V ON V.BUSINESS_UNIT = VL.BUSINESS_UNIT AND VL.VOUCHER_ID = V.VOUCHER_ID
- )
- WHERE PH.PO_DT BETWEEN :v_BegDate AND :v_EndDate
- AND PH.BUSINESS_UNIT IN ('10000', '10200')
- AND PL.DEPTID = '1000'
- AND PL.ACCOUNT IN
- ('61200',
- '61210',
- '61220',
- '61230',
- '61240',
- '61250',
- '61260',
- '61270',
- '61280')
- AND VL.MERCHANDISE_AMT IS NOT NULL
- AND PL.PROJECT_ID IN
- ('HOPP', 'LIFT', 'ROLL', 'CAUS', 'CHEM', 'FUEL')
- AND PL.ACTIVITY_ID IN
- (SELECT DISTINCT ING_VESSEL_ID
- FROM SYSADM.PS_ING_BARGESPECIF
- WHERE (BUSINESS_UNIT, ASSET_ID) IN
- (SELECT DISTINCT BS.BUSINESS_UNIT, BS.ASSET_ID
- FROM SYSADM.PS_ING_BARGESPECIF BS)
- )
- UNION
- SELECT
- (
- CASE WHEN DL2.PROJECT_ID in ('CHEM','CAUS','FUEL') then 'TANK'
- WHEN DL2.PROJECT_ID in ('CHOP','HOPP','ROLL','DECK','LIFT') then 'DRY'
- ELSE '(No Barge Type)'
- END
- ) AS BARGE_TYPE,
- NVL (TRIM (DL2.PROJECT_ID), '(No
- Project ID)') AS PROJECT_ID,
- NVL (TRIM (VL2.PO_ID), '(No PO ID)') AS PO_ID,
- NVL (TRIM (DL2.ACTIVITY_ID), '(No Activity ID)') AS ACTIVITY_ID,
- 0 AS PO_AMOUNT,
- 0 AS REC_AMOUNT,
- SUM (DL2.MONETARY_AMOUNT) AS VOUCHER_LN_AMT,
- V2.VOUCHER_ID,
- V2.ACCOUNTING_DT,
- TO_CHAR (V2.ACCOUNTING_DT, 'MM') AS ACCOUNTING_MO,
- TO_CHAR (V2.ACCOUNTING_DT, 'YYYY') AS ACCOUNTING_YR,
- PV2.VENDOR_ID,
- PV2.VENDOR_NAME_SHORT,
- DL2.ACCOUNT,
- V2.INVOICE_ID,
- V2.BUSINESS_UNIT
- FROM V2, VL2, DL2, PV2
- WHERE V2.BUSINESS_UNIT = VL2.BUSINESS_UNIT
- AND V2.VOUCHER_ID = VL2.VOUCHER_ID
- AND V2.PO_ID = VL2.PO_ID
- AND VL2.BUSINESS_UNIT = DL2.BUSINESS_UNIT
- AND VL2.VOUCHER_ID = DL2.VOUCHER_ID
- AND VL2.VOUCHER_LINE_NUM = VL2.VOUCHER_LINE_NUM
- AND V2.VENDOR_ID = PV2.VENDOR_ID
- AND V2.VENDOR_SETID = PV2.SETID
- AND V2.ENTRY_STATUS <> 'X'
- AND V2.CLOSE_STATUS = 'O'
- AND V2.ACCOUNTING_DT BETWEEN :v_BegDate AND :v_EndDate
- AND V2.BUSINESS_UNIT IN ('10000', '10200')
- AND DL2.DEPTID = '1000'
- AND DL2.ACCOUNT IN
- ('61200',
- '61210',
- '61220',
- '61230',
- '61240',
- '61250',
- '61260',
- '61270',
- '61280')
- AND DL2.PO_ID = ' '
- AND DL2.PROJECT_ID IN
- ('HOPP', 'LIFT', 'ROLL', 'CAUS', 'CHEM', 'FUEL')
- AND DL2.ACTIVITY_ID IN
- (SELECT ING_VESSEL_ID
- FROM SYSADM.PS_ING_BARGESPECIF
- WHERE (BUSINESS_UNIT, ASSET_ID) IN
- (SELECT DISTINCT BS.BUSINESS_UNIT, BS.ASSET_ID
- FROM SYSADM.PS_ING_BARGESPECIF BS))
- GROUP BY VL2.PO_ID,
- V2.VOUCHER_ID,
- V2.ACCOUNTING_DT,
- PV2.VENDOR_ID,
- PV2.VENDOR_NAME_SHORT,
- DL2.ACCOUNT,
- DL2.PROJECT_ID,
- DL2.ACTIVITY_ID,
- VL2.PO_ID,
- V2.INVOICE_ID,
- V2.BUSINESS_UNIT
- ORDER BY ACCOUNTING_YR,
- ACCOUNTING_DT,
- BARGE_TYPE,
- PROJECT_ID,
- ACCOUNT
- /
- 89200 rows selected.
- Elapsed: 00:18:38.52
- ==================================================================
- PLAN_TABLE_OUTPUT
- -------------------------------------------------------------------------------------------------------------------------------------------------------
- Plan hash value: 4236987807
- -------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 6 | 1132 | 26118 (1)| 00:05:14 |
- | 1 | SORT UNIQUE | | 6 | 1132 | 26117 (94)| 00:05:14 |
- | 2 | UNION-ALL | | | | | |
- |* 3 | FILTER | | | | | |
- | 4 | NESTED LOOPS OUTER | | 1 | 292 | 1635 (1)| 00:00:20 |
- | 5 | NESTED LOOPS | | 1 | 247 | 1633 (1)| 00:00:20 |
- | 6 | NESTED LOOPS | | 1 | 209 | 1630 (1)| 00:00:20 |
- | 7 | NESTED LOOPS OUTER | | 1 | 172 | 1627 (1)| 00:00:20 |
- | 8 | NESTED LOOPS | | 1 | 133 | 1626 (1)| 00:00:20 |
- |* 9 | HASH JOIN | | 1 | 114 | 1625 (1)| 00:00:20 |
- |* 10 | TABLE ACCESS BY INDEX ROWID | PS_PO_LINE_DISTRIB | 1 | 45 | 28 (0)| 00:00:01 |
- | 11 | NESTED LOOPS | | 1 | 87 | 1602 (1)| 00:00:20 |
- | 12 | TABLE ACCESS BY INDEX ROWID| PS_PO_HDR | 568 | 23856 | 396 (0)| 00:00:05 |
- |* 13 | INDEX RANGE SCAN | PS0PO_HDR | 1022 | | 14 (0)| 00:00:01 |
- |* 14 | INDEX RANGE SCAN | PSBPO_LINE_DISTRIB | 80 | | 2 (0)| 00:00:01 |
- | 15 | INDEX FAST FULL SCAN | PS_ING_BARGESPECIF | 12151 | 320K| 22 (0)| 00:00:01 |
- |* 16 | INDEX RANGE SCAN | PS_ING_BARGESPECIF | 1 | 19 | 1 (0)| 00:00:01 |
- |* 17 | INDEX RANGE SCAN | PSAVENDOR | 1 | 39 | 1 (0)| 00:00:01 |
- |* 18 | TABLE ACCESS BY INDEX ROWID | PS_RECV_LN_SHIP | 1 | 37 | 3 (0)| 00:00:01 |
- |* 19 | INDEX RANGE SCAN | PSARECV_LN_SHIP | 1 | | 2 (0)| 00:00:01 |
- |* 20 | TABLE ACCESS BY INDEX ROWID | PS_VOUCHER_LINE | 1 | 38 | 3 (0)| 00:00:01 |
- |* 21 | INDEX RANGE SCAN | PSCVOUCHER_LINE | 1 | | 2 (0)| 00:00:01 |
- |* 22 | INDEX RANGE SCAN | PSJVOUCHER | 1 | 45 | 2 (0)| 00:00:01 |
- | 23 | HASH GROUP BY | | 5 | 840 | 24481 (1)| 00:04:54 |
- | 24 | NESTED LOOPS | | 5 | 840 | 24479 (1)| 00:04:54 |
- |* 25 | HASH JOIN SEMI | | 5 | 685 | 24474 (1)| 00:04:54 |
- | 26 | NESTED LOOPS | | 6 | 774 | 24428 (1)| 00:04:54 |
- | 27 | NESTED LOOPS | | 2409 | 195K| 17196 (1)| 00:03:27 |
- | 28 | INLIST ITERATOR | | | | | |
- |* 29 | TABLE ACCESS BY INDEX ROWID | PS_VOUCHER | 2465 | 137K| 12257 (1)| 00:02:28 |
- |* 30 | INDEX RANGE SCAN | PSJVOUCHER | 4461 | | 10812 (1)| 00:02:10 |
- |* 31 | TABLE ACCESS BY INDEX ROWID | PS_VOUCHER_LINE | 1 | 26 | 3 (0)| 00:00:01 |
- |* 32 | INDEX RANGE SCAN | PS_VOUCHER_LINE | 1 | | 2 (0)| 00:00:01 |
- |* 33 | TABLE ACCESS BY INDEX ROWID | PS_DISTRIB_LINE | 1 | 46 | 3 (0)| 00:00:01 |
- |* 34 | INDEX RANGE SCAN | PS_DISTRIB_LINE | 1 | | 2 (0)| 00:00:01 |
- | 35 | VIEW | VW_NSO_1 | 12151 | 97208 | 45 (3)| 00:00:01 |
- |* 36 | FILTER | | | | | |
- |* 37 | HASH JOIN | | 12151 | 545K| 45 (3)| 00:00:01 |
- | 38 | INDEX FAST FULL SCAN | PS_ING_BARGESPECIF | 12151 | 225K| 22 (0)| 00:00:01 |
- | 39 | INDEX FAST FULL SCAN | PS_ING_BARGESPECIF | 12151 | 320K| 22 (0)| 00:00:01 |
- |* 40 | INDEX RANGE SCAN | PSAVENDOR | 1 | 31 | 1 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - filter(TO_DATE(:V_BEGDATE)<=TO_DATE(:V_ENDDATE))
- 9 - access("A"."ACTIVITY_ID"="ING_VESSEL_ID")
- 10 - filter("A"."DEPTID"='1000' AND ("A"."PROJECT_ID"='CAUS' OR "A"."PROJECT_ID"='CHEM' OR
- "A"."PROJECT_ID"='FUEL' OR "A"."PROJECT_ID"='HOPP' OR "A"."PROJECT_ID"='LIFT' OR
- "A"."PROJECT_ID"='ROLL') AND ("A"."ACCOUNT"='61200' OR "A"."ACCOUNT"='61210' OR
- "A"."ACCOUNT"='61220' OR "A"."ACCOUNT"='61230' OR "A"."ACCOUNT"='61240' OR "A"."ACCOUNT"='61250' OR
- "A"."ACCOUNT"='61260' OR "A"."ACCOUNT"='61270' OR "A"."ACCOUNT"='61280'))
- 13 - access("PO_DT">=:V_BEGDATE AND "PO_DT"<=:V_ENDDATE)
- filter(("BUSINESS_UNIT"='10000' OR "BUSINESS_UNIT"='10200') AND "BUSINESS_UNIT"<>' ' AND
- SYS_OP_UNDESCEND(SYS_OP_DESCEND("PO_ID"))<>' ')
- 14 - access("BUSINESS_UNIT"="A"."BUSINESS_UNIT" AND
- SYS_OP_UNDESCEND(SYS_OP_DESCEND("PO_ID"))="A"."PO_ID")
- filter(("A"."BUSINESS_UNIT"='10000' OR "A"."BUSINESS_UNIT"='10200') AND
- "A"."BUSINESS_UNIT"<>' ' AND "A"."PO_ID"<>' ')
- 16 - access("BUSINESS_UNIT"="BS"."BUSINESS_UNIT" AND "ASSET_ID"="BS"."ASSET_ID")
- 17 - access("SETID"(+)="VENDOR_SETID" AND "VENDOR_ID"(+)="VENDOR_ID")
- 18 - filter("RECV_SHIP_STATUS"<>'X')
- 19 - access("BUSINESS_UNIT_PO"="A"."BUSINESS_UNIT" AND "PO_ID"="A"."PO_ID" AND
- "LINE_NBR"="A"."LINE_NBR" AND "SCHED_NBR"="A"."SCHED_NBR")
- filter(("BUSINESS_UNIT_PO"='10000' OR "BUSINESS_UNIT_PO"='10200') AND "PO_ID"<>' ' AND
- "BUSINESS_UNIT_PO"<>' ')
- 20 - filter(("BUSINESS_UNIT_PO"='10000' OR "BUSINESS_UNIT_PO"='10200') AND "SCHED_NBR"="SCHED_NBR"
- AND "BUSINESS_UNIT_PO"="BUSINESS_UNIT_PO" AND "BUSINESS_UNIT"="BUSINESS_UNIT" AND
- "BUSINESS_UNIT_PO"<>' ')
- 21 - access("PO_ID"="PO_ID" AND "LINE_NBR"="LINE_NBR")
- filter("PO_ID"<>' ')
- 22 - access("BUSINESS_UNIT"(+)="BUSINESS_UNIT" AND "VOUCHER_ID"="VOUCHER_ID"(+))
- 25 - access("ACTIVITY_ID"="$nso_col_1")
- 29 - filter("CLOSE_STATUS"='O' AND "ENTRY_STATUS"<>'X')
- 30 - access(("BUSINESS_UNIT"='10000' OR "BUSINESS_UNIT"='10200') AND "ACCOUNTING_DT">=:V_BEGDATE
- AND "ACCOUNTING_DT"<=:V_ENDDATE)
- filter("ACCOUNTING_DT">=:V_BEGDATE AND "ACCOUNTING_DT"<=:V_ENDDATE)
- 31 - filter("PO_ID"="PO_ID")
- 32 - access("BUSINESS_UNIT"="BUSINESS_UNIT" AND SYS_OP_DESCEND("VOUCHER_ID")=SYS_OP_DESCEND("VOUCHE
- R_ID"))
- filter(("BUSINESS_UNIT"='10000' OR "BUSINESS_UNIT"='10200') AND
- "VOUCHER_ID"=SYS_OP_UNDESCEND(SYS_OP_DESCEND("VOUCHER_ID")))
- 33 - filter("DEPTID"='1000' AND "PO_ID"=' ' AND ("PROJECT_ID"='CAUS' OR "PROJECT_ID"='CHEM' OR
- "PROJECT_ID"='FUEL' OR "PROJECT_ID"='HOPP' OR "PROJECT_ID"='LIFT' OR "PROJECT_ID"='ROLL') AND
- ("ACCOUNT"='61200' OR "ACCOUNT"='61210' OR "ACCOUNT"='61220' OR "ACCOUNT"='61230' OR
- "ACCOUNT"='61240' OR "ACCOUNT"='61250' OR "ACCOUNT"='61260' OR "ACCOUNT"='61270' OR
- "ACCOUNT"='61280'))
- 34 - access("BUSINESS_UNIT"="BUSINESS_UNIT" AND SYS_OP_DESCEND("VOUCHER_ID")=SYS_OP_DESCEND("VOUCHE
- R_ID"))
- filter(("BUSINESS_UNIT"='10000' OR "BUSINESS_UNIT"='10200') AND
- SYS_OP_UNDESCEND(SYS_OP_DESCEND("VOUCHER_ID"))=SYS_OP_UNDESCEND(SYS_OP_DESCEND("VOUCHER_ID")))
- 36 - filter(TO_DATE(:V_BEGDATE)<=TO_DATE(:V_ENDDATE))
- 37 - access("BUSINESS_UNIT"="BS"."BUSINESS_UNIT" AND "ASSET_ID"="BS"."ASSET_ID")
- 40 - access("VENDOR_SETID"="SETID" AND "VENDOR_ID"="VENDOR_ID")
- ========================================================================================================================================
- call count cpu elapsed disk query current rows
- ------- ------ -------- ---------- ---------- ---------- ---------- ----------
- Parse 1 0.00 0.00 0 0 0 0
- Execute 1 0.00 0.00 0 0 0 0
- Fetch 5948 1062.12 1099.26 828190 114987523 0 89200
- ------- ------ -------- ---------- ---------- ---------- ---------- ----------
- total 5950 1062.12 1099.26 828190 114987523 0 89200
- =========================================================================================================================================
- Misses in library cache during parse: 0
- Optimizer mode: ALL_ROWS
- Parsing user id: 40 (TAYLORCD)
- Rows Row Source Operation
- ------- ---------------------------------------------------
- 89200 SORT UNIQUE (cr=114987523 pr=828190 pw=639654 time=964602982 us)
- 56540199 UNION-ALL (cr=114987523 pr=828190 pw=639654 time=1029535891 us)
- 56518999 FILTER (cr=114668656 pr=640956 pw=639654 time=803120662 us)
- 56518999 NESTED LOOPS OUTER (cr=114668656 pr=640956 pw=639654 time=746601628 us)
- 56518999 HASH JOIN OUTER (cr=1627957 pr=640956 pw=639654 time=181411532 us)
- 56518999 HASH JOIN (cr=1627708 pr=1302 pw=0 time=71827121 us)
- 167907 NESTED LOOPS (cr=1627545 pr=1302 pw=0 time=14390462 us)
- 168918 NESTED LOOPS (cr=1120753 pr=1302 pw=0 time=12589955 us)
- 171057 NESTED LOOPS (cr=574310 pr=1302 pw=0 time=8925069 us)
- 177260 HASH JOIN (cr=41639 pr=1216 pw=0 time=4996833 us)
- 86405 TABLE ACCESS FULL PS_PO_LINE_DISTRIB (cr=41476 pr=1216 pw=0 time=6739687 us)
- 12151 INDEX FAST FULL SCAN PS_ING_BARGESPECIF (cr=163 pr=0 pw=0 time=12306 us)(object id 252516)
- 171057 TABLE ACCESS BY INDEX ROWID PS_RECV_LN_SHIP (cr=532671 pr=86 pw=0 time=3767197 us)
- 177406 INDEX RANGE SCAN PSARECV_LN_SHIP (cr=355336 pr=1 pw=0 time=2091060 us)(object id 146221)
- 168918 TABLE ACCESS BY INDEX ROWID PS_VOUCHER_LINE (cr=546443 pr=0 pw=0 time=3069220 us)
- 203923 INDEX RANGE SCAN PSCVOUCHER_LINE (cr=342786 pr=0 pw=0 time=1736322 us)(object id 3415975)
- 167907 TABLE ACCESS BY INDEX ROWID PS_PO_HDR (cr=506792 pr=0 pw=0 time=2566984 us)
- 168918 INDEX UNIQUE SCAN PS_PO_HDR (cr=337838 pr=0 pw=0 time=1299398 us)(object id 254380)
- 12151 INDEX FAST FULL SCAN PS_ING_BARGESPECIF (cr=163 pr=0 pw=0 time=12232 us)(object id 252516)
- 14552 INDEX FAST FULL SCAN PSAVENDOR (cr=249 pr=0 pw=0 time=114 us)(object id 294592)
- 56518999 INDEX RANGE SCAN PSJVOUCHER (cr=113040699 pr=0 pw=0 time=461385758 us)(object id 7430349)
- 21200 HASH GROUP BY (cr=318867 pr=187234 pw=0 time=16025039 us)
- 21401 HASH JOIN (cr=318867 pr=187234 pw=0 time=15959796 us)
- 21401 NESTED LOOPS (cr=318623 pr=187234 pw=0 time=17559529 us)
- 21836 NESTED LOOPS (cr=253107 pr=187234 pw=0 time=13158397 us)
- 21738 HASH JOIN (cr=187762 pr=187234 pw=0 time=12190674 us)
- 6185 VIEW VW_NSO_1 (cr=326 pr=0 pw=0 time=964314 us)
- 6185 HASH UNIQUE (cr=326 pr=0 pw=0 time=958128 us)
- 2395163 FILTER (cr=326 pr=0 pw=0 time=2405754 us)
- 2395163 HASH JOIN (cr=326 pr=0 pw=0 time=10561 us)
- 12151 INDEX FAST FULL SCAN PS_ING_BARGESPECIF (cr=163 pr=0 pw=0 time=120 us)(object id 252516)
- 12151 INDEX FAST FULL SCAN PS_ING_BARGESPECIF (cr=163 pr=0 pw=0 time=55 us)(object id 252516)
- 27233 TABLE ACCESS FULL PS_DISTRIB_LINE (cr=187436 pr=187234 pw=0 time=15507778 us)
- 21836 TABLE ACCESS BY INDEX ROWID PS_VOUCHER_LINE (cr=65345 pr=0 pw=0 time=556317 us)
- 21836 INDEX RANGE SCAN PS_VOUCHER_LINE (cr=43594 pr=0 pw=0 time=356807 us)(object id 4760299)
- 21401 TABLE ACCESS BY INDEX ROWID PS_VOUCHER (cr=65516 pr=0 pw=0 time=448255 us)
- 21836 INDEX UNIQUE SCAN PS_VOUCHER (cr=43674 pr=0 pw=0 time=215730 us)(object id 257125)
- 14552 INDEX FAST FULL SCAN PS0VENDOR (cr=244 pr=0 pw=0 time=87 us)(object id 154642)
- ===============================================================================================
- Rows Execution Plan
- ------- ---------------------------------------------------
- 0 SELECT STATEMENT MODE: ALL_ROWS
- 89200 SORT (UNIQUE)
- 56540199 UNION-ALL
- 56518999 FILTER
- 56518999 NESTED LOOPS (OUTER)
- 56518999 NESTED LOOPS
- 56518999 NESTED LOOPS
- 167907 NESTED LOOPS (OUTER)
- 168918 NESTED LOOPS
- 171057 HASH JOIN
- 177260 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
- OF 'PS_PO_LINE_DISTRIB' (TABLE)
- 86405 NESTED LOOPS
- 12151 TABLE ACCESS MODE: ANALYZED (BY INDEX
- ROWID) OF 'PS_PO_HDR' (TABLE)
- 171057 INDEX MODE: ANALYZED (RANGE SCAN) OF
- 'PS0PO_HDR' (INDEX)
- 177406 INDEX MODE: ANALYZED (RANGE SCAN) OF
- 'PSBPO_LINE_DISTRIB' (INDEX)
- 168918 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
- 'PS_ING_BARGESPECIF' (INDEX (UNIQUE))
- 203923 INDEX MODE: ANALYZED (RANGE SCAN) OF
- 'PS_ING_BARGESPECIF' (INDEX (UNIQUE))
- 167907 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PSAVENDOR'
- (INDEX)
- 168918 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
- 'PS_RECV_LN_SHIP' (TABLE)
- 12151 INDEX MODE: ANALYZED (RANGE SCAN) OF
- 'PSARECV_LN_SHIP' (INDEX)
- 14552 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
- 'PS_VOUCHER_LINE' (TABLE)
- 56518999 INDEX MODE: ANALYZED (RANGE SCAN) OF
- 'PSCVOUCHER_LINE' (INDEX)
- 21200 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PSJVOUCHER'
- (INDEX)
- 21401 HASH (GROUP BY)
- 21401 NESTED LOOPS
- 21836 HASH JOIN (SEMI)
- 21738 NESTED LOOPS
- 6185 NESTED LOOPS
- 6185 INLIST ITERATOR
- 2395163 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
- OF 'PS_VOUCHER' (TABLE)
- 2395163 INDEX MODE: ANALYZED (RANGE SCAN) OF
- 'PSJVOUCHER' (INDEX)
- 12151 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
- 'PS_VOUCHER_LINE' (TABLE)
- 12151 INDEX MODE: ANALYZED (RANGE SCAN) OF
- 'PS_VOUCHER_LINE' (INDEX (UNIQUE))
- 27233 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
- 'PS_DISTRIB_LINE' (TABLE)
- 21836 INDEX MODE: ANALYZED (RANGE SCAN) OF
- 'PS_DISTRIB_LINE' (INDEX (UNIQUE))
- 21836 VIEW OF 'VW_NSO_1' (VIEW)
- 21401 FILTER
- 21836 HASH JOIN
- 14552 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
- 'PS_ING_BARGESPECIF' (INDEX (UNIQUE))
- 0 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
- 'PS_ING_BARGESPECIF' (INDEX (UNIQUE))
- 0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PSAVENDOR'
- (INDEX)
- Elapsed times include waiting on following events:
- Event waited on Times Max. Wait Total Waited
- ---------------------------------------- Waited ---------- ------------
- SQL*Net message to client 5948 0.00 0.00
- db file sequential read 1249 0.01 4.35
- db file scattered read 1634 0.10 9.65
- direct path write temp 20634 0.04 8.38
- direct path read temp 20634 0.51 12.45
- latch: cache buffers chains 1 0.00 0.00
- SQL*Net message from client 5948 296.15 316.09
- ********************************************************************************
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement