MaximaXXL
Получилась, конечно, красота. Круто!
Но время выполнения 68 сек
1.
запрос
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.
select mag, sum(ostss) ostss
from (
select
f.salelocationto mag,
decode(f.saletype, 'WO', -1, 'CS', -1 , 1)*f.primecost ostss
from supermag.ffmaprep f
where f.rectype=1
and f.saledate<=to_date('30.11.2017','DD.MM.YYYY')
and f.saletype in ('WO','WI','CS','CR')
and f.salelocationfrom = -2
and f.salelocationto in (120)
union all
select
f.salelocationfrom mag,
decode(f.saletype, 'WO', -1, 'CS', -1 , 1)*f.primecost ostss
from supermag.ffmaprep f
where f.rectype=1
and f.saledate<=to_date('30.11.2017','DD.MM.YYYY')
and f.saletype in ('WO','WI','CS','CR')
and f.salelocationfrom in (120)
) a1
group by a1.mag
план:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
PLAN
--------------------------------------------------------------------------------
SELECT STATEMENT
HASH GROUP BY
VIEW
UNION-ALL
TABLE ACCESS BY INDEX ROWID FFMAPREP
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP CONVERSION FROM ROWIDS
SORT ORDER BY
INDEX RANGE SCAN FFMAPREP_LOCFROM
BITMAP CONVERSION FROM ROWIDS
SORT ORDER BY
INDEX RANGE SCAN FFMAPREP_LOCTO
TABLE ACCESS BY INDEX ROWID FFMAPREP
INDEX RANGE SCAN FFMAPREP_LOCFROM
тогда как
запрос
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.
select
a1.mag,
sum(a1.prih_do)+sum(a1.vozv_do)-sum(a1.rash_do)-sum(a1.prod_do)-sum(a1.rash_vper)+sum(a1.prih_vper)-sum(a1.prod_vper)+sum(a1.vozv_vper) ostss
from
(
select
f.salelocationfrom mag,
case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') then
sum(decode(f.saletype,'WO',f.primecost,0)) else 0 end rash_do,
case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') then
sum(decode(f.saletype,'WI',f.primecost,0)) else 0 end prih_do,
case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') then
sum(decode(f.saletype,'CS',f.primecost,0)) else 0 end prod_do,
case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') then
sum(decode(f.saletype,'CR',f.primecost,0)) else 0 end vozv_do,
case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY') then
sum(decode(f.saletype,'WO',f.primecost,0)) else 0 end rash_vper,
case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY') then
sum(decode(f.saletype,'WI',f.primecost,0)) else 0 end prih_vper,
case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY') then
sum(decode(f.saletype,'CS',f.primecost,0)) else 0 end prod_vper,
case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY')
then sum(decode(f.saletype,'CR',f.primecost,0)) else 0 end vozv_vper,
case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY') then
sum(decode(f.saletype,'CS',f.salesum,0)) else 0 end prod_vper3,
case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY')
then sum(decode(f.saletype,'CR',f.salesum,0)) else 0 end vozv_vper3
from supermag.ffmaprep f
where f.rectype=1
and f.saledate<=to_date('30.11.2017','DD.MM.YYYY')
and f.salelocationfrom in (120)
group by f.salelocationfrom, f.saledate
union all
select
f.salelocationto mag,
case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') then
sum(decode(f.saletype,'WO',f.primecost,0)) else 0 end rash_do,
case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') then
sum(decode(f.saletype,'WI',f.primecost,0)) else 0 end prih_do,
case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') then
sum(decode(f.saletype,'CS',f.primecost,0)) else 0 end prod_do,
case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') then
sum(decode(f.saletype,'CR',f.primecost,0)) else 0 end vozv_do,
case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY') then
sum(decode(f.saletype,'WO',f.primecost,0)) else 0 end rash_vper,
case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY') then
sum(decode(f.saletype,'WI',f.primecost,0)) else 0 end prih_vper,
case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY') then
sum(decode(f.saletype,'CS',f.primecost,0)) else 0 end prod_vper,
case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY')
then sum(decode(f.saletype,'CR',f.primecost,0)) else 0 end vozv_vper,
case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY') then
sum(decode(f.saletype,'CS',f.salesum,0)) else 0 end prod_vper3,
case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY')
then sum(decode(f.saletype,'CR',f.salesum,0)) else 0 end vozv_vper3
from supermag.ffmaprep f
where f.rectype=1
and f.saledate<=to_date('30.11.2017','DD.MM.YYYY')
and f.salelocationto in (120)
group by f.salelocationto, f.saledate
) a1
group by a1.mag
выполняется за 13 сек
план:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
PLAN
--------------------------------------------------------------------------------
SELECT STATEMENT
HASH GROUP BY
VIEW
UNION-ALL
SORT GROUP BY NOSORT
TABLE ACCESS BY INDEX ROWID FFMAPREP
INDEX RANGE SCAN FFMAPREP_LOCFROM
SORT GROUP BY NOSORT
TABLE ACCESS BY INDEX ROWID FFMAPREP
INDEX RANGE SCAN FFMAPREP_LOCTO
|
