hi all
... в продолжение нескольких прошлых топегов про over()-инструменты.
LI-T3.0.0.31003, SuperServer.
1.
2.
3.
recreate table td(id int primary key, pid int , s01 varchar(16) ); commit;
insert into td select row_number()over(), 1+rand()*49999, left(uuid_to_char(gen_uuid()),16)
from rdb$types,rdb$types,rdb$types rows 1000000;
commit;
Итого: число разных pid не превышает 50 тыс, всего же - 1 млн записей.
Допустим, требуется найти для каждого pid такое значение поля s01, которое соотв-вует максимальному id для этого pid. Эту задачу
спрашивал недавно товарищ "ТРАКТОРА", но она вообще частая гостья.
(пользуясь случаем, должен принести пардон Трактору: я там привел запрос с неверным результатом, нужно было учесть, что каст чисел к char(11) и их сортировка будут идти по алфавиту, а не по возрастанию значений :); фикс очевиден - см ниже)
Смотрим, что будет со статистикой для следующих трёх вариантов.
var-1. Без употребления over()-функций:
1.
select d.pid, substring( max( cast( 10000000000 + id as char(11)) || d.s01 ) from 12) s from td d group by 1
Трейс для трёх запусков:
~2960 ms
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.
2014-04-06T01:41:51.3740 (6403:0x7f309ceda100) EXECUTE_STATEMENT_FINISH
sqlex30 (ATT_397, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
/opt/fb30trnk/bin/isql:361
(TRA_12801, CONCURRENCY | WAIT | READ_WRITE)
Statement 107:
-------------------------------------------------------------------------------
select d.pid, substring( max( cast( 10000000000 + id as char(11)) || d.s01 ) from 12) s from td d group by 1
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (D NATURAL)
50000 records fetched
2962 ms, 18108 read(s), 2036385 fetch(es)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TD 1000000
2014-04-06T01:41:56.0120 (6403:0x7f309ceda100) EXECUTE_STATEMENT_FINISH
sqlex30 (ATT_397, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
/opt/fb30trnk/bin/isql:361
(TRA_12801, CONCURRENCY | WAIT | READ_WRITE)
Statement 108:
-------------------------------------------------------------------------------
select d.pid, substring( max( cast( 10000000000 + id as char(11)) || d.s01 ) from 12) s from td d group by 1
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (D NATURAL)
50000 records fetched
2964 ms, 18200 read(s), 2036385 fetch(es)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TD 1000000
2014-04-06T01:42:00.2280 (6403:0x7f309ceda100) EXECUTE_STATEMENT_FINISH
sqlex30 (ATT_397, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
/opt/fb30trnk/bin/isql:361
(TRA_12801, CONCURRENCY | WAIT | READ_WRITE)
Statement 109:
-------------------------------------------------------------------------------
select d.pid, substring( max( cast( 10000000000 + id as char(11)) || d.s01 ) from 12) s from td d group by 1
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (D NATURAL)
50000 records fetched
2961 ms, 18200 read(s), 2036385 fetch(es)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TD 1000000
План и статистика в isql:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
Select Expression
-> Aggregate
-> Sort ( record length: 96 , key length: 8)
-> Table "D" Full Scan
Current memory = 12766584
Delta memory = 0
Max memory = 788968768
Elapsed time= 3.044 sec
Cpu = 0.150 sec
Buffers = 2048
Reads = 18218
Writes = 0
Fetches = 2036407
С использованием max()over(), без group by:
1.
select pid, s from ( select d.pid, d.id, max(id)over(partition by pid) m, s01 s from td d ) where id=m
Trace:
~4530 ms
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.
2014-04-06T01:46:04.9040 (6403:0x7f309ceda100) EXECUTE_STATEMENT_FINISH
sqlex30 (ATT_397, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
/opt/fb30trnk/bin/isql:361
(TRA_12801, CONCURRENCY | WAIT | READ_WRITE)
Statement 110:
-------------------------------------------------------------------------------
select pid, s from ( select d.pid, d.id, max(id)over(partition by pid) m, s01 s from td d ) where id=m
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (D NATURAL)
50000 records fetched
4528 ms, 18200 read(s), 2036385 fetch(es)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TD 1000000
2014-04-06T01:46:12.1840 (6403:0x7f309ceda100) EXECUTE_STATEMENT_FINISH
sqlex30 (ATT_397, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
/opt/fb30trnk/bin/isql:361
(TRA_12801, CONCURRENCY | WAIT | READ_WRITE)
Statement 111:
-------------------------------------------------------------------------------
select pid, s from ( select d.pid, d.id, max(id)over(partition by pid) m, s01 s from td d ) where id=m
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (D NATURAL)
50000 records fetched
4528 ms, 18200 read(s), 2036385 fetch(es)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TD 1000000
2014-04-06T01:46:17.7220 (6403:0x7f309ceda100) EXECUTE_STATEMENT_FINISH
sqlex30 (ATT_397, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
/opt/fb30trnk/bin/isql:361
(TRA_12801, CONCURRENCY | WAIT | READ_WRITE)
Statement 112:
-------------------------------------------------------------------------------
select pid, s from ( select d.pid, d.id, max(id)over(partition by pid) m, s01 s from td d ) where id=m
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (D NATURAL)
50000 records fetched
4531 ms, 18200 read(s), 2036385 fetch(es)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TD 1000000
isql:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
Select Expression
-> Filter
-> Window
-> Record Buffer (record length: 193)
-> Sort ( record length: 188 , key length: 8)
-> Window
-> Record Buffer (record length: 97)
-> Table "D" Full Scan
Current memory = 12747264
Delta memory = 0
Max memory = 788968768
Elapsed time= 4.595 sec
Cpu = 0.150 sec
Buffers = 2048
Reads = 18217
Writes = 0
Fetches = 2036406
Время сильно больше. Не связано ли это с record len, которая здесь 188, а в первом варианте - 96 ?
var-3. Через dense_rank() вместо предыдущего max()over():
1.
select pid, s from (select d.pid, dense_rank()over(partition by pid order by id desc) r, s01 s from td d) where r=1
Trace:
~4750 ms
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.
2014-04-06T01:51:08.0030 (6403:0x7f309ceda100) EXECUTE_STATEMENT_FINISH
sqlex30 (ATT_397, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
/opt/fb30trnk/bin/isql:361
(TRA_12801, CONCURRENCY | WAIT | READ_WRITE)
Statement 113:
-------------------------------------------------------------------------------
select pid, s from (select d.pid, dense_rank()over(partition by pid order by id desc) r, s01 s from td d) where r=1
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (D NATURAL)
50000 records fetched
4754 ms, 18200 read(s), 2036385 fetch(es)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TD 1000000
2014-04-06T01:51:20.6540 (6403:0x7f309ceda100) EXECUTE_STATEMENT_FINISH
sqlex30 (ATT_397, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
/opt/fb30trnk/bin/isql:361
(TRA_12801, CONCURRENCY | WAIT | READ_WRITE)
Statement 114:
-------------------------------------------------------------------------------
select pid, s from (select d.pid, dense_rank()over(partition by pid order by id desc) r, s01 s from td d) where r=1
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (D NATURAL)
50000 records fetched
4742 ms, 18200 read(s), 2036385 fetch(es)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TD 1000000
2014-04-06T01:51:27.0950 (6403:0x7f309ceda100) EXECUTE_STATEMENT_FINISH
sqlex30 (ATT_397, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
/opt/fb30trnk/bin/isql:361
(TRA_12801, CONCURRENCY | WAIT | READ_WRITE)
Statement 115:
-------------------------------------------------------------------------------
select pid, s from (select d.pid, dense_rank()over(partition by pid order by id desc) r, s01 s from td d) where r=1
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (D NATURAL)
50000 records fetched
4760 ms, 18200 read(s), 2036385 fetch(es)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TD 1000000
isql:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
Select Expression
-> Filter
-> Window
-> Record Buffer (record length: 185)
-> Sort (record length: 188, key length: 16)
-> Window
-> Record Buffer (record length: 97)
-> Table "D" Full Scan
Current memory = 12745256
Delta memory = 0
Max memory = 788968768
Elapsed time= 4.825 sec
Cpu = 0.160 sec
Buffers = 2048
Reads = 18217
Writes = 0
Fetches = 2036406
Я не понимаю, отчего второй вариант так сильно проигрывает первому. Печалько, что не видно ни фига, сколько затрат на себя берёт та или иная операция в extended-plan'e.
И также интересно зело, почему третий вариант отстаёт от второго (ненамного, но это я вижу устойчиво).
ЗЫ. Да, понятно, что для случая,
показанного недавно Денисом, со "знакопеременными" сортировка в одном запросе:
1.
2.
select min( x )over(partition by y order by z asc), max( y )over(partition by x order by z desc)
from ...
- оконные ф-ции рулят.
Но хотелось бы, чтобы рулёж этот был и в более простых вещах, которые куда как чаще встречаются...
