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
********************************************************************************