|
Том Кайт
Том Кайт: о запросах первых n строк и запросах с разбивкой на страницы
(On Top-n and Pagination Queries, By Tom Kyte)
Источник: журнал Oracle Magazine, January-February 2007
(http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html).
Наш эксперт получает больше результатов, используя функции ROW_NUMBER, RANK и DENSE_RANK.
В недавнем выпуске колонки Ask Tom
(September/October 2006
– русский перевод: "Том Кайт: о столбце ROWNUM и ограничении размеров результирующих наборов" публикуется
в этом же выпуске журнала) я писал об использовании столбца ROWNUM для выполнения запросов первых n строк упорядоченного результирующего набора (top-n queries) и запросов с разбивкой на страницы (pagination queries), которые позволяют извлекать из результирующего набора строки в диапазоне от n от m.
В том выпуске колонки я показывал, как использовать столбец ROWNUM для извлечения первых 10 строк "вашего запроса":
select *
from
(ваш_запрос)
where rownum <= 10;
А также, как использовать столбец ROWNUM для извлечения строк "вашего запроса" в диапазоне от n до m:
select *
from
( select rownum rnum, a.*
from (ваш_запрос) a
where rownum <= :M )
where rnum >= :N;
Как всегда, в сервере Oracle Database есть несколько способов выполнения чего-либо, и эти запросы первых n строк и запросы с разбивкой на страницы – не исключение. В этом выпуске колонки я рассмотрю альтернативные способы использования этих функциональных возможностей с помощью аналитических функций, а также представлю еще одну разновидность запросов первых n строк: извлечение первых n строк с группированием по некоторому набору атрибутов, такому как извлечение набора строк таблицы EMP, представляющих троих самых высокооплачиваемых служащих каждого отдела (группирование по столбцу DEPTNO).
Запросы первых N строк
Другой способ написания запроса первых n строк – использование аналитической функции ROW_NUMBER (это похоже на ограничение числа строк, возвращаемых запросом, с помощью предложения LIMIT в СУБД MySQL или оператора "set rowcount" в СУБД SQL Server). Эта функция позволяет получить такие же результаты, которые позволяет получить псевдостолбец ROWNUM, но она более гибкая и имеет больше возможностей. В частности, я буду использовать ее в следующем разделе для извлечения первых n строк с группированием по некоторому набору атрибутов.
Давайте посмотрим, что функция ROW_NUMBER может делать. В следующем примере запрос использует эту функцию для присвоения возрастающих номеров строкам таблицы EMP после ее сортировки в убывающем порядке по столбцу SAL (оклад):
SQL> select ename, sal,
2 row_number()
3 over (order by sal desc) rn
4 from emp
5 order by sal desc
6 /
ENAME SAL RN
----- ---- --
KING 5000 1
FORD 3000 2
SCOTT 3000 3
JONES 2975 4
.
.
.
JAMES 950 13
SMITH 800 14
14 rows selected.
Итак, функция ROW_NUMBER позволяет мне легко присваивать эти возрастающие номера после сортировки таблицы, но, на первый взгляд, кажется, что она не поддерживает извлечение только первых n строк:
SQL> select ename, sal,
2 row_number()
3 over (order by sal desc) rn
4 from emp
5 where
6 row_number()
7 over (order by sal desc) <= 3
8 order by sal desc
9 /
row_number()
*
ERROR at line 6:
ORA-30483: window functions are not allowed here
Здесь проблема заключается в том, что аналитические функции вычисляются после обработки предложения WHERE, поэтому их нельзя использовать в предложении WHERE. На самом деле, это никакая не проблема, я же могу использовать вложенные представления – применить предикат после присвоения номеров. Например:
SQL> select *
2 from (
3 select ename, sal,
4 row_number()
5 over (order by sal desc) rn
6 from emp
7 )
8 where rn <= 3
9 order by sal desc
10 /
ENAME SAL RN
----- ---- --
KING 5000 1
SCOTT 3000 2
FORD 3000 3
3 rows selected.
Таким образом, это показывает, как с помощью функции ROW_NUMBER выполнять запросы первых n строк, а также указывает общую проблему запросов первых n строк. Посмотрите на результат, в нем две строки со значением 3000. Что если в таблице EMP оклад 3000 имеют не два человека, а три? Результат этого запроса – неоднозначный, поскольку я могу извлекать три записи, но они могут оказаться случайными. Например:
SQL> update emp
2 set sal = 3000
3 where ename = 'SMITH';
1 row updated.
SQL> select *
2 from (
3 select ename, sal,
4 row_number()
5 over (order by sal desc) rn
6 from emp
7 )
8 where rn <= 3
9 order by sal desc
10 /
ENAME SAL RN
----- ---- --
KING 5000 1
SMITH 3000 2
SCOTT 3000 3
3 rows selected.
Как видите, строка служащего FORD исчезла из результирующего набора. Если же вы сделаете это обновление и запросите ваши данные, эта строка может не исчезнуть, а исчезнет строка служащего SCOTT или SMITH – порядок исчезновения строк будет произвольным. Это – общая проблема запросов первых n строк: если вы сортируете по какому-либо неуникальному столбцу, возвращаемые наборы первых n строк могут быть разными, даже при одинаковых входных данных. Например:
SQL> create table t
2 ( x number,
3 y number
4 );
Table created.
SQL> insert into t
2 values ( 1, 100 );
SQL> insert into t
2 values ( 1, 200 );
SQL> insert into t
2 values ( 1, 300 );
SQL> insert into t
2 values ( 1, 400 );
SQL> select *
2 from
3 (select x,y,
4 row_number()
5 over (order by x) rn
6 from t)
7 where rn <= 3;
X Y RN
--- --- --
1 100 1
1 200 2
1 300 3
3 rows selected.
Теперь, попробуем повторно вставить эти же данные, но в другом порядке:
SQL> truncate table t;
Table truncated.
SQL> insert into t
2 values ( 1, 400 );
SQL> insert into t
2 values ( 1, 200 );
SQL> insert into t
2 values ( 1, 300 );
SQL> insert into t
2 values ( 1, 100 );
SQL> select *
2 from
3 (select x,y,
4 row_number()
5 over (order by x) rn
6 from t)
7 where rn <= 3;
X Y RN
--- --- --
1 400 1
1 200 2
1 300 3
3 rows selected.
Неожиданно я получаю совсем другой ответ. Это то, что вы должны осознавать при выполнении запросов первых n строк и сортировке по неуникальному столбцу.
Имейте в виду, для устранения неоднозначности можно использовать другие аналитические функции. Но при их использовании можно получить более n строк. По моему мнению, когда я использую сортировку по неуникальному столбцу, я хочу получить не только первые n произвольных строк, а все релевантные строки. С этой целью я могу использовать аналитические функции RANK – вычисление ранга и DENSE_RANK – вычисление плотного ранга. Посмотрим, что они делают:
SQL> select ename,sal,
2 row_number()
3 over (order by sal desc)rn,
4 rank()
5 over (order by sal desc)rnk,
6 dense_rank()
7 over (order by sal desc)drnk
8 from emp
9 order by sal desc
10 /
ENAME SAL RN RNK DRNK
----- ---- -- --- ----
KING 5000 1 1 1
FORD 3000 2 2 2
SCOTT 3000 3 2 2
JONES 2975 4 4 3
BLAKE 2850 5 5 4
CLARK 2450 6 6 5
.
.
.
14 rows selected.
Здесь главное отметить следующее:
- функция ROW_NUMBER присваивает строкам результирующего набора непрерывные, уникальные номера от 1 до N;
- функция RANK не присваивает уникальные номера – служащие FORD и SCOTT делят второе место. Она не присваивает и непрерывные номера – никакой записи не присвоено значение 3, поскольку два человека делят второе место и, в соответствии с определением функции RANK, никто не может занять третье место;
- функция DENSE_RANK, как и функция RANK, не присваивает уникальные номера, но она присваивает непрерывные номера. Хотя две записи делят второе место, имеется также и третье место.
Вы можете использовать функции RANK and DENSE_RANK так же как вы использовали функцию
ROW_NUMBER для ограничения числа возвращаемых строк, но, очевидно, вы получите несколько другие результаты. Например:
SQL> select *
2 from (
3 select ename,sal,
4 dense_rank()
5 over (order by sal desc)drnk
6 from emp
7 ) where drnk <= 3
8 order by sal desc
9 /
ENAME SAL DRNK
----- ---- ----
KING 5000 1
SCOTT 3000 2
FORD 3000 2
JONES 2975 3
4 rows selected.
Этот запрос возвращает "набор строк, представляющих служащих, которые имеют три самых высоких оклада"; это похоже на искомый результат. Если использовать функцию ROW_NUMBER, результат может быть произвольным, потому что столбец SAL – неуникальный столбец (результат зависит от порядка вставки строк). Однако при использовании функции DENSE_RANK я получаю не точно три строки, а повторяемый (детерминированный) результирующий набор. И я полагаю, что я нашел то, что на самом деле хотел найти конечный пользователь – людей с тремя самыми высокими окладами.
Запросы первых n строк с группированием
Аналитические функции более предпочтительно (по сравнению с псевдостолбцом ROWNUM) использовать при необходимости обработки более сложных запросов. Например, предположим, вы хотите в таблице EMP найти набор строк, представляющих n самых высокооплачиваемых служащих каждого отдела (группирование по столбцу DEPTNO). Для этого мне нужно отсортировать результирующий набор по столбцу DEPTNO, затем отсортировать получившиеся группы по столбцу SAL в убывающем порядке, а затем каждой строке присвоить результаты аналитических функций. К счастью, аналитика разработана для выполнения именно такой работы. Например:
SQL> break on deptno skip 1
SQL> select deptno, ename,sal,
2 row_number()
3 over (partition by deptno
4 order by sal desc)rn,
5 rank()
6 over (partition by deptno
7 order by sal desc)rnk,
8 dense_rank()
9 over (partition by deptno
10 order by sal desc)drnk
11 from emp
12 order by deptno, sal desc
13 /
DEPTNO ENAME SAL RN RNK DRNK
-------- ----- ---- -- --- ----
10 KING 5000 1 1 1
CLARK 2450 2 2 2
MILLER 1300 3 3 3
20 SCOTT 3000 1 1 1
FORD 3000 2 1 1
JONES 2975 3 3 2
ADAMS 1100 4 4 3
SMITH 800 5 5 4
30 BLAKE 2850 1 1 1
ALLEN 1600 2 2 2
TURNER 1500 3 3 3
MARTIN 1250 4 4 4
WARD 1250 5 4 4
JAMES 950 6 6 5
14 rows selected.
Здесь я при вызове аналитических функций использую предложение секционирования PARTITION. Оно виртуально разбивает мой результирующий набор на некоторое количество групп, а затем по очереди применяет аналитические функции к каждой группе.
Это позволяет мне присвоить возрастающие номера, используя, например, функцию ROW_NUMBER, строкам каждого отдела (со сбросом номера строки в 1 при переходе от группы к группе). Как видите, теперь очень легко получить набор строк, представляющих трех самых высокооплачиваемых служащих каждого отдела; для этого используется вложенное представление и применяется предикат WHERE RN <= 3 или RNK <= 3 или DRNK <= 3 – в зависимости от того, какой метод нумерации возвращает ответ, который вы считаете наиболее подходящим. Например:
SQL> break on deptno skip 1
SQL> select *
2 from (
3 select deptno, ename, sal,
4 dense_rank()
5 over (partition by deptno
6 order by sal desc)drnk
7 from emp
8 ) where drnk <= 3
9 order by deptno, sal desc
10 /
DEPTNO ENAME SAL DRNK
------ ----- ---- ----
10 KING 5000 1
CLARK 2450 2
MILLER 1300 3
20 SCOTT 3000 1
FORD 3000 1
JONES 2975 2
ADAMS 1100 3
30 BLAKE 2850 1
ALLEN 1600 2
TURNER 1500 3
10 rows selected.
Разбивка на страницы с получением строк в диапазоне от N до M
Последнее, что я хочу показать, это использование аналитики для разбивки результатов запросов на страницы. Такая разбивка обычно применяется в веб-приложениях: пользователь вводит набор критериев, по которым выполняется запрос, а затем этот пользователь может просматривать результирующий набор, нажимая на кнопки Previous (предыдущая) и Next (следующая). Для этого приложение должно уметь извлекать из запроса определенный набор строк. В уже упомянутом выпуске колонки "О столбце ROWNUM и ограничении размеров результирующих наборов" а также в представленном выше разделе "Запросы первых N строк" я уже показывал, как выполнять такую разбивку, но такой подход требует два уровня вложенных представлений и его можно считать чересчур громоздким. Синтаксически гораздо проще написать следующий запрос:
select *
from (
select /*+ first_rows(25) */
ваши_столбцы,
row_number()
over (order by что-то уникальное)rn
from ваши_таблицы)
where rn between :n and :m
order by rn;
Например, предположим, вы хотите разбить на страницы результаты запроса к представлению ALL_OBJECTS, по 25 строк на странице, и нужно показать страницу 5:
SQL> variable n number
SQL> variable m number
SQL> exec :n := 101; :m := 125
PL/SQL procedure successfully completed.
SQL> select *
2 from (
3 select /*+ first_rows(25) */
4 object_id,object_name,
5 row_number() over
6 (order by object_id) rn
7 from all_objects)
8 where rn between :n and :m
9 order by rn;
OBJECT_ID OBJECT_NAME RN
--------- ----------- ---
102 I_SEQ1 101
103 I_OBJAUTH1 102
.
.
.
124 I_ACCESS1 123
125 I_TRIGGER1 124
126 I_TRIGGER2 125
25 rows selected.
В подсказке FIRST_ROWS я указываю число 25 – размер моей страницы, я хочу как можно быстрее получить мою первую страницу. После сортировки по столбцу OBJECT_ID я присваиваю номер ROW_NUMBER каждой строке получившегося результирующего набора. Затем для извлечения нужных мне строк (в данном случае от 101 до 125) я использую простое предложение BETWEEN.
Чтобы при каждом выполнении такого запроса этот номер ROW_NUMBER присваивался детерминировано, нужно сортировать по уникальным ключам. В противном случае, вы столкнетесь с проблемой, похожей на ту, которую я указал при рассмотрении запросов первых n строк, представляющих самых высокооплачиваемых служащих. Более подробно о детерминированных запросах см. oracle.com/technology/oramag/oracle/06-sep/o56asktom.html. Кроме того, полная информация об использовании аналитических функций содержится в download.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm#i1007779 (глава 21 Руководства по хранилищам данных). По моему мнению, аналитические функции – самая крутая вещь, появившаяся в языке SQL после ключевого слова SELECT.
Выполняет ли предложение GROUP BY сортировку?
Гарантирует ли предложение GROUP BY, что результирующий набор запроса будет отсортирован по столбцам предложения GROUP BY, даже если в запросе нет предложения ORDER BY?
До тех пор пока в запросе не появится предложение ORDER BY, нельзя предполагать какой-либо порядок возвращаемых строк. Без предложения ORDER BY данные могут возвращаться в любом порядке, который заблагорассудится выбрать серверу баз данных. Это всегда было так и всегда будет так.
На самом деле, в сервере Oracle Database 10g Release 2 можно обнаружить, что предложение GROUP BY возвращает данные в более произвольном порядке, чем это было ранее:
SQL> set autotrace on explain
SQL> select job, count(*)
2 from emp
3 group by job
4 /
JOB COUNT(*)
--------- --------
CLERK 4
SALESMAN 4
PRESIDENT 1
MANAGER 3
ANALYST 2
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH GROUP BY | |
| 2 | TABLE ACCESS FULL | EMP |
-----------------------------------
В этом плане выполнения появился новый шаг HASH GROUP BY, который в общем более быстрый и эффективный шага выполнения операции GROUP BY, он точно не сортирует данные.
Если в вашей системе получаются другие результаты, это не должно вас вводить в заблуждение. Если в вашей системе получились отсортированные результаты, это не имеет никакого значения – потребуется выполнить только один контрпример, и все встанет на свои места. Только использование предложения ORDER BY позволяет сделать предположения о порядке сортировки данных. Многие вещи – от параллельных запросов до секционирования, до индексов по инвертированным ключам, до хеш-секционированных индексов, до шагов HASH GROUP BY, до наличия (или отсутствия) индексов со структурой B-дерева – могут и будут возвращать данные не в отсортированном порядке.
Если вам нужны отсортированные данные, вы должны использовать предложение ORDER BY; нет никакой возможности обойтись без него. Более подробное обсуждение предложения ORDER BY см. на сайте asktom.oracle.com/tkyte/OrderBy.html.
Ведущий данной колонки Том Кайт (Tom Kyte, thomas.kyte@oracle.com) работает в корпорации Oracle с 1993 года. Кайт – вице-президент Oracle, возглавляющий группу Oracle Public Sector; он автор таких книг, как "Expert Oracle: 9i and 10g Programming Techniques and Solutions" (Apress, 2005), "Effective Oracle by Design" (Oracle Press, 2003), а также ряда других.
| 
