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.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
/*Создаем объекты*/
CREATE SEQUENCE tmp_sales_seq;
CREATE TABLE tmp_sales
( sale_date date
,sale_type varchar2(10)
,sale_id number
--block1
,block1_data1 number
,block1_data2 number
,block1_data3 number
,block1_data4 number
,block1_data5 number
--block2
,block2_data1 number
,block2_data2 number
,block2_data3 number
,block2_data4 number
,block2_data5 number
--block3
,block3_data1 number
,block3_data2 number
,block3_data3 number
--block4
,block4_data1 number
,block4_data2 number
,block4_data3 number
,CONSTRAINT tmp_sales_pk PRIMARY KEY(sale_date, sale_type, sale_id) USING INDEX LOCAL
)
PARTITION BY RANGE(sale_date) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY LIST(sale_type)
SUBPARTITION TEMPLATE
(SUBPARTITION sp_part1 VALUES ('TYPE_1')
,SUBPARTITION sp_part2 VALUES ('TYPE_2')
,SUBPARTITION sp_part3 VALUES ('TYPE_3')
)
(partition p_start values less than(to_date('01-01-2018','dd-mm-yyyy')) segment creation deferred);
/*Добавляем для теста 2-3 мил. записей*/
BEGIN
FOR indx IN 1 .. 12
LOOP
INSERT INTO tmp_sales
(sale_date
,sale_type
,sale_id
,block1_data1
,block1_data2
,block1_data3
,block1_data4
,block1_data5
,block2_data1
,block2_data2
,block2_data3
,block2_data4
,block2_data5
,block3_data1
,block3_data2
,block3_data3
,block4_data1
,block4_data2
,block4_data3)
SELECT trunc(SYSDATE)
,'TYPE_1'
,tmp_sales_seq.nextval
,round(dbms_random.value(1, 10))
,round(dbms_random.value(11, 20))
,round(dbms_random.value(21, 30))
,round(dbms_random.value(31, 40))
,round(dbms_random.value(41, 50))
--
,round(dbms_random.value(201, 210))
,round(dbms_random.value(211, 220))
,round(dbms_random.value(221, 230))
,round(dbms_random.value(231, 240))
,round(dbms_random.value(241, 250))
--
,round(dbms_random.value(301, 310))
,round(dbms_random.value(311, 320))
,round(dbms_random.value(321, 330))
--
,round(dbms_random.value(401, 410))
,round(dbms_random.value(411, 420))
,round(dbms_random.value(421, 430))
FROM all_objects;
COMMIT;
END LOOP;
END;
/*Собираем статистику*/
begin
dbms_stats.gather_table_stats(ownname => OWNNAME,tabname => 'TMP_SALES');
end;
/*Запрос из партиции и сабпартиции с сортировкой по ID*/
select
sale_date, sale_type, sale_id,
block1_data1, block1_data2, block1_data3, block1_data4, block1_data5,
block2_data1, block2_data2, block2_data3, block2_data4, block2_data5,
block3_data1, block3_data2, block3_data3,
block4_data1, block4_data2, block4_data3
from tmp_sales
where sale_date = to_date('14.12.2018','dd.mm.yyyy')
and sale_type = 'TYPE_1'
order by sale_id desc;
/*Все 3 поля (sale_date, sale_type, sale_id) есть в индексе PK, запрос выполняется быстро */
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1157732 | 92618560 | 22356 | 00:04:34 |
| 1 | PARTITION RANGE SINGLE | | 1157732 | 92618560 | 22356 | 00:04:34 |
| 2 | PARTITION LIST SINGLE | | 1157732 | 92618560 | 22356 | 00:04:34 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID | TMP_SALES | 1157732 | 92618560 | 22356 | 00:04:34 |
| * 4 | INDEX RANGE SCAN DESCENDING | TMP_SALES_PK | 623685 | | 4456 | 00:00:55 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("SALE_DATE"=TO_DATE(' 2018-12-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SALE_TYPE"='TYPE_1')
/*Запрос из партиции и сабпартиции с сортировкой по не индексированному полю block1_data1*/
select
sale_date, sale_type, sale_id,
block1_data1, block1_data2, block1_data3, block1_data4, block1_data5,
block2_data1, block2_data2, block2_data3, block2_data4, block2_data5,
block3_data1, block3_data2, block3_data3,
block4_data1, block4_data2, block4_data3
from tmp_sales
where sale_date = to_date('14.12.2018','dd.mm.yyyy')
and sale_type = 'TYPE_1'
order by block1_data1 desc;
/*Попали в партицию и сабпапртицию, но для сортировки приходится делать FULL скан всех данных в сабпартиции, запрос выполняется ДОЛГО */
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1157732 | 92618560 | 24351 | 00:04:59 |
| 1 | SORT ORDER BY | | 1157732 | 92618560 | 24351 | 00:04:59 |
| 2 | PARTITION RANGE SINGLE | | 1157732 | 92618560 | 2846 | 00:00:35 |
| 3 | PARTITION LIST SINGLE | | 1157732 | 92618560 | 2846 | 00:00:35 |
| * 4 | TABLE ACCESS FULL | TMP_SALES | 1157732 | 92618560 | 2846 | 00:00:35 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 4 - filter("SALE_DATE"=TO_DATE(' 2018-12-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
/*Напрашивается создание индекса. Локального? */
CREATE INDEX tmp_sales_inx01 ON tmp_sales (sale_date, sale_type, block1_data1) LOCAL;
/*После чего план опять радует*/
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1157732 | 92618560 | 0 | 00:00:01 |
| 1 | PARTITION RANGE SINGLE | | 1157732 | 92618560 | 0 | 00:00:01 |
| 2 | PARTITION LIST SINGLE | | 1157732 | 92618560 | 0 | 00:00:01 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID | TMP_SALES | 1157732 | 92618560 | 0 | 00:00:01 |
| * 4 | INDEX RANGE SCAN DESCENDING | TMP_SALES_INX01 | 623685 | | 0 | 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("SALE_DATE"=TO_DATE(' 2018-12-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SALE_TYPE"='TYPE_1')
/*Имеем 2 индекса, по PK и tmp_sales_inx01 в отдельности они работают хорошо,
а есть мы будем фильтровать по block1_data1, а сортировать по sale_id, то опять работает ДОЛГО*/
select
sale_date, sale_type, sale_id,
block1_data1, block1_data2, block1_data3, block1_data4, block1_data5,
block2_data1, block2_data2, block2_data3, block2_data4, block2_data5,
block3_data1, block3_data2, block3_data3,
block4_data1, block4_data2, block4_data3
from tmp_sales
where sale_date = to_date('14.12.2018','dd.mm.yyyy')
and sale_type = 'TYPE_1'
and block1_data1 = 5
order by sale_id desc;
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 136444 | 10915520 | 2537 | 00:00:32 |
| 1 | SORT ORDER BY | | 136444 | 10915520 | 2537 | 00:00:32 |
| 2 | PARTITION RANGE SINGLE | | 136444 | 10915520 | 1 | 00:00:01 |
| 3 | PARTITION LIST SINGLE | | 136444 | 10915520 | 1 | 00:00:01 |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID | TMP_SALES | 136444 | 10915520 | 1 | 00:00:01 |
| * 5 | INDEX RANGE SCAN | TMP_SALES_INX01 | 73504 | | 1 | 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 5 - access("SALE_DATE"=TO_DATE(' 2018-12-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SALE_TYPE"='TYPE_1' AND "BLOCK1_DATA1"=5)
