F-r-IEND Apr 23 2014 at 00:22

или расследование с неожиданным концом

8 min

14K

Oracle*SQL*

From sandbox

Comments 15

maovrn Apr 23 2014 at 15:04

Что-то очень сумбурно: собеседование, ваши впечатления, вопрос, процесс решения, измерение производительности, планы запросов и все это в одной статье. Тяжело удерживать фокус.

А ключ похоже содержится в самом вопросе: «Нужно вывести последние операции по каждому клиенту». Операции — множественное число. Max всегда возвращает 1 строку. Если клиент совершил 3 операции на миллион долларов, в выводе будет отображена только одна из них, случайная операция из этих трех максимальных. Dense_rank выведет их все. И эффективность тут непричем.

F-r-IEND Apr 23 2014 at 17:29

Постараюсь ответить по порядку:
Собеседование: в реальной жизни у меня не было времени и желания сравнивать функции, если решение удовлетворяет требованиям по производительности — никто ничего не меняет. Работает, устраивает заказчика — задача выполнена, у вас не так?
В данном случае я хотел описать причину такого интереса к этим функциям.
Мои впечатления: — это попытка оправдаться перед сообществом и перед самим собой за сорванное интервью, а также описать свои чувства и меру своего желания разобраться в данном вопросе. Что является еще одним мотиватором для детального изучения вопроса.
Вопрос, решение задачи, измерение производительности, планы запросов — все это все касается решения задачи.
Главный акцент в статье — это странное поведение оракла при использовании order by, вот собственно и все, что я хотел рассказать, но без остального картинка не была бы полной.

maovrn Apr 23 2014 at 18:19

Желание разобраться и провести работу над ошибками похвально.

>если решение удовлетворяет требованиям по производительности
Прежде всего, решение должно удовлетворять условию задачи (правильно работать), и только потом уже требованиям производительности. У вас же запросы с max и dense_rank дают разный результат!

Сортировка очень помогает группировке. И максимальное значение на отсортированных данных найти элементарно. Вот и получилось, что проще один раз явно отсортировать данные и использовать их в следующем подзапросе, чем неявно вызывать ее при группировке и поиске максимального значения по-отдельности. А на cost акцентировать внимание не стоит. Иногда при переписывании запроса cost увеличивается в сотни раз, а время выполнения уменьшается.

F-r-IEND Apr 24 2014 at 18:02

У вас же запросы с max и dense_rank дают разный результат!

Да ну неужели, правда что ль? Может сами проверите. Я, конечно, могу скинуть скрин, но не думаю что вы ему поверите.

maovrn Apr 24 2014 at 19:47

Я вам верю, не надо меня принимать за упертого. Почему у вас результат с max и dense_rank совпадает я описал в комментарии от 23 апреля 2014 в 18:29. Так как вы сделали сортировку по oper_id (уникальный номер), вы избавились от случая, когда ranking criteria совпадает. Хорошо это или плохо зависит от условий задачи.

F-r-IEND Apr 23 2014 at 17:51

Все запросы выведут последнюю операцию с максимальным amount.

maovrn Apr 23 2014 at 18:29

Да, сейчас заметил, вы берете минимальный ID операции:
dense_rank() over (partition by c.client_id order by c.oper_id desc)
Так как ID операции совпадать не может, это то же самое, что и max.
Самое интересное проявляется когда условие совпадает, для DENSE_RANK: Rows with equal values for the ranking criteria receive the same rank.

F-r-IEND Apr 24 2014 at 10:27

Странный вы человек, говорите

минимальный ID операции

и тут же приводите пример из моего кода

order by c.oper_id desc)

который говорит о том, что отсортировано по убыванию, и dense_rank поставит единицу там где будет максимальное значение.
Я предоставил все для того, чтобы каждый мог в течении 2 минут получить возможность протестировать собственными руками то, что я описал, при наличии у него под рукой какой нибудь бд оракл. Вы же не потрудились не то чтобы протестировать самому, вы даже не прочитали код, по крайней мере внимательно. А приведенный вами текст из документации ни о чем, вода — мокрая, воздух — воздушный, масло масленое…

zhekappp Apr 23 2014 at 15:04

Отличная статья, есть над чем подумать.

