Здравствуйте, коллеги.
В этой статье я расскажу о своих изысканиях в вопросе: «А что же лучше: dense_rank() или max()» и, конечно, почему эти изыскания завершились с неожиданным, по крайней мере для меня, результатом.
Так сложились звезды, что мне нужно сейчас искать работу. Перед каждым собеседованием я изучаю компанию, в которую меня пригласили, дабы понимать чем занимается компания, чему я научусь если мне сделают оффер и т.д. И вот, в один прекрасный момент, мне пришло приглашение на интервью, на позицию PL/SQL разработчика, от одной прекрасной компании. Прочитав о ней, мне показалось, что я влюблен и хочу там работать. Когда же я пришел на само интервью и в тот момент, когда все к интервью уже готово, а оно еще не началось просто потому что люди знакомятся, hr предлагает кофе и т.д., я уже понял, что хочу, очень хочу, тут работать.
За все интервью с тимлидом, мне один раз предложили написать простенький запрос, в остальном же все проходило в режиме: вопрос-ответ.
Задачка звучала так:
«У нас есть таблица операций, в ней 4 столбца: id операции, id клиента, дата операции, сумма операции. Нужно вывести последние операции по каждому клиенту с максимальной суммой за определенный период».
И я, конечно, начал лихорадочно соображать, как же написать так, чтобы было красиво и эффективно. А кроме оракла я еще работал с терадатой и в тот момент мой мозг выдал вот такой запрос:
На что мне был задан вопрос: «А почему была использована max(), а не dense_rank()?», не помню что я точно ответил, но звучало это приблизительно так: «max() я использовал чаще и могу, более-менее точно, представить, что он мне вернет, в отличии от dense_rank()». Дальше описывать интервью не буду, скажу только, что мне, конечно же, отказали. Позже, уже дома, в попытках проанализировать все и понять ошибки, я пришел к выводу, что слишком сильно хотел там работать и переволновался, иначе ту кашу, творившуюся у меня в голове во время интервью, я объяснить не могу. Это было нечто сродное с тем ощущением, когда школьник пытается поговорить с девочкой, которую он тайно любит еще с садика, но эти попытки все больше и больше ставят его в неловкое положение. Так же и я, стараясь выглядеть спокойным и адекватным, показал себя как никудышный специалист. В общем, я решил для себя выяснить, что лучше использовать dense_rank() или max() при решении такой задачи.
Если вы хотите собственными глазами увидеть все, что я напишу и собственными руками это все потрогать — я подготовил набор скриптов создания данных для теста:
Итак, тестовые данные созданы, пора приступать к, собственно, самим запросам. Для того, чтобы не обрезать наши 20000 строк, мы не будем ограничивать нашу выборку каким-то определенным периодом, нам ведь важно понять какой метод лучше и эффективнее, а
Предварительные планы для этих запросов (получено в pl/sql developer):
Max:
Dense_rank:
Но это предварительные планы, реальные получим при помощи утилиты SQLTUNE:
Помимо реального плана SQLTUNE также выдает рекомендации по оптимизации скрипта, в нашем случае он рекомендует собрать статистику, но так как табличка у нас одна, то и запросы находятся в одинаковых условиях.
Но не это побудило меня писать статью.
Позже я написал «запрос с dense_rank()» и начал сравнивать планы, но заметив этот злополучный
Да и вообще, сказать что я сильно удивился — ничего не сказать… Как так получилось? Почему
Отсюда видим, что и предварительный, и реальный планы не ошибочны, никакого подвоха вроде бы как нет и можно радоваться десятикратному ускорению. А так ли это?
P.S. Я так и не смог ответить на этот вопрос и все еще не верю в то, что запрос реально ускорить в разы при помощи order by. Буду и дальше пытаться выяснить этот момент, к чему и вас призываю. И да откроются перед нами сокрытые тайны оракла!
P.P.S. Благодарю всех за внимание! Если вы вместе со мной проводили тест — не забываем чистить за собой базу, особенно если это прод какого нибудь банка.
В этой статье я расскажу о своих изысканиях в вопросе: «А что же лучше: dense_rank() или max()» и, конечно, почему эти изыскания завершились с неожиданным, по крайней мере для меня, результатом.
Предыстория:
Так сложились звезды, что мне нужно сейчас искать работу. Перед каждым собеседованием я изучаю компанию, в которую меня пригласили, дабы понимать чем занимается компания, чему я научусь если мне сделают оффер и т.д. И вот, в один прекрасный момент, мне пришло приглашение на интервью, на позицию PL/SQL разработчика, от одной прекрасной компании. Прочитав о ней, мне показалось, что я влюблен и хочу там работать. Когда же я пришел на само интервью и в тот момент, когда все к интервью уже готово, а оно еще не началось просто потому что люди знакомятся, hr предлагает кофе и т.д., я уже понял, что хочу, очень хочу, тут работать.
За все интервью с тимлидом, мне один раз предложили написать простенький запрос, в остальном же все проходило в режиме: вопрос-ответ.
Задачка звучала так:
«У нас есть таблица операций, в ней 4 столбца: id операции, id клиента, дата операции, сумма операции. Нужно вывести последние операции по каждому клиенту с максимальной суммой за определенный период».
И я, конечно, начал лихорадочно соображать, как же написать так, чтобы было красиво и эффективно. А кроме оракла я еще работал с терадатой и в тот момент мой мозг выдал вот такой запрос:
Скрытый текст
И в случае с терадатой такое бы сработало, но не с ораклом, увы. И четко помня, что «qualify» в оракле нет, я написал на бумажке что то на подобии:/*id операции - oper_id, id клиента - client_id, дата операции - input_date, сумма операции - amount*/
select t.*
, max(t.amount) over (partition by t.client_id) as m_a
from some_table t
qualify oper_id = max(t.oper_id) over (partition by t.client_id)
where m_a = amount
Скрытый текст
select t.* from some_table t
where amount = max(t.oper_id) over (partition by t.client_id)
На что мне был задан вопрос: «А почему была использована max(), а не dense_rank()?», не помню что я точно ответил, но звучало это приблизительно так: «max() я использовал чаще и могу, более-менее точно, представить, что он мне вернет, в отличии от dense_rank()». Дальше описывать интервью не буду, скажу только, что мне, конечно же, отказали. Позже, уже дома, в попытках проанализировать все и понять ошибки, я пришел к выводу, что слишком сильно хотел там работать и переволновался, иначе ту кашу, творившуюся у меня в голове во время интервью, я объяснить не могу. Это было нечто сродное с тем ощущением, когда школьник пытается поговорить с девочкой, которую он тайно любит еще с садика, но эти попытки все больше и больше ставят его в неловкое положение. Так же и я, стараясь выглядеть спокойным и адекватным, показал себя как никудышный специалист. В общем, я решил для себя выяснить, что лучше использовать dense_rank() или max() при решении такой задачи.
Исследование
Если вы хотите собственными глазами увидеть все, что я напишу и собственными руками это все потрогать — я подготовил набор скриптов создания данных для теста:
Скрытый текст
/*создаем табличку*/
create table habr_test_table_220414
(
oper_id number,
client_id number,
input_date date,
amount number,
constraint habr_test_table_220414_pk primary key (oper_id)
);
grant all on habr_test_table_220414 to public;
/*Для того, чтобы поле oper_id было уникальным - создадим последовательность*/
create sequence habr_test_sequence_220414 increment by 1;
grant all on habr_test_sequence_220414 to public;
/*и триггер, который при вставке в таблицу, будет подменять значение oper_id на следующий элемент последовательности*/
create trigger habr_test_trigger_220414
before insert
on habr_test_table_220414
for each row
begin
:new.oper_id := habr_test_sequence_220414.nextval;
end;
/*Наполняем табличку данными и да поможет нам рандом заполнить ее разнообразными значениями*/
/*Для теста примем период в год, также рассмотрим ситуацию с 10-тью клиентами*/
/*Для того, чтобы нам не выдавался cost = 3, наполним табличку 20000 строк, если кому то хочется*/
/*больше или меньше - разделите желаемое количество пополам и присвойте это значение переменной counter */
declare
counter number := 10000;
i number := 0;
begin
loop
insert into habr_test_table_220414
(
client_id
, input_date
, amount
)
values
(
trunc (dbms_random.value (1, 11))
, to_date(trunc(dbms_random.value(to_char(date '2013-01-01','j'),to_char(date '2013-12-31','j'))),'j')
, trunc (dbms_random.value (1, 100000))
);
exit when (i = counter);
i := i + 1;
end loop;
commit;
/*Дабы обеспечить возникновение ситуации, когда мы имеем одинаковые суммы по операциям одного клиента*/
/*выполним такую команду:*/
insert into habr_test_table_220414 select * from habr_test_table_220414;
commit;
/*а о уникальности id операции позаботится триггер*/
end;Итак, тестовые данные созданы, пора приступать к, собственно, самим запросам. Для того, чтобы не обрезать наши 20000 строк, мы не будем ограничивать нашу выборку каким-то определенным периодом, нам ведь важно понять какой метод лучше и эффективнее, а
where input_date between to_date('01.01.2013','dd.mm.yyyy') and to_date('01.05.2013','dd.mm.yyyy')Запрос с использованием max()
select * from
(
select c.*
, max(c.oper_id) over (partition by c.client_id) as m_o/*max_operation*/
from
(
select t.*
, max(t.amount) over (partition by t.client_id) as m_a/*max_amount*/
from habr_test_table_220414 t
) c
where c.m_a = c.amount
) where m_o = oper_id;
Запрос с использованием dense_rank()
select * from
(
select c.*
, dense_rank() over (partition by c.client_id order by c.oper_id desc) as m_o/*max_operation*/
from
(
select t.*
, dense_rank() over (partition by t.client_id order by t.amount desc) as m_a/*max_amount*/
from habr_test_table_220414 t
) c
where c.m_a = 1
) where m_o = 1;Предварительные планы для этих запросов (получено в pl/sql developer):
Max:
Скрытый текст
Dense_rank:
Скрытый текст
Но это предварительные планы, реальные получим при помощи утилиты SQLTUNE:
Подготовка:
и выглядят эти реальные планы вот так:/*Для запроса с max()*/
DECLARE
my_task_name varchar2(30);my_sqltext clob;rep_tuning clob;
BEGIN
Begin
DBMS_SQLTUNE.DROP_TUNING_TASK('my_sql_tuning_task_max');
exception when others then NULL;
end;
MY_SQLTEXT:=
'select * from
(
select c.*
, max(c.oper_id) over (partition by c.client_id) as m_o/*max_operation*/
from
(
select t.*
, max(t.amount) over (partition by t.client_id) as m_a/*max_amount*/
from habr_test_table_220414 t
) c
where c.m_a = c.amount
) where m_o = oper_id';
MY_TASK_NAME:=DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_TEXT => my_sqltext,
TIME_LIMIT => 60, --задается время выполнения в секундах
TASK_NAME =>'my_sql_tuning_task_max', DESCRIPTION=> my_task_name ,
SCOPE => DBMS_SQLTUNE.scope_comprehensive);
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_sql_tuning_task_max');
exception when others then null;
end;
END;
/*Для запроса с dense_rank()*/
DECLARE
my_task_name varchar2(30);my_sqltext clob;rep_tuning clob;
BEGIN
Begin
DBMS_SQLTUNE.DROP_TUNING_TASK('my_sql_tuning_task_dense');
exception when others then NULL;
end;
MY_SQLTEXT:=
'select * from
(
select c.*
, dense_rank() over (partition by c.client_id order by c.oper_id desc) as m_o/*max_operation*/
from
(
select t.*
, dense_rank() over (partition by t.client_id order by t.amount desc) as m_a/*max_amount*/
from habr_test_table_220414 t
) c
where c.m_a = 1
) where m_o = 1';
MY_TASK_NAME:=DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_TEXT => my_sqltext,
TIME_LIMIT => 60, --задается время выполнения в секундах
TASK_NAME =>'my_sql_tuning_task_dense', DESCRIPTION=> my_task_name ,
SCOPE => DBMS_SQLTUNE.scope_comprehensive);
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_sql_tuning_task_dense');
exception when others then null;
end;
END;
/*К сожалению, что то сломалось и половина запросов в этом куске кода разукрашена цветом как строка*/
/*прошу прощения, не обращайте на это внимание*/Скрытый текст
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_max') FROM DUAL;
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_dense') FROM DUAL;
Помимо реального плана SQLTUNE также выдает рекомендации по оптимизации скрипта, в нашем случае он рекомендует собрать статистику, но так как табличка у нас одна, то и запросы находятся в одинаковых условиях.
Предварительный итог
После всех этих манипуляций мне, как я надеюсь и вам, ясно что при решении этой задачи max() отрабатывает быстрее чем dense_rank() в 2 раза и съедает вдвое меньше процессорного времени. Ну оно то понятно итак, без планов и прочего, ведь max() это всего лишь поиск наибольшего, в то время как dense_rank() — это, в первую очередь сортировка, а уже потом нумерование.Но не это побудило меня писать статью.
Внезапно
В процессе первоначального заполнения таблицы для теста, это я ведь для статьи сообразил скрипты, а в первый раз все происходило практически в ручном режиме, и для проверки состояния подопытной таблицы я использовал запрос сorder by.Скрытый текст
После этого я модифицировал этот запрос до финального состояния «запрос с max()», так и не убрав /*сначала было 10 инсертов и запрос*/
insert into habr_test_table_220414...;
....
....
insert into habr_test_table_220414...;
commit;
select * from habr_test_table_220414 t order by t.client_id;
/*потом несколько раз повторялся кусок:*/
insert into habr_test_table_220414 select * from habr_test_table_220414;
commit;
select * from habr_test_table_220414 t order by t.client_id;
/*вот так я и строил первые тестовые данные*/
order by. Вот что получилось:
select * from
(
select c.*
, max(c.oper_id) over (partition by c.client_id) as m_o/*max_operation*/
from
(
select t.*
, max(t.amount) over (partition by t.client_id) as m_a/*max_amount*/
from habr_test_table_220414 t
order by t.client_id
) c
where c.m_a = c.amount
) where m_o = oper_id
Позже я написал «запрос с dense_rank()» и начал сравнивать планы, но заметив этот злополучный
order by в запросе с max(), удалил order by, но cost его я уже увидел и запомнил. И когда увидел cost в запросе с max() без order by сильно удивился, ведь:Предварительный план
Реальный план от SQLTUNE
Да и вообще, сказать что я сильно удивился — ничего не сказать… Как так получилось? Почему
order by ускорил запрос в 10 раз? Решил найти ответ в трассировке. Не буду писать как именно снимать трассу в оракле, ибо это тема для отдельной статьи, да и статьи с описанием сего процесса легко найти во всемирной паутине. Предоставлю только набор скриптов, которыми я проводил трассировку и ссылку на такую статью, нашел я ее довольно давно, с тех пор она меня выручает:Скрытый текст
В трассе нас интересует кусок, который описывает действия оракла при выполнении запроса, а именно:Ссылка на статью по включению трассировки
alter system set timed_statistics=true;
alter session set tracefile_identifier='test_for_habr_220414';
alter session set events '10046 trace name context forever, level 12';
select * from
(
select c.*
, max(c.oper_id) over (partition by c.client_id) as m_o/*max_operation*/
from
(
select t.*
, max(t.amount) over (partition by t.client_id) as m_a/*max_amount*/
from habr_test_table_220414 t
order by client_id
) c
where c.m_a = c.amount
) where m_o = oper_id;
alter session set events '10046 trace name context off';
select value from v$parameter p where name='user_dump_dest';
/*Далее расшифровываем файл трассы при помощи tkprof*/
/*в имени файла будет присутствовать 'test_for_habr_220414'*/Скрытый текст
select * from
(
select c.*
, max(c.oper_id) over (partition by c.client_id) as m_o/*max_operation*/
from
(
select t.*
, max(t.amount) over (partition by t.client_id) as m_a/*max_amount*/
from habr_test_table_220414 t
order by client_id
) c
where c.m_a = c.amount
) where m_o = oper_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.03 0.02 0 84 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.04 0.03 0 85 0 10
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
----- ---------------------------------------------------
10 VIEW (cr=84 pr=0 pw=0 time=28155 us cost=23 size=1592850 card=21525)
20 WINDOW BUFFER (cr=84 pr=0 pw=0 time=28145 us cost=23 size=1313025 card=21525)
20 VIEW (cr=84 pr=0 pw=0 time=21628 us cost=23 size=1313025 card=21525)
22010 WINDOW SORT(cr=84 pr=0 pw=0 time=24393 us cost=23 size=1033200 card=21525)
22010 TABLE ACCESS FULL HABR_TEST_TABLE_220414(cr=84 pr=0 pw=0 time=5172 us cost=23 size=1033200 card=21525)
Итоги
Отсюда видим, что и предварительный, и реальный планы не ошибочны, никакого подвоха вроде бы как нет и можно радоваться десятикратному ускорению. А так ли это?
P.S. Я так и не смог ответить на этот вопрос и все еще не верю в то, что запрос реально ускорить в разы при помощи order by. Буду и дальше пытаться выяснить этот момент, к чему и вас призываю. И да откроются перед нами сокрытые тайны оракла!
P.P.S. Благодарю всех за внимание! Если вы вместе со мной проводили тест — не забываем чистить за собой базу, особенно если это прод какого нибудь банка.
Скрытый текст
drop trigger habr_test_trigger_220414;
drop sequence habr_test_sequence_220414;
drop table habr_test_table_220414;
