1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
SELECT
'Y' as selected,
CASE PK_INTERNET.get_doc_version(o.order_id)
WHEN '2' THEN o.outerid
WHEN '4' THEN o.outerid
WHEN '3' THEN opv7.opvalue
END as outerid,
o.outerid as konosament,
CASE
WHEN o.order_id IN (
SELECT osl1.order_id
FROM o_status_log osl1,
o_status_log osl2,
orders o
WHERE osl1.order_id = osl2.order_id
AND osl1.os_code = 'WRQST_CRTD'
AND osl2.os_code = 'WRQST_WAIT'
AND osl2.osl_id < osl1.osl_id
AND osl1.order_id = o.order_id
AND o.os_code = 'WRQST_CRTD')
THEN 'Y'
ELSE 'N'
END AS repeat,
o.os_code,
DECODE(os.os_code,
'DOC_STORN', DECODE(o.hasremoteitems, 'Y', 'Çàêàç îòìåíåí êëèåíòîì (ÓÑ)',
CASE
WHEN (SELECT DISTINCT (og.order_id)
FROM o_good_item og
WHERE og.order_id = o.order_id
AND og.quantity > 0
AND NVL(og.st_code, 'N') <> 'TRASH') IS NULL THEN 'Äîêóìåíò ñòîðíèðîâàí'
ELSE 'Çàêàç îòìåíåí êëèåíòîì (ËÑ)'
END),
'ZEXPIRED', DECODE(o.hasremoteitems, 'Y', 'Çàêàç îòìåíåí. Èñòåê ñðîê õðàíåíèÿ òîâàðà (ÓÑ)',
'Çàêàç îòìåíåí. Èñòåê ñðîê õðàíåíèÿ òîâàðà (ËÑ)'),
os.NAME) AS os_name,
os.os_comment as action,
o.chainnum, o.docdate,
TO_CHAR (ord.order_id) AS cust_ord_id,
CASE WHEN (o.os_code = 'WRQST_CRTD'
and exists(select *
from staff t, tsys_profiles prf, tsys_usrprof_link plk
where plk.staff_id = t.staff_id and
prf.prof_code = plk.prof_code and
prf.prof_code <> 'SELLER' and
(t.user_code = PK_MASTERDATA.GET_USERCODE))) OR
(o.os_code = 'WRQST_ACPT'
AND ord.os_code = 'ZORDR_OPEN'
AND NVL(opv9.opvalue,'N') <> 'Y')
AND (NVL(b_stop.opvalue, 'N') <> 'Y')
THEN
( CASE PK_INTERNET.get_doc_version(o.order_id)
WHEN '4' THEN 'Z'
ELSE 'Y'
END
)
ELSE 'N'
END AS LCHNG_ACCESS,
CASE WHEN ((o.os_code = 'PWRQT_DLVD') or
(o.os_code = 'PWRQT_DLVR'
and exists(select *
from staff t, tsys_profiles prf, tsys_usrprof_link plk
where plk.staff_id = t.staff_id and
prf.prof_code = plk.prof_code and
prf.prof_code NOT IN ('SELLER', 'STOCKMAN') and
(t.user_code = PK_MASTERDATA.GET_USERCODE))))
AND ord.os_code = 'ZORDR_OPEN'
AND (NVL(b_stop.opvalue, 'N') <> 'Y')
AND NVL(opv9.opvalue,'N') <> 'Y'
THEN 'Y' ELSE 'N'
END AS RCHNG_ACCESS,
orders_proc.fn_get_RDLVD_access(ord.order_id, o.order_id, ord3.order_id) RDLVD_ACCESS,
CASE WHEN ord.os_code = 'ZORDR_PAYD' AND
(o.hasremoteitems IS NULL OR o.hasremoteitems = 'N')
THEN 'Y' ELSE 'N'
END AS LRCVD_ACCESS,
CASE WHEN (ord.os_code = 'ZORDR_PAYD' AND
o.hasremoteitems = 'Y')
THEN 'Y' ELSE 'N'
END AS RRCVD_ACCESS,
(SELECT COUNT (*)
FROM o_good_item og
WHERE og.order_id = o.order_id) AS ccount,
(SELECT SUM (og.quantity)
FROM o_good_item og
WHERE og.order_id = o.order_id) AS qnt,
(SELECT SUM (amount)
FROM o_good_item
WHERE ignore <> 'Y' AND order_id = o.order_id) AS o_amnt,
o.changedatetime, o.finish_date AS finish_date, o.changeusercode,
TO_CHAR (o.order_id) AS order_id,
TO_CHAR(ord2.order_id) as ZDLVGI_NUM,
os.color,
ord3.order_id as ZDLVGR_ID,
opv5.opvalue as ZDLVGR_SAPID,
CASE
WHEN ord2.os_code = 'ZDLVGI_FIN' THEN 'Y' ELSE 'N'
END AS ZDLVGI_FIN,
CASE WHEN (nvl(opv9.opvalue,'N') = 'N') or (o.os_code IN ('WRQST_CRTD','PWRQT_DLVR','PWRQT_DLVD'))
THEN 'Y' ELSE 'N'
END AS PRNT_ACCESS,
nvl(opv8.opvalue,'N') AS LEGAL_PERSON,
(case
when (select nvl(max(opvw.opvalue),'') as op_code from op_values opvw where opvw.op_code = 'IM_WERK' and opvw.order_id = o.order_id)=get_werk_base then 'Y'
else 'N' end)as wbtrmnl,
nvl(opv9.opvalue,'N') AS ONLINE_PAY,
CASE WHEN ( o.os_code IN ('WRQST_ACPT', 'PWRQT_DLVD') AND nvl(opv9.opvalue,'N') = 'Y'
AND exists(SELECT * FROM orders zpp WHERE zpp.parent_order_id = o.order_id
AND zpp.ot_code = 'ZPPAY'
AND zpp.os_code = 'ZPPAY_CRTD'))
THEN 'Y' ELSE 'N'
END AS ONLINE_ACCESS,
CASE WHEN ( o.os_code IN ('WRQST_RCVD', 'PWRQT_PRCH') AND nvl(opv9.opvalue,'N') = 'Y'
AND exists(SELECT * FROM orders zpp WHERE zpp.parent_order_id = o.order_id
AND zpp.ot_code = 'ZPPAY'
AND zpp.os_code = 'ZPPAY_CRTD'))
THEN 'Y' ELSE 'N'
END AS ONLINE_STORNO_ACCESS,
CASE WHEN ( o.os_code IN ('WRQST_ACPT', 'PWRQT_DLVD', 'WRQST_RCVD', 'PWRQT_PRCH') AND nvl(opv9.opvalue,'N') = 'Y'
AND exists(SELECT * FROM orders zpp WHERE zpp.parent_order_id = o.order_id
AND zpp.ot_code = 'ZPPAY'
AND zpp.os_code = 'ZPPAY_CRTD'))
THEN 'Y' ELSE 'N'
END AS ONLINE_RESEND_ACCESS,
CASE WHEN ( o.ot_code IN ('WRQST', 'PWRQT') AND nvl(opv9.opvalue,'N') = 'Y'
AND exists(SELECT * FROM orders zpp WHERE zpp.parent_order_id = o.order_id
AND zpp.ot_code = 'ZPPAY'
AND zpp.os_code = 'ZPPAY_CRTD'))
THEN 'Y' ELSE 'N'
END AS ONLINE_ZPPAY_CRTD,
CASE
WHEN 0 = (select count(*) from op_values ov where ov.op_code = 'VITRINE' and ov.opvalue = 'Y' and ov.order_id = o.order_id)
THEN 'N'
ELSE 'Y'
END AS VITRINE
FROM orders o
JOIN order_status os
ON o.os_code = os.os_code
LEFT OUTER JOIN op_values opv7
ON opv7.order_id = o.order_id
AND opv7.op_code = 'ORDER_HY'
LEFT OUTER JOIN op_values opv8
ON opv8.order_id = o.order_id
AND opv8.op_code = 'CORPORATE'
LEFT OUTER JOIN op_values opv9
ON opv9.order_id = o.order_id
AND opv9.op_code = 'ONLINE_PAY'
LEFT JOIN orders ord
ON o.order_id = ord.parent_order_id
AND ord.ot_code = 'ZORDR'
LEFT JOIN op_values opv
ON ord.order_id = opv.order_id
AND opv.op_code = 'SAPORDNUM'
left join orders ord2 on get_op_values(ord2.order_id, null, 'SAPORDNUM') = opv.opvalue
and ord2.chainnum <> o.chainnum
and ord2.ot_code = 'ZDLVGI'
and not (ord2.os_code in ('DOC_STORN','ZDLVGI_DEL'))
left join op_values opv4
on ((o.outerid = opv4.opvalue) or ('39|'||o.outerid = opv4.opvalue) or ('40|'||o.outerid = opv4.opvalue))
and opv4.op_code = 'WEBORDNUM'
and (( opv4.order_id is null and not exists (select 1 from orders os where opv4.order_id = os.order_id and os.ot_code = 'ZDLVGR')
or( opv4.order_id is not null and exists (select 1 from orders os where opv4.order_id = os.order_id and os.ot_code = 'ZDLVGR'))))
left JOIN orders ord3
ON opv4.order_id = ord3.order_id
AND ord3.ot_code = 'ZDLVGR'
LEFT JOIN op_values opv5
ON ord3.order_id = opv5.order_id
AND opv5.op_code = 'SAPORDNUM'
left join op_values b_stop on b_stop.order_id = o.order_id
and b_stop.op_code = 'BITRIX_STOP'
WHERE o.ot_code = 'WRQST' and exists
(select null from op_values WRQST_DOC_VERS_opv3
where o.order_id = WRQST_DOC_VERS_opv3.order_id
AND WRQST_DOC_VERS_opv3.op_code = 'DOC_VERS'
AND WRQST_DOC_VERS_opv3.opvalue IN ('2','3','4'))
ORDER BY o.order_id
Падение производительности происходит на вызове функции: get_op_values
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
FUNCTION get_op_values (
vorder_id in varchar2,
vog_item_id in varchar2 default null,
vop_code in varchar2 default 'DSCNT_NMBR')
return varchar2
is
-- Purpose: Returns opvalue from op_values
--
-- MODIFICATION HISTORY
-- Person Date Comments
-- --------- ---------- -------------------------------------------
-- Zakharov 23.01.2004 First edition
ret_value op_values.opvalue%type;
begin
if vog_item_id is null then
select MAX(opvalue)
into ret_value
from op_values
where order_id = vorder_id and op_code = vop_code;
else
select MAX(opvalue)
into ret_value
from op_values
where order_id = vorder_id
and og_item_id = vog_item_id
and op_code = vop_code;
end if;
return ret_value;
exception
when no_data_found then
return '';
end;
По сути ее можно заменить просто вызовом
1.
2.
3.
4.
select MAX(opvalue)
into ret_value
from op_values
where order_id = vorder_id and op_code = vop_code;
Но даже в этом случае лучше не становиться.
План всего запроса:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11522 | 8416K| | 43966 (1)| 00:08:48 |
|* 1 | TABLE ACCESS BY INDEX ROWID | O_STATUS_LOG | 1 | 23 | | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 63 | | 9 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 40 | | 6 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 17 | | 3 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | SYS_C0010173 | 1 | | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | O_STATUS_LOG | 1 | 23 | | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | INU_O_STATUS_LOG_CODE_ORD_DATE | 1 | | | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | INU_O_STATUS_LOG_CODE_ORD_DATE | 1 | | | 2 (0)| 00:00:01 |
| 9 | SORT UNIQUE NOSORT | | 1 | 12 | | 5 (20)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | O_GOOD_ITEM | 2 | 24 | | 4 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IF111O_GOOD_ITEM | 2 | | | 3 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 45 | | 3 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 31 | | 3 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | STAFF | 1 | 15 | | 2 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | UI1STAFF | 1 | | | 1 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | TSYS_USRPROF_LINK_PK | 1 | 16 | | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | SYS_C0012646 | 1 | 14 | | 0 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 45 | | 3 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 31 | | 3 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | STAFF | 1 | 15 | | 2 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | UI1STAFF | 1 | | | 1 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | TSYS_USRPROF_LINK_PK | 1 | 16 | | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | SYS_C0012646 | 1 | 14 | | 0 (0)| 00:00:01 |
| 24 | SORT AGGREGATE | | 1 | 6 | | | |
|* 25 | INDEX RANGE SCAN | IF111O_GOOD_ITEM | 2 | 12 | | 3 (0)| 00:00:01 |
| 26 | SORT AGGREGATE | | 1 | 9 | | | |
| 27 | TABLE ACCESS BY INDEX ROWID | O_GOOD_ITEM | 2 | 18 | | 4 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | IF111O_GOOD_ITEM | 2 | | | 3 (0)| 00:00:01 |
| 29 | SORT AGGREGATE | | 1 | 12 | | | |
|* 30 | TABLE ACCESS BY INDEX ROWID | O_GOOD_ITEM | 2 | 24 | | 4 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | IF111O_GOOD_ITEM | 2 | | | 3 (0)| 00:00:01 |
| 32 | SORT AGGREGATE | | 1 | 24 | | | |
| 33 | TABLE ACCESS BY INDEX ROWID | OP_VALUES | 1 | 24 | | 4 (0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | INU_OP_VALUES | 1 | | | 3 (0)| 00:00:01 |
|* 35 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 21 | | 4 (0)| 00:00:01 |
|* 36 | INDEX RANGE SCAN | IF138ORDERS | 1 | | | 3 (0)| 00:00:01 |
|* 37 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 21 | | 4 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN | IF138ORDERS | 1 | | | 3 (0)| 00:00:01 |
|* 39 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 21 | | 4 (0)| 00:00:01 |
|* 40 | INDEX RANGE SCAN | IF138ORDERS | 1 | | | 3 (0)| 00:00:01 |
|* 41 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 21 | | 4 (0)| 00:00:01 |
|* 42 | INDEX RANGE SCAN | IF138ORDERS | 1 | | | 3 (0)| 00:00:01 |
|* 43 | TABLE ACCESS BY INDEX ROWID | OP_VALUES | 1 | 24 | | 4 (0)| 00:00:01 |
|* 44 | INDEX RANGE SCAN | INU_OP_VALUES | 1 | | | 3 (0)| 00:00:01 |
| 45 | SORT ORDER BY | | 11522 | 8416K| 9232K| 43966 (1)| 00:08:48 |
|* 46 | HASH JOIN RIGHT OUTER | | 11522 | 8416K| 5264K| 42149 (1)| 00:08:26 |
|* 47 | TABLE ACCESS FULL | OP_VALUES | 149K| 3506K| | 15564 (2)| 00:03:07 |
|* 48 | HASH JOIN RIGHT OUTER | | 11274 | 7971K| | 25935 (1)| 00:05:12 |
| 49 | TABLE ACCESS BY INDEX ROWID | ORDERS | 20403 | 259K| | 1566 (1)| 00:00:19 |
|* 50 | INDEX RANGE SCAN | IOT_CODE_ORDERS | 20403 | | | 51 (0)| 00:00:01 |
| 51 | NESTED LOOPS OUTER | | 11274 | 7827K| | 24368 (1)| 00:04:53 |
|* 52 | HASH JOIN OUTER | | 3758 | 2587K| | 23542 (1)| 00:04:43 |
| 53 | NESTED LOOPS OUTER | | 209 | 137K| | 20876 (1)| 00:04:11 |
| 54 | NESTED LOOPS SEMI | | 209 | 132K| | 20319 (1)| 00:04:04 |
| 55 | VIEW | | 843 | 516K| | 18074 (1)| 00:03:37 |
| 56 | NESTED LOOPS OUTER | | 843 | 183K| | 18074 (1)| 00:03:37 |
| 57 | NESTED LOOPS OUTER | | 843 | 163K| | 15828 (1)| 00:03:10 |
| 58 | NESTED LOOPS OUTER | | 843 | 141K| | 13886 (1)| 00:02:47 |
| 59 | NESTED LOOPS OUTER | | 843 | 121K| | 11641 (1)| 00:02:20 |
| 60 | NESTED LOOPS OUTER | | 843 | 102K| | 9395 (1)| 00:01:53 |
|* 61 | HASH JOIN | | 843 | 84300 | | 7149 (1)| 00:01:26 |
| 62 | TABLE ACCESS FULL | ORDER_STATUS | 666 | 24642 | | 3 (0)| 00:00:01 |
|* 63 | TABLE ACCESS BY INDEX ROWID| ORDERS | 843 | 53109 | | 7146 (1)| 00:01:26 |
|* 64 | INDEX RANGE SCAN | IOT_CODE_ORDERS | 93192 | | | 225 (1)| 00:00:03 |
| 65 | TABLE ACCESS BY INDEX ROWID | OP_VALUES | 1 | 24 | | 3 (0)| 00:00:01 |
|* 66 | INDEX RANGE SCAN | INU_OP_VALUES | 1 | | | 2 (0)| 00:00:01 |
| 67 | TABLE ACCESS BY INDEX ROWID | OP_VALUES | 1 | 24 | | 3 (0)| 00:00:01 |
|* 68 | INDEX RANGE SCAN | INU_OP_VALUES | 1 | | | 2 (0)| 00:00:01 |
| 69 | TABLE ACCESS BY INDEX ROWID | OP_VALUES | 1 | 24 | | 3 (0)| 00:00:01 |
|* 70 | INDEX RANGE SCAN | INU_OP_VALUES | 1 | | | 2 (0)| 00:00:01 |
|* 71 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 27 | | 3 (0)| 00:00:01 |
|* 72 | INDEX RANGE SCAN | IF138ORDERS | 1 | | | 2 (0)| 00:00:01 |
| 73 | TABLE ACCESS BY INDEX ROWID | OP_VALUES | 1 | 24 | | 3 (0)| 00:00:01 |
|* 74 | INDEX RANGE SCAN | INU_OP_VALUES | 1 | | | 2 (0)| 00:00:01 |
|* 75 | TABLE ACCESS BY INDEX ROWID | OP_VALUES | 52 | 1248 | | 3 (0)| 00:00:01 |
|* 76 | INDEX RANGE SCAN | INU_OP_VALUES | 1 | | | 2 (0)| 00:00:01 |
| 77 | TABLE ACCESS BY INDEX ROWID | OP_VALUES | 1 | 24 | | 3 (0)| 00:00:01 |
|* 78 | INDEX RANGE SCAN | INU_OP_VALUES | 1 | | | 2 (0)| 00:00:01 |
|* 79 | TABLE ACCESS BY INDEX ROWID | ORDERS | 33625 | 985K| | 2665 (1)| 00:00:32 |
|* 80 | INDEX RANGE SCAN | IOT_CODE_ORDERS | 34740 | | | 85 (0)| 00:00:02 |
| 81 | VIEW | | 3 | 18 | | 0 (0)| 00:00:01 |
| 82 | CONCATENATION | | | | | | |
|* 83 | FILTER | | | | | | |
|* 84 | TABLE ACCESS BY INDEX ROWID | OP_VALUES | 14 | 336 | | 3134 (1)| 00:00:38 |
|* 85 | INDEX RANGE SCAN | IDX_OPVALUE | 8881 | | | 27 (0)| 00:00:01 |
|* 86 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 13 | | 3 (0)| 00:00:01 |
|* 87 | INDEX UNIQUE SCAN | SYS_C0010173 | 1 | | | 2 (0)| 00:00:01 |
|* 88 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 13 | | 3 (0)| 00:00:01 |
|* 89 | INDEX UNIQUE SCAN | SYS_C0010173 | 1 | | | 2 (0)| 00:00:01 |
|* 90 | FILTER | | | | | | |
|* 91 | TABLE ACCESS BY INDEX ROWID | OP_VALUES | 14 | 336 | | 3134 (1)| 00:00:38 |
|* 92 | INDEX RANGE SCAN | IDX_OPVALUE | 8881 | | | 27 (0)| 00:00:01 |
|* 93 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 13 | | 3 (0)| 00:00:01 |
|* 94 | INDEX UNIQUE SCAN | SYS_C0010173 | 1 | | | 2 (0)| 00:00:01 |
|* 95 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 13 | | 3 (0)| 00:00:01 |
|* 96 | INDEX UNIQUE SCAN | SYS_C0010173 | 1 | | | 2 (0)| 00:00:01 |
|* 97 | FILTER | | | | | | |
|* 98 | TABLE ACCESS BY INDEX ROWID | OP_VALUES | 13 | 312 | | 42 (0)| 00:00:01 |
|* 99 | INDEX RANGE SCAN | IDX_OPVALUE | 56 | | | 22 (0)| 00:00:01 |
|*100 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 13 | | 3 (0)| 00:00:01 |
|*101 | INDEX UNIQUE SCAN | SYS_C0010173 | 1 | | | 2 (0)| 00:00:01 |
|*102 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 13 | | 3 (0)| 00:00:01 |
|*103 | INDEX UNIQUE SCAN | SYS_C0010173 | 1 | | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OSL2"."OSL_ID"<"OSL1"."OSL_ID")
4 - filter("O"."OS_CODE"='WRQST_CRTD')
5 - access("O"."ORDER_ID"=:B1)
7 - access("OSL2"."OS_CODE"='WRQST_WAIT' AND "OSL2"."ORDER_ID"=:B1)
8 - access("OSL1"."OS_CODE"='WRQST_CRTD' AND "OSL1"."ORDER_ID"="O"."ORDER_ID")
filter("OSL1"."ORDER_ID"=:B1 AND "OSL1"."ORDER_ID"="OSL2"."ORDER_ID")
10 - filter("OG"."QUANTITY">0 AND NVL("OG"."ST_CODE",'N')<>'TRASH')
11 - access("OG"."ORDER_ID"=:B1)
15 - access("T"."USER_CODE"="PK_MASTERDATA"."GET_USERCODE"())
16 - access("PLK"."STAFF_ID"="T"."STAFF_ID")
filter("PLK"."PROF_CODE"<>'SELLER')
17 - access("PLK"."PROF_CODE"="PRF"."PROF_CODE")
filter("PRF"."PROF_CODE"<>'SELLER')
21 - access("T"."USER_CODE"="PK_MASTERDATA"."GET_USERCODE"())
22 - access("PLK"."STAFF_ID"="T"."STAFF_ID")
filter("PLK"."PROF_CODE"<>'SELLER' AND "PLK"."PROF_CODE"<>'STOCKMAN')
23 - access("PLK"."PROF_CODE"="PRF"."PROF_CODE")
filter("PRF"."PROF_CODE"<>'SELLER' AND "PRF"."PROF_CODE"<>'STOCKMAN')
25 - access("OG"."ORDER_ID"=:B1)
28 - access("OG"."ORDER_ID"=:B1)
30 - filter("IGNORE"<>'Y')
31 - access("ORDER_ID"=:B1)
34 - access("OPVW"."ORDER_ID"=:B1 AND "OPVW"."OP_CODE"='IM_WERK')
35 - filter("ZPP"."OT_CODE"='ZPPAY' AND "ZPP"."OS_CODE"='ZPPAY_CRTD')
36 - access("ZPP"."PARENT_ORDER_ID"=:B1)
37 - filter("ZPP"."OT_CODE"='ZPPAY' AND "ZPP"."OS_CODE"='ZPPAY_CRTD')
38 - access("ZPP"."PARENT_ORDER_ID"=:B1)
39 - filter("ZPP"."OT_CODE"='ZPPAY' AND "ZPP"."OS_CODE"='ZPPAY_CRTD')
40 - access("ZPP"."PARENT_ORDER_ID"=:B1)
41 - filter("ZPP"."OT_CODE"='ZPPAY' AND "ZPP"."OS_CODE"='ZPPAY_CRTD')
42 - access("ZPP"."PARENT_ORDER_ID"=:B1)
43 - filter("OV"."OPVALUE"='Y')
44 - access("OV"."ORDER_ID"=:B1 AND "OV"."OP_CODE"='VITRINE')
46 - access("ORD3"."ORDER_ID"="OPV5"."ORDER_ID"(+))
47 - filter("OPV5"."OP_CODE"(+)='SAPORDNUM')
48 - access("OPV4"."ORDER_ID"="ORD3"."ORDER_ID"(+))
50 - access("ORD3"."OT_CODE"(+)='ZDLVGR')
52 - access("OPV"."OPVALUE"="GET_OP_VALUES"(TO_CHAR("ORD2"."ORDER_ID"(+)),NULL,'SAPORDNUM'))
filter("ORD2"."CHAINNUM"(+)<>"O"."CHAINNUM")
61 - access("O"."OS_CODE"="OS"."OS_CODE")
63 - filter("O"."DOCDATE">=TO_DATE(' 2016-07-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "O"."DOCDATE"<TO_DATE('
2016-10-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
64 - access("O"."OT_CODE"='WRQST')
66 - access("OPV7"."ORDER_ID"(+)="O"."ORDER_ID" AND "OPV7"."OP_CODE"(+)='ORDER_HY')
68 - access("OPV8"."ORDER_ID"(+)="O"."ORDER_ID" AND "OPV8"."OP_CODE"(+)='CORPORATE')
70 - access("OPV9"."ORDER_ID"(+)="O"."ORDER_ID" AND "OPV9"."OP_CODE"(+)='ONLINE_PAY')
71 - filter("ORD"."OT_CODE"(+)='ZORDR')
72 - access("O"."ORDER_ID"="ORD"."PARENT_ORDER_ID"(+))
filter("ORD"."PARENT_ORDER_ID"(+) IS NOT NULL)
74 - access("ORD"."ORDER_ID"="OPV"."ORDER_ID"(+) AND "OPV"."OP_CODE"(+)='SAPORDNUM')
75 - filter("WRQST_DOC_VERS_OPV3"."OPVALUE"='2' OR "WRQST_DOC_VERS_OPV3"."OPVALUE"='3' OR
"WRQST_DOC_VERS_OPV3"."OPVALUE"='4')
76 - access("from$_subquery$_032"."QCSJ_C000000002400000"="WRQST_DOC_VERS_OPV3"."ORDER_ID" AND
"WRQST_DOC_VERS_OPV3"."OP_CODE"='DOC_VERS')
78 - access("B_STOP"."ORDER_ID"(+)="from$_subquery$_032"."QCSJ_C000000002400000" AND "B_STOP"."OP_CODE"(+)='BITRIX_STOP')
79 - filter("ORD2"."OS_CODE"(+)<>'DOC_STORN' AND "ORD2"."OS_CODE"(+)<>'ZDLVGI_DEL')
80 - access("ORD2"."OT_CODE"(+)='ZDLVGI')
83 - filter("OPV4"."ORDER_ID" IS NOT NULL AND EXISTS (SELECT 0 FROM "ORDERS" "OS" WHERE "OS"."ORDER_ID"=:B1 AND
"OS"."OT_CODE"='ZDLVGR') OR "OPV4"."ORDER_ID" IS NULL AND NOT EXISTS (SELECT 0 FROM "ORDERS" "OS" WHERE
"OS"."ORDER_ID"=:B2 AND "OS"."OT_CODE"='ZDLVGR'))
84 - filter("OPV4"."OP_CODE"='WEBORDNUM')
85 - access("OPV4"."OPVALUE"='40|'||"from$_subquery$_032"."QCSJ_C000000003000024")
86 - filter("OS"."OT_CODE"='ZDLVGR')
87 - access("OS"."ORDER_ID"=:B1)
88 - filter("OS"."OT_CODE"='ZDLVGR')
89 - access("OS"."ORDER_ID"=:B1)
90 - filter( EXISTS (SELECT 0 FROM "ORDERS" "OS" WHERE "OS"."ORDER_ID"=:B1 AND "OS"."OT_CODE"='ZDLVGR') AND
"OPV4"."ORDER_ID" IS NOT NULL OR NOT EXISTS (SELECT 0 FROM "ORDERS" "OS" WHERE "OS"."ORDER_ID"=:B2 AND
"OS"."OT_CODE"='ZDLVGR') AND "OPV4"."ORDER_ID" IS NULL)
91 - filter("OPV4"."OP_CODE"='WEBORDNUM')
92 - access("OPV4"."OPVALUE"='39|'||"from$_subquery$_032"."QCSJ_C000000003000024")
filter(LNNVL("OPV4"."OPVALUE"='40|'||"from$_subquery$_032"."QCSJ_C000000003000024"))
93 - filter("OS"."OT_CODE"='ZDLVGR')
94 - access("OS"."ORDER_ID"=:B1)
95 - filter("OS"."OT_CODE"='ZDLVGR')
96 - access("OS"."ORDER_ID"=:B1)
97 - filter( EXISTS (SELECT 0 FROM "ORDERS" "OS" WHERE "OS"."ORDER_ID"=:B1 AND "OS"."OT_CODE"='ZDLVGR') AND
"OPV4"."ORDER_ID" IS NOT NULL OR NOT EXISTS (SELECT 0 FROM "ORDERS" "OS" WHERE "OS"."ORDER_ID"=:B2 AND
"OS"."OT_CODE"='ZDLVGR') AND "OPV4"."ORDER_ID" IS NULL)
98 - filter("OPV4"."OP_CODE"='WEBORDNUM')
99 - access("from$_subquery$_032"."QCSJ_C000000003000024"="OPV4"."OPVALUE")
filter("OPV4"."OPVALUE" IS NOT NULL AND LNNVL("OPV4"."OPVALUE"='39|'||"from$_subquery$_032"."QCSJ_C000000003000024")
AND LNNVL("OPV4"."OPVALUE"='40|'||"from$_subquery$_032"."QCSJ_C000000003000024"))
100 - filter("OS"."OT_CODE"='ZDLVGR')
101 - access("OS"."ORDER_ID"=:B1)
102 - filter("OS"."OT_CODE"='ZDLVGR')
103 - access("OS"."ORDER_ID"=:B1)
