У объекта есть ряд показателей (20+) каждый из которых выбираться подзапросом t1,t2,t3...
Значения показателей изменяются во времени, но четкой корреляции между датами появления показателей разного типа нет,
т.е. первый показатель может быть создан 01.01.2017 и не менялся по текущую дату, а второй показатель появился 02.02.2017
и периодически появляются новые значения, а время жизни третьего показателя всего месяц
с 03.03.2017 по 04.04.2017.
Необходимо представить некую витрину данных из значений этих показателей в табличном виде, в которой будет отражена история
изменений всех показателей по объекту
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
OBJ_ID ST ET VAL1 VAL2 VAL3 VAL4
1 17.06.2017 16:00:00 1 10 100 4000
1 17.06.2017 15:00:00 17.06.2017 16:00:00 1 10 100 3999
1 17.06.2017 14:00:00 17.06.2017 15:00:00 1 10 100 3998
1 17.06.2017 13:00:00 17.06.2017 14:00:00 1 10 100 3997
1 17.06.2017 12:00:00 17.06.2017 13:00:00 1 10 100 3996
1 17.06.2017 11:00:00 17.06.2017 12:00:00 1 10 100 3995
1 17.06.2017 10:00:00 17.06.2017 11:00:00 1 10 100 3994
1 17.06.2017 9:00:00 17.06.2017 10:00:00 1 10 100 3993
1 17.06.2017 8:00:00 17.06.2017 9:00:00 1 10 100 3992
1 17.06.2017 7:00:00 17.06.2017 8:00:00 1 10 100 3991
1. Материализовал выборку каждого показателя
2. Собрал все возможные даты появлений и окончания действия любого показателя
3. Вычислил значение каждого показателя для каждой даты
На этапе соединения возникают просадки производительности даже для относительно небольшого набора данных (выделено красным).
Другие типы соединений показали себя ещё менее производительными.
Требуется помощь зала в поиске жизнеспособного решения задачи.
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.
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
SQL> with t1 as (select /*+ materialize cardinality(t,1) показатель 1 */ level as val1,trunc(sysdate,'y')+level as st1,lead(trunc(sysdate,'y')+level) over (order by 1)as et1 from dual t connect by level <= 1)
2 , t2 as (select /*+ materialize cardinality(t,10) показатель 2 */ level as val2,trunc(sysdate,'y')+level as st2,lead(trunc(sysdate,'y')+level) over (order by 1) as et2 from dual t connect by level <= 10)
3 , t3 as (select /*+ materialize cardinality(t,100) показатель 3 */ level as val3,trunc(sysdate,'y')+level as st3,lead(trunc(sysdate,'y')+level) over (order by 1) as et3 from dual t connect by level <= 100)
4 , t4 as (select /*+ materialize cardinality(t,4000) показатель 4 */ level as val4,trunc(sysdate,'y')+1+level/24 as st4,lead(trunc(sysdate,'y')+1+level/24) over (order by 1) as et4 from dual t connect by level
5 , t19 as (
6 select /*+ materialize */ 1 as obj_id, st, lead(st) over (order by st) as et
7 from (select st1 as st from t1 union
8 select st2 from t2 union
9 select st3 from t3 union
10 select st4 from t4 union
11 select et1 from t1 union
12 select et2 from t2 union
13 select et3 from t3 union
14 select et4 from t4)
15 where st is not null
16 )
17 , t20 as (
18 select * from t1,t2,t3,t4,t19
19 where t19.st >= t1.st1(+)
20 and t19.st < nvl(t1.et1(+),sysdate)
21 and t19.st >= t2.st2(+)
22 and t19.st < nvl(t2.et2(+),sysdate)
23 and t19.st >= t3.st3(+)
24 and t19.st < nvl(t3.et3(+),sysdate)
25 and t19.st >= t4.st4(+)
26 and t19.st < nvl(t4.et4(+),sysdate)
27 order by 1 desc
28 )
29 select /*+ gather_plan_statistics */
30 obj_id,st,et,val1,val2,val3,val4
31 from t20
32 where rownum <=10
33 /
1 17.06.2017 16:00:00 1 10 100 4000
1 17.06.2017 15:00:00 17.06.2017 16:00:00 1 10 100 3999
1 17.06.2017 14:00:00 17.06.2017 15:00:00 1 10 100 3998
1 17.06.2017 13:00:00 17.06.2017 14:00:00 1 10 100 3997
1 17.06.2017 12:00:00 17.06.2017 13:00:00 1 10 100 3996
1 17.06.2017 11:00:00 17.06.2017 12:00:00 1 10 100 3995
1 17.06.2017 10:00:00 17.06.2017 11:00:00 1 10 100 3994
1 17.06.2017 09:00:00 17.06.2017 10:00:00 1 10 100 3993
1 17.06.2017 08:00:00 17.06.2017 09:00:00 1 10 100 3992
1 17.06.2017 07:00:00 17.06.2017 08:00:00 1 10 100 3991
10 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(format => 'ALLSTATS LAST'))
2 /
SQL_ID 2b0caj2x6rqt1, child number 0
-------------------------------------
with t1 as (select /*+ materialize cardinality(t,1) показатель 1 */
...
Plan hash value: 3132191154
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:04.84 | 154 | 31 | 31 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | 10 |00:00:04.84 | 154 | 31 | 31 | | | |
| 2 | LOAD AS SELECT | | 1 | | 0 |00:00:00.01 | 4 | 0 | 1 | 269K| 269K| 269K (0)|
| 3 | WINDOW BUFFER | | 1 | 1 | 1 |00:00:00.01 | 0 | 0 | 0 | 2048 | 2048 | 2048 (0)|
| 4 | CONNECT BY WITHOUT FILTERING| | 1 | | 1 |00:00:00.01 | 0 | 0 | 0 | | | |
| 5 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | 0 | 0 | | | |
| 6 | LOAD AS SELECT | | 1 | | 0 |00:00:00.01 | 4 | 0 | 1 | 269K| 269K| 269K (0)|
| 7 | WINDOW BUFFER | | 1 | 10 | 10 |00:00:00.01 | 0 | 0 | 0 | 2048 | 2048 | 2048 (0)|
| 8 | CONNECT BY WITHOUT FILTERING| | 1 | | 10 |00:00:00.01 | 0 | 0 | 0 | | | |
| 9 | FAST DUAL | | 1 | 10 | 1 |00:00:00.01 | 0 | 0 | 0 | | | |
| 10 | LOAD AS SELECT | | 1 | | 0 |00:00:00.01 | 4 | 0 | 1 | 269K| 269K| 269K (0)|
| 11 | WINDOW BUFFER | | 1 | 100 | 100 |00:00:00.01 | 0 | 0 | 0 | 4096 | 4096 | 4096 (0)|
| 12 | CONNECT BY WITHOUT FILTERING| | 1 | | 100 |00:00:00.01 | 0 | 0 | 0 | | | |
| 13 | FAST DUAL | | 1 | 100 | 1 |00:00:00.01 | 0 | 0 | 0 | | | |
| 14 | LOAD AS SELECT | | 1 | | 0 |00:00:00.02 | 17 | 0 | 14 | 269K| 269K| 269K (0)|
| 15 | WINDOW BUFFER | | 1 | 4000 | 4000 |00:00:00.01 | 0 | 0 | 0 | 142K| 142K| 126K (0)|
| 16 | CONNECT BY WITHOUT FILTERING| | 1 | | 4000 |00:00:00.01 | 0 | 0 | 0 | | | |
| 17 | FAST DUAL | | 1 | 4000 | 1 |00:00:00.01 | 0 | 0 | 0 | | | |
| 18 | LOAD AS SELECT | | 1 | | 0 |00:00:00.03 | 71 | 17 | 14 | 269K| 269K| 269K (0)|
| 19 | WINDOW SORT | | 1 | 8222 | 4001 |00:00:00.02 | 54 | 17 | 0 | 124K| 124K| 110K (0)|
| 20 | VIEW | | 1 | 8222 | 4001 |00:00:00.02 | 54 | 17 | 0 | | | |
| 21 | SORT UNIQUE | | 1 | 8222 | 4001 |00:00:00.02 | 54 | 17 | 0 | 267K| 267K| 237K (0)|
| 22 | UNION-ALL | | 1 | | 8218 |00:00:00.01 | 54 | 17 | 0 | | | |
|* 23 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 5 | 1 | 0 | | | |
| 24 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF03F_C65F3617 | 1 | 1 | 1 |00:00:00.01 | 5 | 1 | 0 | | | |
|* 25 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 5 | 1 | 0 | | | |
| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF040_C65F3617 | 1 | 10 | 10 |00:00:00.01 | 5 | 1 | 0 | | | |
|* 27 | VIEW | | 1 | 100 | 100 |00:00:00.01 | 5 | 1 | 0 | | | |
| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF041_C65F3617 | 1 | 100 | 100 |00:00:00.01 | 5 | 1 | 0 | | | |
|* 29 | VIEW | | 1 | 4000 | 4000 |00:00:00.01 | 18 | 14 | 0 | | | |
| 30 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF042_C65F3617 | 1 | 4000 | 4000 |00:00:00.01 | 18 | 14 | 0 | | | |
|* 31 | VIEW | | 1 | 1 | 0 |00:00:00.01 | 2 | 0 | 0 | | | |
| 32 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF03F_C65F3617 | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | 0 | | | |
|* 33 | VIEW | | 1 | 10 | 9 |00:00:00.01 | 2 | 0 | 0 | | | |
| 34 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF040_C65F3617 | 1 | 10 | 10 |00:00:00.01 | 2 | 0 | 0 | | | |
|* 35 | VIEW | | 1 | 100 | 99 |00:00:00.01 | 2 | 0 | 0 | | | |
| 36 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF041_C65F3617 | 1 | 100 | 100 |00:00:00.01 | 2 | 0 | 0 | | | |
|* 37 | VIEW | | 1 | 4000 | 3999 |00:00:00.01 | 15 | 0 | 0 | | | |
| 38 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF042_C65F3617 | 1 | 4000 | 4000 |00:00:00.01 | 15 | 0 | 0 | | | |
|* 39 | COUNT STOPKEY | | 1 | | 10 |00:00:04.78 | 39 | 14 | 0 | | | |
| 40 | VIEW | | 1 | 82220 | 10 |00:00:04.78 | 39 | 14 | 0 | | | |
|* 41 | SORT ORDER BY STOPKEY | | 1 | 82220 | 10 |00:00:04.78 | 39 | 14 | 0 | 2048 | 2048 | 2048 (0)|
| 42 | MERGE JOIN OUTER | | 1 | 82220 | 4001 |00:00:04.78 | 39 | 14 | 0 | | | |
| 43 | MERGE JOIN OUTER | | 1 | 8222 | 4001 |00:00:00.23 | 24 | 14 | 0 | | | |
| 44 | MERGE JOIN OUTER | | 1 | 8222 | 4001 |00:00:00.06 | 22 | 14 | 0 | | | |
| 45 | MERGE JOIN OUTER | | 1 | 8222 | 4001 |00:00:00.02 | 20 | 14 | 0 | | | |
| 46 | SORT JOIN | | 1 | 8222 | 4001 |00:00:00.01 | 18 | 14 | 0 | 232K| 232K| 206K (0)|
| 47 | VIEW | | 1 | 8222 | 4001 |00:00:00.01 | 18 | 14 | 0 | | | |
| 48 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF043_C65F3617 | 1 | 8222 | 4001 |00:00:00.01 | 18 | 14 | 0 | | | |
|* 49 | FILTER | | 4001 | | 4001 |00:00:00.01 | 2 | 0 | 0 | | | |
|* 50 | SORT JOIN | | 4001 | 1 | 4001 |00:00:00.01 | 2 | 0 | 0 | 2048 | 2048 | 2048 (0)|
| 51 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | 0 | | | |
| 52 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF03F_C65F3617 | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | 0 | | | |
|* 53 | FILTER | | 4001 | | 4001 |00:00:00.03 | 2 | 0 | 0 | | | |
|* 54 | SORT JOIN | | 4001 | 10 | 38930 |00:00:00.02 | 2 | 0 | 0 | 2048 | 2048 | 2048 (0)|
| 55 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 2 | 0 | 0 | | | |
| 56 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF040_C65F3617 | 1 | 10 | 10 |00:00:00.01 | 2 | 0 | 0 | | | |
|* 57 | FILTER | | 4001 | | 4001 |00:00:00.17 | 2 | 0 | 0 | | | |
|* 58 | SORT JOIN | | 4001 | 100 | 281K|00:00:00.11 | 2 | 0 | 0 | 11264 | 11264 |10240 (0)|
| 59 | VIEW | | 1 | 100 | 100 |00:00:00.01 | 2 | 0 | 0 | | | |
| 60 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF041_C65F3617 | 1 | 100 | 100 |00:00:00.01 | 2 | 0 | 0 | | | |
|* 61 | FILTER | | 4001 | | 4000 |00:00:04.55 | 15 | 0 | 0 | | | |
|* 62 | SORT JOIN | | 4001 | 4000 | 8002K|00:00:03.05 | 15 | 0 | 0 | 267K| 267K| 237K (0)|
| 63 | VIEW | | 1 | 4000 | 4000 |00:00:00.01 | 15 | 0 | 0 | | | |
| 64 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF042_C65F3617 | 1 | 4000 | 4000 |00:00:00.01 | 15 | 0 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
23 - filter(INTERNAL_FUNCTION("ST1") IS NOT NULL)
25 - filter(INTERNAL_FUNCTION("ST2") IS NOT NULL)
27 - filter(INTERNAL_FUNCTION("ST3") IS NOT NULL)
29 - filter(INTERNAL_FUNCTION("ST4") IS NOT NULL)
31 - filter("ET1" IS NOT NULL)
33 - filter("ET2" IS NOT NULL)
35 - filter("ET3" IS NOT NULL)
37 - filter("ET4" IS NOT NULL)
39 - filter(ROWNUM<=10)
41 - filter(ROWNUM<=10)
49 - filter("T19"."ST"<NVL("T1"."ET1",SYSDATE@!))
50 - access(INTERNAL_FUNCTION("T19"."ST")>=INTERNAL_FUNCTION("T1"."ST1"))
filter(INTERNAL_FUNCTION("T19"."ST")>=INTERNAL_FUNCTION("T1"."ST1"))
53 - filter("T19"."ST"<NVL("T2"."ET2",SYSDATE@!))
54 - access(INTERNAL_FUNCTION("T19"."ST")>=INTERNAL_FUNCTION("T2"."ST2"))
filter(INTERNAL_FUNCTION("T19"."ST")>=INTERNAL_FUNCTION("T2"."ST2"))
57 - filter("T19"."ST"<NVL("T3"."ET3",SYSDATE@!))
58 - access(INTERNAL_FUNCTION("T19"."ST")>=INTERNAL_FUNCTION("T3"."ST3"))
filter(INTERNAL_FUNCTION("T19"."ST")>=INTERNAL_FUNCTION("T3"."ST3"))
61 - filter("T19"."ST"<NVL("T4"."ET4",SYSDATE@!))
62 - access(INTERNAL_FUNCTION("T19"."ST")>=INTERNAL_FUNCTION("T4"."ST4"))
filter(INTERNAL_FUNCTION("T19"."ST")>=INTERNAL_FUNCTION("T4"."ST4"))
117 rows selected.
SQL>