xtender Apr 25 2014 at 11:34

Честно говоря, вам еще учиться и учиться. Советую читать почаще оракловые форумы, например OTN и SQL.RU. Там вы научитесь делать правильные и удобные тест-кейсы и, самое главное, правильно их анализировать.
Полный разбор сейчас делать мне некогда, поэтому пробегусь бегло по некоторым наиболее важным вещам. Потом если захотите задать какие-либо вопросы — можете написать мне на почту.

1. Старайтесь избегать создания лишних объектов (а триггеры вообще старайтесь никогда не создавать) и максимально упрощать тест-кейс.
Например, можно было бы сделать так:

drop table habr_test_table purge;
/*создаем табличку*/
create table habr_test_table
(
   oper_id   
  ,client_id 
  ,input_date
  ,amount    
  ,constraint habr_test_table_pk primary key (oper_id)
)
as 
select
   rownum
  ,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))
from dual
connect by level<=50000;
-- Добавим дубли
insert into habr_test_table(oper_id,client_id,input_date,amount)
select rownum+50000,client_id,input_date,amount from habr_test_table;
commit;

call dbms_stats.gather_table_stats(user,'HABR_TEST_TABLE');

2. Собирайте всегда статистику, чтобы на выполнение запросов не влиял dynamic_sampling.

call dbms_stats.gather_table_stats(user,'HABR_TEST_TABLE');

