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 ALL
- 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
- /
- 90227 rows selected.
- Elapsed: 00:01:03.66
- ==================================================================================================================================
- Plan hash value: 1949711655
- ----------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 6 | 1092 | 26138 (1)| 00:05:14 |
- | 1 | SORT ORDER BY | | 6 | 1092 | 26137 (94)| 00:05:14 |
- | 2 | UNION-ALL | | | | | |
- |* 3 | HASH JOIN SEMI | | 1 | 252 | 1657 (1)| 00:00:20 |
- | 4 | NESTED LOOPS OUTER | | 1 | 246 | 1611 (1)| 00:00:20 |
- | 5 | NESTED LOOPS | | 1 | 201 | 1609 (1)| 00:00:20 |
- | 6 | NESTED LOOPS | | 1 | 163 | 1606 (1)| 00:00:20 |
- | 7 | NESTED LOOPS OUTER | | 1 | 126 | 1603 (1)| 00:00:20 |
- | 8 | NESTED LOOPS | | 1 | 87 | 1602 (1)| 00:00:20 |
- | 9 | TABLE ACCESS BY INDEX ROWID| PS_PO_HDR | 568 | 23856 | 396 (0)| 00:00:05 |
- |* 10 | INDEX RANGE SCAN | PS0PO_HDR | 1022 | | 14 (0)| 00:00:01 |
- |* 11 | TABLE ACCESS BY INDEX ROWID| PS_PO_LINE_DISTRIB | 1 | 45 | 28 (0)| 00:00:01 |
- |* 12 | INDEX RANGE SCAN | PSBPO_LINE_DISTRIB | 80 | | 2 (0)| 00:00:01 |
- |* 13 | INDEX RANGE SCAN | PSAVENDOR | 1 | 39 | 1 (0)| 00:00:01 |
- |* 14 | TABLE ACCESS BY INDEX ROWID | PS_RECV_LN_SHIP | 1 | 37 | 3 (0)| 00:00:01 |
- |* 15 | INDEX RANGE SCAN | PSARECV_LN_SHIP | 1 | | 2 (0)| 00:00:01 |
- |* 16 | TABLE ACCESS BY INDEX ROWID | PS_VOUCHER_LINE | 1 | 38 | 3 (0)| 00:00:01 |
- |* 17 | INDEX RANGE SCAN | PSCVOUCHER_LINE | 1 | | 2 (0)| 00:00:01 |
- |* 18 | INDEX RANGE SCAN | PSJVOUCHER | 1 | 45 | 2 (0)| 00:00:01 |
- | 19 | VIEW | VW_NSO_1 | 12151 | 72906 | 45 (3)| 00:00:01 |
- |* 20 | FILTER | | | | | |
- |* 21 | HASH JOIN | | 12151 | 545K| 45 (3)| 00:00:01 |
- | 22 | INDEX FAST FULL SCAN | PS_ING_BARGESPECIF | 12151 | 225K| 22 (0)| 00:00:01 |
- | 23 | INDEX FAST FULL SCAN | PS_ING_BARGESPECIF | 12151 | 320K| 22 (0)| 00:00:01 |
- | 24 | HASH GROUP BY | | 5 | 840 | 24480 (1)| 00:04:54 |
- | 25 | NESTED LOOPS | | 5 | 840 | 24479 (1)| 00:04:54 |
- |* 26 | HASH JOIN SEMI | | 5 | 685 | 24474 (1)| 00:04:54 |
- | 27 | NESTED LOOPS | | 6 | 774 | 24428 (1)| 00:04:54 |
- | 28 | NESTED LOOPS | | 2409 | 195K| 17196 (1)| 00:03:27 |
- | 29 | INLIST ITERATOR | | | | | |
- |* 30 | TABLE ACCESS BY INDEX ROWID| PS_VOUCHER | 2465 | 137K| 12257 (1)| 00:02:28 |
- |* 31 | INDEX RANGE SCAN | PSJVOUCHER | 4461 | | 10812 (1)| 00:02:10 |
- |* 32 | TABLE ACCESS BY INDEX ROWID | PS_VOUCHER_LINE | 1 | 26 | 3 (0)| 00:00:01 |
- |* 33 | INDEX RANGE SCAN | PS_VOUCHER_LINE | 1 | | 2 (0)| 00:00:01 |
- |* 34 | TABLE ACCESS BY INDEX ROWID | PS_DISTRIB_LINE | 1 | 46 | 3 (0)| 00:00:01 |
- |* 35 | INDEX RANGE SCAN | PS_DISTRIB_LINE | 1 | | 2 (0)| 00:00:01 |
- | 36 | VIEW | VW_NSO_2 | 12151 | 97208 | 45 (3)| 00:00:01 |
- |* 37 | FILTER | | | | | |
- |* 38 | HASH JOIN | | 12151 | 545K| 45 (3)| 00:00:01 |
- | 39 | INDEX FAST FULL SCAN | PS_ING_BARGESPECIF | 12151 | 225K| 22 (0)| 00:00:01 |
- | 40 | INDEX FAST FULL SCAN | PS_ING_BARGESPECIF | 12151 | 320K| 22 (0)| 00:00:01 |
- |* 41 | INDEX RANGE SCAN | PSAVENDOR | 1 | 31 | 1 (0)| 00:00:01 |
- ----------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access("A"."ACTIVITY_ID"="$nso_col_1")
- 10 - 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"))<>' ')
- 11 - 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'))
- 12 - 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"<>' ')
- 13 - access("SETID"(+)="VENDOR_SETID" AND "VENDOR_ID"(+)="VENDOR_ID")
- 14 - filter("RECV_SHIP_STATUS"<>'X')
- 15 - 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"<>' ')
- 16 - 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"<>' ')
- 17 - access("PO_ID"="PO_ID" AND "LINE_NBR"="LINE_NBR")
- filter("PO_ID"<>' ')
- 18 - access("BUSINESS_UNIT"(+)="BUSINESS_UNIT" AND "VOUCHER_ID"="VOUCHER_ID"(+))
- 20 - filter(TO_DATE(:V_BEGDATE)<=TO_DATE(:V_ENDDATE))
- 21 - access("BUSINESS_UNIT"="BS"."BUSINESS_UNIT" AND "ASSET_ID"="BS"."ASSET_ID")
- 26 - access("ACTIVITY_ID"="$nso_col_1")
- 30 - filter("CLOSE_STATUS"='O' AND "ENTRY_STATUS"<>'X')
- 31 - 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)
- 32 - filter("PO_ID"="PO_ID")
- 33 - access("BUSINESS_UNIT"="BUSINESS_UNIT" AND
- SYS_OP_DESCEND("VOUCHER_ID")=SYS_OP_DESCEND("VOUCHER_ID"))
- filter(("BUSINESS_UNIT"='10000' OR "BUSINESS_UNIT"='10200') AND
- "VOUCHER_ID"=SYS_OP_UNDESCEND(SYS_OP_DESCEND("VOUCHER_ID")))
- 34 - 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'))
- 35 - access("BUSINESS_UNIT"="BUSINESS_UNIT" AND
- SYS_OP_DESCEND("VOUCHER_ID")=SYS_OP_DESCEND("VOUCHER_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")))
- 37 - filter(TO_DATE(:V_BEGDATE)<=TO_DATE(:V_ENDDATE))
- 38 - access("BUSINESS_UNIT"="BS"."BUSINESS_UNIT" AND "ASSET_ID"="BS"."ASSET_ID")
- 41 - 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 6017 14.25 24.30 187607 1147312 0 90227
- ------- ------ -------- ---------- ---------- ---------- ---------- ----------
- total 6019 14.25 24.30 187607 1147312 0 90227
- ==============================================================================================================================================
- Misses in library cache during parse: 0
- Optimizer mode: ALL_ROWS
- Parsing user id: 40 (TAYLORCD)
- Rows Row Source Operation
- ------- ---------------------------------------------------
- 90227 SORT ORDER BY (cr=1147312 pr=187607 pw=0 time=23990450 us)
- 90227 UNION-ALL (cr=1147312 pr=187607 pw=0 time=7785516 us)
- 68821 NESTED LOOPS OUTER (cr=828478 pr=183 pw=0 time=7416978 us)
- 68821 HASH JOIN OUTER (cr=689981 pr=182 pw=0 time=6591071 us)
- 68821 NESTED LOOPS (cr=689732 pr=182 pw=0 time=6359417 us)
- 68850 NESTED LOOPS (cr=483154 pr=182 pw=0 time=5191006 us)
- 70017 NESTED LOOPS (cr=260549 pr=6 pw=0 time=2874610 us)
- 72956 HASH JOIN (cr=41802 pr=0 pw=0 time=1477115 us)
- 86403 TABLE ACCESS FULL PS_PO_LINE_DISTRIB (cr=41476 pr=0 pw=0 time=518552 us)
- 6185 VIEW VW_NSO_1 (cr=326 pr=0 pw=0 time=827568 us)
- 6185 HASH UNIQUE (cr=326 pr=0 pw=0 time=821350 us)
- 2395163 FILTER (cr=326 pr=0 pw=0 time=2403020 us)
- 2395163 HASH JOIN (cr=326 pr=0 pw=0 time=7828 us)
- 12151 INDEX FAST FULL SCAN PS_ING_BARGESPECIF (cr=163 pr=0 pw=0 time=106 us)(object id 252516)
- 12151 INDEX FAST FULL SCAN PS_ING_BARGESPECIF (cr=163 pr=0 pw=0 time=40 us)(object id 252516)
- 70017 TABLE ACCESS BY INDEX ROWID PS_RECV_LN_SHIP (cr=218747 pr=6 pw=0 time=1418991 us)
- 72341 INDEX RANGE SCAN PSARECV_LN_SHIP (cr=146450 pr=4 pw=0 time=909687 us)(object id 146221)
- 68850 TABLE ACCESS BY INDEX ROWID PS_VOUCHER_LINE (cr=222605 pr=176 pw=0 time=2272316 us)
- 82289 INDEX RANGE SCAN PSCVOUCHER_LINE (cr=140478 pr=2 pw=0 time=736814 us)(object id 3415975)
- 68821 TABLE ACCESS BY INDEX ROWID PS_PO_HDR (cr=206578 pr=0 pw=0 time=1033995 us)
- 68850 INDEX UNIQUE SCAN PS_PO_HDR (cr=137702 pr=0 pw=0 time=529286 us)(object id 254380)
- 14552 INDEX FAST FULL SCAN PSAVENDOR (cr=249 pr=0 pw=0 time=58 us)(object id 294592)
- 68821 INDEX RANGE SCAN PSJVOUCHER (cr=138497 pr=1 pw=0 time=703004 us)(object id 7430349)
- 21406 HASH GROUP BY (cr=318834 pr=187424 pw=0 time=16054781 us)
- 21607 HASH JOIN (cr=318834 pr=187424 pw=0 time=15992088 us)
- 21607 NESTED LOOPS (cr=318590 pr=187424 pw=0 time=14385857 us)
- 21836 NESTED LOOPS (cr=253080 pr=187415 pw=0 time=12540318 us)
- 21738 HASH JOIN (cr=187735 pr=187181 pw=0 time=16053451 us)
- 6185 VIEW VW_NSO_2 (cr=326 pr=0 pw=0 time=1024930 us)
- 6185 HASH UNIQUE (cr=326 pr=0 pw=0 time=1018743 us)
- 2395163 FILTER (cr=326 pr=0 pw=0 time=2405517 us)
- 2395163 HASH JOIN (cr=326 pr=0 pw=0 time=10320 us)
- 12151 INDEX FAST FULL SCAN PS_ING_BARGESPECIF (cr=163 pr=0 pw=0 time=146 us)(object id 252516)
- 12151 INDEX FAST FULL SCAN PS_ING_BARGESPECIF (cr=163 pr=0 pw=0 time=53 us)(object id 252516)
- 27233 TABLE ACCESS FULL PS_DISTRIB_LINE (cr=187409 pr=187181 pw=0 time=13136606 us)
- 21836 TABLE ACCESS BY INDEX ROWID PS_VOUCHER_LINE (cr=65345 pr=234 pw=0 time=1659266 us)
- 21836 INDEX RANGE SCAN PS_VOUCHER_LINE (cr=43594 pr=102 pw=0 time=865410 us)(object id 4760299)
- 21607 TABLE ACCESS BY INDEX ROWID PS_VOUCHER (cr=65510 pr=9 pw=0 time=480644 us)
- 21836 INDEX UNIQUE SCAN PS_VOUCHER (cr=43674 pr=9 pw=0 time=250201 us)(object id 257125)
- 14552 INDEX FAST FULL SCAN PS0VENDOR (cr=244 pr=0 pw=0 time=80 us)(object id 154642)
- ============================================================================================================================
- Rows Execution Plan
- ------- ---------------------------------------------------
- 0 SELECT STATEMENT MODE: ALL_ROWS
- 90227 SORT (ORDER BY)
- 90227 UNION-ALL
- 68821 HASH JOIN (SEMI)
- 68821 NESTED LOOPS (OUTER)
- 68821 NESTED LOOPS
- 68850 NESTED LOOPS
- 70017 NESTED LOOPS (OUTER)
- 72956 NESTED LOOPS
- 86403 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
- OF 'PS_PO_HDR' (TABLE)
- 6185 INDEX MODE: ANALYZED (RANGE SCAN) OF
- 'PS0PO_HDR' (INDEX)
- 6185 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
- OF 'PS_PO_LINE_DISTRIB' (TABLE)
- 2395163 INDEX MODE: ANALYZED (RANGE SCAN) OF
- 'PSBPO_LINE_DISTRIB' (INDEX)
- 2395163 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PSAVENDOR'
- (INDEX)
- 12151 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
- 'PS_RECV_LN_SHIP' (TABLE)
- 12151 INDEX MODE: ANALYZED (RANGE SCAN) OF
- 'PSARECV_LN_SHIP' (INDEX)
- 70017 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
- 'PS_VOUCHER_LINE' (TABLE)
- 72341 INDEX MODE: ANALYZED (RANGE SCAN) OF
- 'PSCVOUCHER_LINE' (INDEX)
- 68850 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PSJVOUCHER'
- (INDEX)
- 82289 VIEW OF 'VW_NSO_1' (VIEW)
- 68821 FILTER
- 68850 HASH JOIN
- 14552 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
- 'PS_ING_BARGESPECIF' (INDEX (UNIQUE))
- 68821 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
- 'PS_ING_BARGESPECIF' (INDEX (UNIQUE))
- 21406 HASH (GROUP BY)
- 21607 NESTED LOOPS
- 21607 HASH JOIN (SEMI)
- 21836 NESTED LOOPS
- 21738 NESTED LOOPS
- 6185 INLIST ITERATOR
- 6185 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
- OF 'PS_VOUCHER' (TABLE)
- 2395163 INDEX MODE: ANALYZED (RANGE SCAN) OF
- 'PSJVOUCHER' (INDEX)
- 2395163 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
- 'PS_VOUCHER_LINE' (TABLE)
- 12151 INDEX MODE: ANALYZED (RANGE SCAN) OF
- 'PS_VOUCHER_LINE' (INDEX (UNIQUE))
- 12151 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
- 'PS_DISTRIB_LINE' (TABLE)
- 27233 INDEX MODE: ANALYZED (RANGE SCAN) OF
- 'PS_DISTRIB_LINE' (INDEX (UNIQUE))
- 21836 VIEW OF 'VW_NSO_2' (VIEW)
- 21836 FILTER
- 21607 HASH JOIN
- 21836 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
- 'PS_ING_BARGESPECIF' (INDEX (UNIQUE))
- 14552 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 6017 0.00 0.00
- db file sequential read 433 0.03 2.14
- db file scattered read 1633 0.06 8.75
- SQL*Net message from client 6017 19.99 58.72
- ********************************************************************************
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement