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 barge_type,
project_id,
po_id,
activity_id,
po_line_amt,
rec_ln_amt,
voucher_ln_amt,
voucher_id,
accounting_dt,
accounting_mo,
accounting_yr,
vendor_id,
Vendor_Name,
Account,
invoice_id,
business_unit
FROM
(
SELECT barge_type
, project_id
, po_id
, activity_id
, po_line_amt
, rec_ln_amt
, voucher_ln_amt
, voucher_id
, accounting_dt
, accounting_mo
, accounting_yr
, vendor_id
, vendor_name
, account
, invoice_id
, business_unit
, row_Number() over (partition by barge_Type, project_id, po_id, activity_id, po_line_amt, rec_ln_amt, voucher_ln_amt, voucher_id, accounting_dt, accounting_mo, accounting_yr, vendor_id, vendor_name, account, invoice_id, business_unit order by accounting_yr, accounting_dt, barge_Type, project_id, account) as row_key
from
(
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)
)
WHERE row_key = 1
/
89200 rows selected.
Elapsed: 00:00:50.99
==========================================================================================================================================
Plan hash value: 575431646
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1008 | 26138 (1)| 00:05:14 |
|* 1 | VIEW | | 6 | 1008 | 26138 (1)| 00:05:14 |
|* 2 | WINDOW SORT PUSHED RANK | | 6 | 930 | 26138 (1)| 00:05:14 |
| 3 | VIEW | | 6 | 930 | 26138 (1)| 00:05:14 |
| 4 | SORT ORDER BY | | 6 | 1092 | 26137 (94)| 00:05:14 |
| 5 | UNION-ALL | | | | | |
|* 6 | HASH JOIN SEMI | | 1 | 252 | 1657 (1)| 00:00:20 |
| 7 | NESTED LOOPS OUTER | | 1 | 246 | 1611 (1)| 00:00:20 |
| 8 | NESTED LOOPS | | 1 | 201 | 1609 (1)| 00:00:20 |
| 9 | NESTED LOOPS | | 1 | 163 | 1606 (1)| 00:00:20 |
| 10 | NESTED LOOPS OUTER | | 1 | 126 | 1603 (1)| 00:00:20 |
| 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 | TABLE ACCESS BY INDEX ROWID| PS_PO_LINE_DISTRIB | 1 | 45 | 28 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | PSBPO_LINE_DISTRIB | 80 | | 2 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | PSAVENDOR | 1 | 39 | 1 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | PS_RECV_LN_SHIP | 1 | 37 | 3 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | PSARECV_LN_SHIP | 1 | | 2 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID | PS_VOUCHER_LINE | 1 | 38 | 3 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | PSCVOUCHER_LINE | 1 | | 2 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | PSJVOUCHER | 1 | 45 | 2 (0)| 00:00:01 |
| 22 | VIEW | VW_NSO_1 | 12151 | 72906 | 45 (3)| 00:00:01 |
|* 23 | FILTER | | | | | |
|* 24 | HASH JOIN | | 12151 | 545K| 45 (3)| 00:00:01 |
| 25 | INDEX FAST FULL SCAN | PS_ING_BARGESPECIF | 12151 | 225K| 22 (0)| 00:00:01 |
| 26 | INDEX FAST FULL SCAN | PS_ING_BARGESPECIF | 12151 | 320K| 22 (0)| 00:00:01 |
| 27 | HASH GROUP BY | | 5 | 840 | 24480 (1)| 00:04:54 |
| 28 | NESTED LOOPS | | 5 | 840 | 24479 (1)| 00:04:54 |
|* 29 | HASH JOIN SEMI | | 5 | 685 | 24474 (1)| 00:04:54 |
| 30 | NESTED LOOPS | | 6 | 774 | 24428 (1)| 00:04:54 |
| 31 | NESTED LOOPS | | 2409 | 195K| 17196 (1)| 00:03:27 |
| 32 | INLIST ITERATOR | | | | | |
|* 33 | TABLE ACCESS BY INDEX ROWID| PS_VOUCHER | 2465 | 137K| 12257 (1)| 00:02:28 |
|* 34 | INDEX RANGE SCAN | PSJVOUCHER | 4461 | | 10812 (1)| 00:02:10 |
|* 35 | TABLE ACCESS BY INDEX ROWID | PS_VOUCHER_LINE | 1 | 26 | 3 (0)| 00:00:01 |
|* 36 | INDEX RANGE SCAN | PS_VOUCHER_LINE | 1 | | 2 (0)| 00:00:01 |
|* 37 | TABLE ACCESS BY INDEX ROWID | PS_DISTRIB_LINE | 1 | 46 | 3 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN | PS_DISTRIB_LINE | 1 | | 2 (0)| 00:00:01 |
| 39 | VIEW | VW_NSO_2 | 12151 | 97208 | 45 (3)| 00:00:01 |
|* 40 | FILTER | | | | | |
|* 41 | HASH JOIN | | 12151 | 545K| 45 (3)| 00:00:01 |
| 42 | INDEX FAST FULL SCAN | PS_ING_BARGESPECIF | 12151 | 225K| 22 (0)| 00:00:01 |
| 43 | INDEX FAST FULL SCAN | PS_ING_BARGESPECIF | 12151 | 320K| 22 (0)| 00:00:01 |
|* 44 | INDEX RANGE SCAN | PSAVENDOR | 1 | 31 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_KEY"=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "BARGE_TYPE","PROJECT_ID","PO_ID","ACTIVITY_ID","PO_LI
NE_AMT","REC_LN_AMT","VOUCHER_LN_AMT","VOUCHER_ID","ACCOUNTING_DT","ACCOUNTING_MO","ACCOUNTING_YR","V
ENDOR_ID","VENDOR_NAME","ACCOUNT","INVOICE_ID","BUSINESS_UNIT" ORDER BY
"ACCOUNTING_YR","ACCOUNTING_DT","BARGE_TYPE","PROJECT_ID","ACCOUNT")<=1)
6 - access("A"."ACTIVITY_ID"="$nso_col_1")
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 - 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'))
15 - 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("SETID"(+)="VENDOR_SETID" AND "VENDOR_ID"(+)="VENDOR_ID")
17 - filter("RECV_SHIP_STATUS"<>'X')
18 - 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"<>' ')
19 - 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"<>' ')
20 - access("PO_ID"="PO_ID" AND "LINE_NBR"="LINE_NBR")
filter("PO_ID"<>' ')
21 - access("BUSINESS_UNIT"(+)="BUSINESS_UNIT" AND "VOUCHER_ID"="VOUCHER_ID"(+))
23 - filter(TO_DATE(:V_BEGDATE)<=TO_DATE(:V_ENDDATE))
24 - access("BUSINESS_UNIT"="BS"."BUSINESS_UNIT" AND "ASSET_ID"="BS"."ASSET_ID")
29 - access("ACTIVITY_ID"="$nso_col_1")
33 - filter("CLOSE_STATUS"='O' AND "ENTRY_STATUS"<>'X')
34 - 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)
35 - filter("PO_ID"="PO_ID")
36 - 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")))
37 - 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'))
38 - 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")))
40 - filter(TO_DATE(:V_BEGDATE)<=TO_DATE(:V_ENDDATE))
41 - access("BUSINESS_UNIT"="BS"."BUSINESS_UNIT" AND "ASSET_ID"="BS"."ASSET_ID")
44 - 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 14.92 22.25 187215 1146077 0 89200
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5950 14.92 22.25 187215 1146077 0 89200
=======================================================================================================================================
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 40 (TAYLORCD)
Rows Row Source Operation
------- ---------------------------------------------------
89200 VIEW (cr=1146077 pr=187215 pw=0 time=21962100 us)
89276 WINDOW SORT PUSHED RANK (cr=1146077 pr=187215 pw=0 time=21873031 us)
89276 VIEW (cr=1146077 pr=187215 pw=0 time=21427534 us)
89276 SORT ORDER BY (cr=1146077 pr=187215 pw=0 time=21338253 us)
89276 UNION-ALL (cr=1146077 pr=187215 pw=0 time=6430479 us)
68076 NESTED LOOPS OUTER (cr=827243 pr=0 pw=0 time=6086770 us)
68076 HASH JOIN OUTER (cr=690255 pr=0 pw=0 time=5337879 us)
68076 NESTED LOOPS (cr=690006 pr=0 pw=0 time=5166378 us)
68911 NESTED LOOPS (cr=483245 pr=0 pw=0 time=4108755 us)
70025 NESTED LOOPS (cr=260561 pr=0 pw=0 time=2890621 us)
72958 HASH JOIN (cr=41802 pr=0 pw=0 time=1562965 us)
86405 TABLE ACCESS FULL PS_PO_LINE_DISTRIB (cr=41476 pr=0 pw=0 time=518574 us)
6185 VIEW VW_NSO_1 (cr=326 pr=0 pw=0 time=907222 us)
6185 HASH UNIQUE (cr=326 pr=0 pw=0 time=901012 us)
2395163 FILTER (cr=326 pr=0 pw=0 time=2406555 us)
2395163 HASH JOIN (cr=326 pr=0 pw=0 time=11359 us)
12151 INDEX FAST FULL SCAN PS_ING_BARGESPECIF (cr=163 pr=0 pw=0 time=116 us)(object id 252516)
12151 INDEX FAST FULL SCAN PS_ING_BARGESPECIF (cr=163 pr=0 pw=0 time=55 us)(object id 252516)
70025 TABLE ACCESS BY INDEX ROWID PS_RECV_LN_SHIP (cr=218759 pr=0 pw=0 time=1240971 us)
72349 INDEX RANGE SCAN PSARECV_LN_SHIP (cr=146454 pr=0 pw=0 time=768246 us)(object id 146221)
68911 TABLE ACCESS BY INDEX ROWID PS_VOUCHER_LINE (cr=222684 pr=0 pw=0 time=1200922 us)
82352 INDEX RANGE SCAN PSCVOUCHER_LINE (cr=140494 pr=0 pw=0 time=684626 us)(object id 3415975)
68076 TABLE ACCESS BY INDEX ROWID PS_PO_HDR (cr=206761 pr=0 pw=0 time=975453 us)
68911 INDEX UNIQUE SCAN PS_PO_HDR (cr=137824 pr=0 pw=0 time=501150 us)(object id 254380)
14552 INDEX FAST FULL SCAN PSAVENDOR (cr=249 pr=0 pw=0 time=55 us)(object id 294592)
68076 INDEX RANGE SCAN PSJVOUCHER (cr=136988 pr=0 pw=0 time=615636 us)(object id 7430349)
21200 HASH GROUP BY (cr=318834 pr=187215 pw=0 time=14887042 us)
21401 HASH JOIN (cr=318834 pr=187215 pw=0 time=14824476 us)
21401 NESTED LOOPS (cr=318590 pr=187215 pw=0 time=14843653 us)
21836 NESTED LOOPS (cr=253080 pr=187215 pw=0 time=12617061 us)
21738 HASH JOIN (cr=187735 pr=187215 pw=0 time=12694593 us)
6185 VIEW VW_NSO_2 (cr=326 pr=0 pw=0 time=827724 us)
6185 HASH UNIQUE (cr=326 pr=0 pw=0 time=821537 us)
2395163 FILTER (cr=326 pr=0 pw=0 time=2402386 us)
2395163 HASH JOIN (cr=326 pr=0 pw=0 time=7192 us)
12151 INDEX FAST FULL SCAN PS_ING_BARGESPECIF (cr=163 pr=0 pw=0 time=137 us)(object id 252516)
12151 INDEX FAST FULL SCAN PS_ING_BARGESPECIF (cr=163 pr=0 pw=0 time=41 us)(object id 252516)
27233 TABLE ACCESS FULL PS_DISTRIB_LINE (cr=187409 pr=187215 pw=0 time=12304244 us)
21836 TABLE ACCESS BY INDEX ROWID PS_VOUCHER_LINE (cr=65345 pr=0 pw=0 time=540589 us)
21836 INDEX RANGE SCAN PS_VOUCHER_LINE (cr=43594 pr=0 pw=0 time=348998 us)(object id 4760299)
21401 TABLE ACCESS BY INDEX ROWID PS_VOUCHER (cr=65510 pr=0 pw=0 time=434654 us)
21836 INDEX UNIQUE SCAN PS_VOUCHER (cr=43674 pr=0 pw=0 time=207283 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
89200 VIEW
89276 WINDOW (SORT PUSHED RANK)
89276 VIEW
89276 SORT (ORDER BY)
89276 UNION-ALL
68076 HASH JOIN (SEMI)
68076 NESTED LOOPS (OUTER)
68076 NESTED LOOPS
68911 NESTED LOOPS
70025 NESTED LOOPS (OUTER)
72958 NESTED LOOPS
86405 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)
70025 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'PS_VOUCHER_LINE' (TABLE)
72349 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PSCVOUCHER_LINE' (INDEX)
68911 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PSJVOUCHER' (INDEX)
82352 VIEW OF 'VW_NSO_1' (VIEW)
68076 FILTER
68911 HASH JOIN
14552 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'PS_ING_BARGESPECIF' (INDEX (UNIQUE))
68076 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'PS_ING_BARGESPECIF' (INDEX (UNIQUE))
21200 HASH (GROUP BY)
21401 NESTED LOOPS
21401 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
21401 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 5948 0.00 0.00
db file scattered read 1612 0.05 8.88
db file sequential read 7 0.00 0.00
SQL*Net message from client 5948 52.36 80.63
********************************************************************************