Все потоки
Поиск
Написать публикацию
Обновить

Комментарии 15

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

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

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

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

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

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

Рейтинг скрыт
Я вам верю, не надо меня принимать за упертого. Почему у вас результат с max и dense_rank совпадает я описал в комментарии от 23 апреля 2014 в 18:29. Так как вы сделали сортировку по oper_id (уникальный номер), вы избавились от случая, когда ranking criteria совпадает. Хорошо это или плохо зависит от условий задачи.
Рейтинг скрыт
Все запросы выведут последнюю операцию с максимальным amount.
Рейтинг скрыт
Да, сейчас заметил, вы берете минимальный 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.
Рейтинг скрыт
Странный вы человек, говорите
минимальный ID операции
и тут же приводите пример из моего кода
order by c.oper_id desc)
который говорит о том, что отсортировано по убыванию, и dense_rank поставит единицу там где будет максимальное значение.
Я предоставил все для того, чтобы каждый мог в течении 2 минут получить возможность протестировать собственными руками то, что я описал, при наличии у него под рукой какой нибудь бд оракл. Вы же не потрудились не то чтобы протестировать самому, вы даже не прочитали код, по крайней мере внимательно. А приведенный вами текст из документации ни о чем, вода — мокрая, воздух — воздушный, масло масленое…
Рейтинг скрыт
Отличная статья, есть над чем подумать.
Рейтинг скрыт
Честно говоря, вам еще учиться и учиться. Советую читать почаще оракловые форумы, например 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;
Рейтинг скрыт
На всякий случай прикладываю, полученные планы:
(в более удобном виде: 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)
Рейтинг скрыт
Перечитал свой 4-й пункт и понял, что неясно выразился: на самом деле нет никакой 10-кратной разницы и на исходном объеме данных, т.е на 20 тысячах. Наоборот, разница будет проявляться с увеличением кол-ва данных. Проверьте на разных данных: на паре миллионов, на разном кол-ве и распределении записей по клиентам, и тд…
Рейтинг скрыт
Благодарю за такой развернутый ответ, за потраченное время и за справедливую критику. Вот ведь как бывает, думаешь что сделал конфетку, а на деле… Впредь буду стараться следовать вашим советам, спасибо вам большое.
Рейтинг скрыт
… И четко помня, что «qualify» в оракле нет, я написал на бумажке что то на подобии:

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

Так возможно, что неудача на собеседовании была как раз из-за этого запроса, а не из-за ответа про особенности «max» и «dense_rank»?
Рейтинг скрыт
Согласен. Так и было, но мой пост не о неудаче на интервью, а о моих наблюдениях. Все это предыстория, которой я хотел рассказать о том, как я заметил изменения в плане запроса с использованием order by и без него.
Рейтинг скрыт
Зарегистрируйтесь на Хабре, чтобы оставить комментарий

Публикации

Ваш аккаунт

Разделы

Информация

Услуги

Техническая поддержка
© 2006–2025, Habr