3. Реальные планы не надо показывать через DBMS_SQLTUNE.REPORT_TUNING_TASK. Лучше показывать через dbms_xplan.display_cursor с параметром 'allstats last', или отчет dbms_sqltune.report_sql_monitor, ну или трассировку(что немного более геморройно). И лучше в текстовом виде.
А в идеале, готовить тестовый скрипт для sql*plus(командное окно PL/SQL developer`а в принципе это тоже умеет).
Например:

set echo on serverout off feed on timing on;
spool tests_habr.sql
-- ! on test db only !
alter system flush shared_pool;
alter session set "_optimizer_use_feedback"=false;
alter session set statistics_level=all;
-- 2 max:
select/*+ findme 1 */ * 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 t
        ) c 
    where c.m_a = c.amount
) where m_o = oper_id;
select * from table(dbms_xplan.display_cursor('','','allstats last'));

-- 2 dense_rank:
select/*+ findme 2 */ * 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 t
        ) c 
    where c.m_a = 1
) where m_o = 1;
select * from table(dbms_xplan.display_cursor('','','allstats last'));

-- 2 max + order by:
select/*+ findme 3 */ * 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 t
        order by t.client_id
        ) c 
    where c.m_a = c.amount
) where m_o = oper_id;
select * from table(dbms_xplan.display_cursor('','','allstats last'));

-- 1 row_number:
select/*+ findme 4 */ * from
(
    select t.*
    , row_number() over (partition by t.client_id order by t.amount desc, t.oper_id desc) as rn
    from habr_test_table t
) where rn = 1;
select * from table(dbms_xplan.display_cursor('','','allstats last'));

select 
     substr(sql_text,1,18) as text
    ,sql_id
    ,elapsed_time
    ,s.cpu_time
    ,s.user_io_wait_time
    ,s.BUFFER_GETS
    ,s.executions
from v$sql s 
where s.sql_text like 'select/*+ findme %'
order by 1;
spool off;
set echo off timing off;

4. У вас сомнительные данные с сомнительными выводами. Вообще для такого типа запросов очень важен объем данных. Например, на 100 тысячах записей нет никакой 10-кратной разницы.
Вот кусок вывода того скрипта, что я привел выше:

TEXT                 SQL_ID        ELAPSED_TIME   CPU_TIME USER_IO_WAIT_TIME BUFFER_GETS EXECUTIONS
-------------------- ------------- ------------ ---------- ----------------- ----------- ----------
select/*+ findme 1   6swypwnq6kxvy      1052892     920000            120803         454          1
select/*+ findme 2   0dgjzaawppasx       883205     860000             10838         394          1
select/*+ findme 3   am5gh1pxv4d43       940052     840000            103318         394          1
select/*+ findme 4   cyr7z0c7zq24p       764480     750000             11194         394          1

Все отклонения в пределах нормы. Советую еще протрассировать с ивентом 10032 — это sort trace. Там вы увидите разницу в кол-ве сравнений и используемой для этого памяти.

5. На самом деле, достаточно было одной аналитической функции, а так как у вас еще и уникальный oper_id, то лучше вообще row_number(в моем скрипте это пример №4)

-- 1 row_number:
select/*+ findme 4 */ * from
(
    select t.*
    , row_number() over (partition by t.client_id order by t.amount desc, t.oper_id desc) as rn
    from habr_test_table t
) where rn = 1;

xtender Apr 25 2014 at 11:41

На всякий случай прикладываю, полученные планы:
(в более удобном виде: gist.github.com/xtender/11281007 )

SQL> -- 2 max:
SQL> select/*+ findme 1 */ * from
  2  (
  3      select c.*
  4          , max(c.oper_id) over (partition by c.client_id) as m_o/*max_operation*/
  5      from
  6          (
  7          select t.*
  8          , max(t.amount) over (partition by t.client_id) as m_a/*max_amount*/
  9          from habr_test_table t
 10          ) c
 11      where c.m_a = c.amount
 12  ) where m_o = oper_id;

Plan hash value: 673711813

----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |      1 |        |     10 |00:00:01.02 |     392 |   1151 |    775 |       |       |          |         |
|*  1 |  VIEW                 |                 |      1 |  99720 |     10 |00:00:01.02 |     392 |   1151 |    775 |       |       |          |         |
|   2 |   WINDOW BUFFER       |                 |      1 |  99720 |     20 |00:00:01.02 |     392 |   1151 |    775 |  2048 |  2048 | 2048  (0)|         |
|*  3 |    VIEW               |                 |      1 |  99720 |     20 |00:00:01.02 |     392 |   1151 |    775 |       |       |          |         |
|   4 |     WINDOW SORT       |                 |      1 |  99720 |    100K|00:00:00.97 |     392 |   1151 |    775 |  3519K|   815K| 1179K (1)|    5120 |
|   5 |      TABLE ACCESS FULL| HABR_TEST_TABLE |      1 |  99720 |    100K|00:00:00.08 |     381 |    376 |      0 |       |       |          |         |
----------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("M_O"="OPER_ID")
   3 - filter("C"."M_A"="C"."AMOUNT")


SQL> -- 2 dense_rank:
SQL> select/*+ findme 2 */ * from
  2  (
  3      select c.*
  4          , dense_rank() over (partition by c.client_id order by c.oper_id desc) as m_o/*max_operation*/
  5      from
  6          (
  7          select t.*
  8          , dense_rank() over (partition by t.client_id order by t.amount desc) as m_a/*max_amount*/
  9          from habr_test_table t
 10          ) c
 11      where c.m_a = 1
 12  ) where m_o = 1;

Plan hash value: 331359864

---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                 |      1 |        |     10 |00:00:00.87 |     394 |    387 |     11 |       |       |          |         |
|*  1 |  VIEW                      |                 |      1 |  99720 |     10 |00:00:00.87 |     394 |    387 |     11 |       |       |          |         |
|*  2 |   WINDOW SORT PUSHED RANK  |                 |      1 |  99720 |     20 |00:00:00.87 |     394 |    387 |     11 |  2048 |  2048 | 2048  (0)|         |
|*  3 |    VIEW                    |                 |      1 |  99720 |     20 |00:00:00.87 |     394 |    387 |     11 |       |       |          |         |
|*  4 |     WINDOW SORT PUSHED RANK|                 |      1 |  99720 |     30 |00:00:00.87 |     394 |    387 |     11 | 92160 | 92160 | 1123K (2)|    1024 |
|   5 |      TABLE ACCESS FULL     | HABR_TEST_TABLE |      1 |  99720 |    100K|00:00:00.09 |     381 |    376 |      0 |       |       |          |         |
---------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("M_O"=1)
   2 - filter(DENSE_RANK() OVER ( PARTITION BY "C"."CLIENT_ID" ORDER BY INTERNAL_FUNCTION("C"."OPER_ID") DESC )<=1)
   3 - filter("C"."M_A"=1)
   4 - filter(DENSE_RANK() OVER ( PARTITION BY "T"."CLIENT_ID" ORDER BY INTERNAL_FUNCTION("T"."AMOUNT") DESC )<=1)


SQL> -- 2 max + order by:
SQL> select/*+ findme 3 */ * from
  2  (
  3      select c.*
  4          , max(c.oper_id) over (partition by c.client_id) as m_o/*max_operation*/
  5      from
  6          (
  7          select t.*
  8          , max(t.amount) over (partition by t.client_id) as m_a/*max_amount*/
  9          from habr_test_table t
 10          order by t.client_id
 11          ) c
 12      where c.m_a = c.amount
 13  ) where m_o = oper_id;

Plan hash value: 673711813

----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |      1 |        |     10 |00:00:00.93 |     394 |   1284 |    908 |       |       |          |         |
|*  1 |  VIEW                 |                 |      1 |  99720 |     10 |00:00:00.93 |     394 |   1284 |    908 |       |       |          |         |
|   2 |   WINDOW BUFFER       |                 |      1 |  99720 |     20 |00:00:00.93 |     394 |   1284 |    908 |  2048 |  2048 | 2048  (0)|         |
|*  3 |    VIEW               |                 |      1 |  99720 |     20 |00:00:00.93 |     394 |   1284 |    908 |       |       |          |         |
|   4 |     WINDOW SORT       |                 |      1 |  99720 |    100K|00:00:00.88 |     394 |   1284 |    908 |  3519K|   815K| 1123K (2)|    4096 |
|   5 |      TABLE ACCESS FULL| HABR_TEST_TABLE |      1 |  99720 |    100K|00:00:00.07 |     381 |    376 |      0 |       |       |          |         |
----------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("M_O"="OPER_ID")
   3 - filter("C"."M_A"="C"."AMOUNT")

SQL> -- 1 row_number:
SQL> select/*+ findme 4 */ * from
  2  (
  3      select t.*
  4      , row_number() over (partition by t.client_id order by t.amount desc, t.oper_id desc) as rn
  5      from habr_test_table t
  6  ) where rn = 1;

Plan hash value: 19323224

-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |      1 |        |     10 |00:00:00.76 |     394 |    387 |     11 |       |       |          |         |
|*  1 |  VIEW                    |                 |      1 |  99720 |     10 |00:00:00.76 |     394 |    387 |     11 |       |       |          |         |
|*  2 |   WINDOW SORT PUSHED RANK|                 |      1 |  99720 |     20 |00:00:00.76 |     394 |    387 |     11 | 92160 | 92160 | 1123K (2)|    1024 |
|   3 |    TABLE ACCESS FULL     | HABR_TEST_TABLE |      1 |  99720 |    100K|00:00:00.07 |     381 |    376 |      0 |       |       |          |         |
-------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."CLIENT_ID" ORDER BY INTERNAL_FUNCTION("T"."AMOUNT") DESC ,INTERNAL_FUNCTION("T"."OPER_ID") DESC
              )<=1)

xtender Apr 25 2014 at 11:53

Перечитал свой 4-й пункт и понял, что неясно выразился: на самом деле нет никакой 10-кратной разницы и на исходном объеме данных, т.е на 20 тысячах. Наоборот, разница будет проявляться с увеличением кол-ва данных. Проверьте на разных данных: на паре миллионов, на разном кол-ве и распределении записей по клиентам, и тд…

F-r-IEND Apr 25 2014 at 12:36

Благодарю за такой развернутый ответ, за потраченное время и за справедливую критику. Вот ведь как бывает, думаешь что сделал конфетку, а на деле… Впредь буду стараться следовать вашим советам, спасибо вам большое.

kirillzorin Apr 26 2014 at 18:28

… И четко помня, что «qualify» в оракле нет, я написал на бумажке что то на подобии:

select t.* from some_table t
where amount = max(t.oper_id) over (partition by t.client_id)

Так возможно, что неудача на собеседовании была как раз из-за этого запроса, а не из-за ответа про особенности «max» и «dense_rank»?

F-r-IEND Apr 27 2014 at 23:02

Согласен. Так и было, но мой пост не о неудаче на интервью, а о моих наблюдениях. Все это предыстория, которой я хотел рассказать о том, как я заметил изменения в плане запроса с использованием order by и без него.

Show the best of all time