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