|
Том Кайт
Том Кайт: о столбце ROWNUM
и ограничении размеров результирующих наборов
(On ROWNUM and Limiting Results,
by Tom Kyte)
Источник: журнал Oracle Magazine, September – October 2006
(
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html).
Наш эксперт объясняет как обрабатывается столбец ROWNUM и как заставить этот механизм работать на вас.
Этот выпуск колонки Ask Tom немного отличается от обычных выпусков. Я получаю много вопросов о том, как в сервере Oracle Database выполнять запросы первых N записей упорядоченного результирующего набора (top-N queries) и запросы с разбивкой на страницы (pagination queries), поэтому я решил предоставить отрывок из книги Effective Oracle by Design (Oracle Press, 2003) в надежде, что в этом выпуске вы получите ответы на многие эти вопросы. Для подгонки к объему и формату колонки оригинальный текст был модифицирован.
Ограничение размеров результирующих наборов
В сервере Oracle Database столбец ROWNUM – магический столбец, который многим причиняет неприятности. Однако, когда вы изучите, что он собой представляет, как он обрабатывается, он может оказаться очень полезным. В основном я использую его в двух случаях:
- для запроса первых N записей. Это похоже на использование предложения LIMIT, доступного в некоторых других СУБД;
- для разбивки результатов запросов на страницы, обычно в таких средах, в которых не сохраняется состояние, например, в Вебе. Я использую эту технику в веб-сайте asktom.oracle.com.
Я рассмотрю обе эти техники после обзора механизма обработки столбца ROWNUM.
Как столбец ROWNUM обрабатывается?
На самом деле столбец ROWNUM – не реально существующий столбец, а псевдостолбец, который доступен в запросах. Значениям столбца ROWNUM присваиваются номера 1, 2, 3, 4, ... N, где N – число строк результирующего набора запроса, в котором используется столбец ROWNUM. Значение ROWNUM для какой-то строки не является постоянным (это обычное заблуждение). Строка в таблице не имеет номера; вы не сможете выполнить запрос к таблице для извлечения, например, пятой строки – в таблице нет такой строки.
Многие также путаются с моментом времени фактического присваивания значений столбца ROWNUM. Значение ROWNUM присваивается строке после завершения фазы обработки предикатов запроса, но перед выполнением каких-либо сортировок и агрегирований. Кроме того, значение ROWNUM увеличивается только после его присваивания, вот почему следующий запрос никогда не возвратит какие-то строки:
select *
from t
where ROWNUM > 1;
Значение предиката ROWNUM > 1 для первой строки не является истинным, поэтому значение ROWNUM не увеличится до 2. Следовательно, никогда не будет никаких значений ROWNUM, превышающих 1. Рассмотрим запрос со следующей структурой:
select ..., ROWNUM
from t
where <предложение where>
group by <столбцы>
having <предложение having>
order by <столбцы>;
Полагаем, этот запрос выполняется в следующем порядке:
Сначала обрабатываются предложения FROM и WHERE.
Присваиваются значения ROWNUM, они увеличиваются для каждой строки результирующего множества, полученного при обработке предложений FROM и WHERE.
Применяется предложение SELECT.
Применяется предложение GROUP BY.
Применяется предложение HAVING.
Применяется предложение ORDER BY.
Вот почему запрос следующего вида почти всегда будет выполняться неправильно:
N записей. На самом же деле запрос возвратит пять случайных записей (первые пять, которые попались запросу), отсортированных по столбцу sal (оклад). Процедурный псевдокод этого запроса выглядит так:
ROWNUM = 1
for x in
( select * from emp )
loop
exit when NOT(ROWNUM <= 5)
OUTPUT record to temp
ROWNUM = ROWNUM+1
end loop
SORT TEMP
Здесь извлекаются первые пять строк, затем они сортируются. Запросы с предложениями WHERE ROWNUM = 5 или WHERE ROWNUM > 5 вообще не имеют какого-либо смысла. Значение ROWNUM присваивается строке во время вычисления предикатов и увеличивается только после того, как для строки завершится обработка предложения WHERE.
Вот корректный вариант этого запроса:
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
В этом запросе таблица EMP сортируется в убывающем порядке по столбцу sal, а затем возвращаются первые пять записей. Как вы увидите в следующем ниже обсуждении, на самом деле сервер Oracle Database не сортирует весь результирующий набор, он гораздо умнее, но концептуально это так и есть.
Обработка запросов первых N записей с использованием столбца ROWNUM
В такого типа запросах нас обычно интересуют какие-то сложные запросы с сортировками, которые затем извлекают только первые N записей. Для этого типа запросов со столбцами ROWNUM предусмотрен механизм оптимизации обработки первых N записей (top-N optimization). Для того чтобы избежать массовых сортировок больших наборов данных, можно использовать столбец ROWNUM. Сначала я рассмотрю этот механизм концептуально, а потом представлю пример.
Предположим, у нас есть запрос следующего вида:
select ...
from ...
where ...
order by столбцы;
Предположим, этот запрос возвращает много данных: тысячи строк, сотни тысяч или даже больше. Однако нас интересуют только первые N строк, скажем 10 или 100. Для получения этих строк имеется два подхода:
- клиентское приложение выполняет этот запрос и выбирает только первые N строк;
- этот запрос используется как вложенное представление, а для ограничения размера результирующего набора используется столбец ROWNUM, т.е. мы имеем запрос вида SELECT * FROM (здесь_ваш_запрос) WHERE ROWNUM <= N.
Второй подход намного превосходит первый по двум причинам. Наименее важная причина – клиент выполняет меньший объем работы, поскольку сервер ограничивает размер результирующего набора. Более важная причина – специальная обработка данных в сервере позволяет вам получить только первые N строк. Использование таких запросов означает, что вы предоставляете серверу дополнительную информацию. Вы говорите ему: "Мне нужно получить только N строк; мне совсем не нужны остальные строки". Это не кажется чрезвычайно важным, если вы только не подумаете о сортировке: как она выполняется, что должен сделать сервер. Рассмотрим эти подходы, используя типовой запрос:
select *
from t
order by неиндексированный_столбец;
Теперь, предположим, что таблица T большая – в ней свыше миллиона строк, а каждая строка "толстая", скажем, размером 100 и более байтов. Также предположим, что по столбцу НЕИНДЕКСИРОВАННЫЙ_СТОЛБЕЦ не создано никаких индексов (как и гласит его имя). Кроме того, предположим, нам нужны только первые 10 строк. Сервер Oracle Database будет делать следующее:
Выполняет полный просмотр таблицы T.
Выполняет сортировку этой таблицы по столбцу НЕИНДЕКСИРОВАННЫЙ_СТОЛБЕЦ. Это – полная сортировка.
Выходит, вероятно, за пределы области сортировки в оперативной памяти и вынужден использовать временные экстенты на диске.
Для получения первых 10 записей сервер сливает временные экстенты.
Очищает (освобождает) временные экстенты, поскольку они уже не нужны.
Налицо большой объем ввода-вывода. Сервер Oracle Database копирует, вероятно, всю эту таблицу во временное табличное пространство TEMP и работает с ней там, и это все для того, чтобы получить только первые 10 записей.
Теперь, рассмотрим, что сервер Oracle Database может концептуально делать, обрабатывая запрос первых N требуемых строк:
select *
from
(select *
from t
order by неиндексированный_столбец)
where ROWNUM < :N;
В этом случае сервер Oracle Database будет выполнять следующие шаги:
Выполняет полный просмотр таблицы T, как и раньше (этого шага нельзя избежать).
В массиве, состоящем из :N элементов (в это время он, вероятно, уже находится в оперативной памяти), выполняет сортировку только :N строк.
Этот массив заполняется строками в отсортированном порядке. Когда выбирается строка N +1, она сравнивается с последней строкой массива. Если она попадает в интервал массива N +1, она отбрасывается. В противном случае, она добавляется в массив вместо одной из существующих в нем строк, массив сортируется. В области сортировки содержится максимум N строк, не нужно сортировать миллион строк, а только N.
Эти на вид несущественные частности использования концепции массивов и сортировки только N строк могут привести к существенному выигрышу с точки зрения производительности и использования ресурсов. Для сортировки не миллиона строк, а 10, требуется намного меньше оперативной памяти (не говоря уже об использовании пространства TEMP).
Используя следующую таблицу T, можно увидеть, что хотя оба подхода позволяют получить одинаковые результаты, они используют совершенно разный объем ресурсов:
create table t
as
select dbms_random.value(1,1000000)
id,
rpad('*',40,'*' ) data
from dual
connect by level <= 100000;
begin
dbms_stats.gather_table_stats
( user, 'T');
end;
/
Теперь включаем трассировку:
exec
dbms_monitor.session_trace_enable
(waits=>true);
Затем выполним наш запрос первых N строк, используя столбец ROWNUM:
select *
from
(select *
from t
order by id)
where rownum <= 10;
А в конце выполним "самодельный" запрос, который выбирает только первые 10 записей:
declare
cursor c is
select *
from t
order by id;
l_rec c%rowtype;
begin
open c;
for i in 1 .. 10
loop
fetch c into l_rec;
exit when c%notfound;
end loop;
close c;
end;
/
После выполнения этих запросов для форматирования созданных трассировочных файлов можно использовать утилиту TKPROF, а затем проверить, что получилось. Сначала рассмотрим первый запрос, см. листинг 1.
|
ЛИСТИНГ 1: запрос первых N строк с использованием столбца ROWNUM. |
select *
from
(select *
from t
order by id)
where rownum <= 10
call count cpu elapsed disk query current rows
-------- -------- ------- ------- ------- -------- -------- ------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.04 0 949 0 10
-------- -------- ------- ------- ------- -------- -------- ------
total 4 0.04 0.04 0 949 0 10
Rows Row Source Operation
----------------- ---------------------------------------------------
10 COUNT STOPKEY (cr=949 pr=0 pw=0 time=46997 us)
10 VIEW (cr=949 pr=0 pw=0 time=46979 us)
10 SORT ORDER BY STOPKEY (cr=949 pr=0 pw=0 time=46961 us)
100000 TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400066 us) |
Этот запрос читает всю таблицу (он должен делать это), но при этом используется шаг SORT ORDER BY STOPKEY, что позволяет ограничить использование временного пространства только 10 строками. Обратите внимание на последнюю строку столбца Row Source Operation (операция над источником строк) – она показывает, что запрос выполнил в сумме 949 логических чтений (consistent reads) (cr=949), не было никаких физических чтений (physical reads) или записей (physical writes) (pr=0 и pw=0), для этого потребовалось (статистика time – время) 400066 микросекунд (0.04 секунд). Сравните это с выполнением самодельного запроса, см. листинг 2.
|
ЛИСТИНГ 2: самодельный запрос без использования столбца ROWNUM. |
SELECT * FROM T ORDER BY ID
call count cpu elapsed disk query current rows
-------- -------- ------- ------- ------- -------- -------- ------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 10 0.35 0.40 155 949 6 10
-------- -------- ------- ------- ------- -------- -------- ------
total 13 0.36 0.40 155 949 6 10
Rows Row Source Operation
----------------- ---------------------------------------------------
10 SORT ORDER BY (cr=949 pr=155 pw=891 time=401610 us)
100000 TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400060 us)
Elapsed times include waiting for the following events:
Event waited on Times
------------------------------ ------------
direct path write temp 33
direct path read temp 5 |
Как видите, результаты очень отличаются. Особенно заметно увеличились общее затраченное время (elapsed) и время центрального процессора (cpu), последние строки столбца Row Source Operation позволяют понять, почему это произошло. Нужно было выполнить сортировку на диске – об этом говорит появление операций физической записи pw=891. Этот запрос для сортировки 100 000 записей (вместо 10, которые в конечном счете нас и интересуют) выполнял некоторое количество операций прямого физического чтения и записи, что существенно повлияло на время выполнения и использование ресурсов.
Разбивка на страницы с использованием столбца ROWNUM
Мое излюбленное использование столбца ROWNUM – разбивка результатов запросов на страницы. В этом случае я использую столбец ROWNUM для извлечения из результирующего набора строк в диапазоне от N до M. Общий вид запроса:
select *
from ( select /*+ FIRST_ROWS(n) */
a.*, ROWNUM rnum
from (здесь ваш запрос
с предложением order by ) a
where ROWNUM <=
:MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
- подсказка FIRST_ROWS(N) говорит оптимизатору: "Эй, меня интересует получение первых строк, и я хочу выбрать первые N строк как можно быстрее";
- в переменной связывания :MAX_ROW_TO_FETCH устанавливается номер последней строки результирующего набора, которую вы хотите выбрать – если вам нужны строки 50-60, то нужно в этой переменной установить 60;
- в переменной связывания :MIN_ROW_TO_FETCH устанавливается номер первой строки результирующего набора, которую вы хотите выбрать – если вам нужны строки 50-60, то нужно в этой переменной установить 50.
Идея этого плана действий заключается в том, что конечный пользователь, работающий с веб-браузером, запускает выполнение поиска и ждет получения результатов. Крайне важно как можно быстрее возвратить первую результирующую страницу (а также вторую и т.д). Если вы внимательно посмотрите на этот запрос, вы обнаружите, что в него включен запрос первых N строк (извлечь первые :MAX_ROW_TO_FETCH строки), поэтому в нем используются все преимущества механизма оптимизации обработки первых N строк, только что описанного мною. Более того, он по сети возвращает клиенту только конкретные нужные строки – удаляет из результирующего набора первые ненужные строки.
SQL> create table t
2 as
3 select mod(level,5) id,
trunc(dbms_random.value(1,100)) data
4 from dual
5 connect by level <= 10000;
Table created.
Затем после сортировки по столбцу ID я выполню два запроса строк 148-150 и 148-151:
SQL> select *
2 from
3 (select a.*, rownum rnum
4 from
5 (select id, data
6 from t
7 order by id) a
8 where rownum <= 150
9 )
10 where rnum >= 148;
ID DATA RNUM
------- ---------- -----------
0 38 148
0 64 149
0 53 150
SQL>
SQL> select *
2 from
3 (select a.*, rownum rnum
4 from
5 (select id, data
6 from t
7 order by id) a
8 where rownum <= 151
9 )
10 where rnum >= 148;
ID DATA RNUM
------- ---------- -----------
0 59 148
0 38 149
0 64 150
0 53 151
Обратите внимание, в первый раз для строки 148 возвращается результат DATA=38, а во второй раз – DATA=59. Оба запроса дают совершенно правильный ответ на ваш запрос: отсортируйте данные по столбцу ID, отбросьте первые 147 строк и возвратите следующие 3 или 4 строки. Оба запроса делают это, но поскольку в столбце ID содержаться очень много дублированных значений, запросы не могут сделать это детерминировано – во время разных выполнений запроса не обеспечивается один и тот же порядок сортировки. Для исправления такого положения дел нужно добавить в предложение ORDER BY что-то уникальное. В данном случае можно просто использовать столбец ROWID:
SQL> select *
2 from
3 (select a.*, rownum rnum
4 from
5 (select id, data
6 from t
7 order by id, rowid) a
8 where rownum <= 150
9 )
10 where rnum >= 148;
ID DATA RNUM
------- ---------- -----------
0 45 148
0 99 149
0 41 150
SQL>
SQL> select *
2 from
3 (select a.*, rownum rnum
4 from
5 (select id, data
6 from t
7 order by id, rowid) a
8 where rownum <= 151
9 )
10 where rnum >= 148;
ID DATA RNUM
------- ---------- -----------
0 45 148
0 99 149
0 41 150
0 45 151
Теперь запрос стал всецело детерминированным. В таблице значения столбца ROWID – уникальны, поэтому при таком упорядочении строки всегда будут иметь одинаковый, детерминированный порядок и запрос с разбивкой на страницы будет детерминировано возвращать строки, как и полагается.
Резюме
Я полагаю, вы и многие другие читатели теперь уважают столбец ROWNUM и понимают следующие аспекты:
- как присваиваются значения столбца ROWNUM, так что вы можете без ошибок писать запросы, в которых он используется;
- как этот столбец влияет на обработку вашего запроса, так что вы можете использовать его в веб-среде для разбивки результатов запросов на страницы;
- как этот столбец может позволить уменьшить работу, выполняемую вашим запросом, так что запросы первых N строк, которые раньше потребляли большой объем временного пространства TEMP, теперь совсем не используют его и возвращают результаты намного быстрее.
Планы на конференции Oracle OpenWorld
Этот выпуск журнала Oracle Magazine выходит перед конференцией Oracle OpenWorld. Я действительно получаю удовольствие от этой недельной конференции – собираюсь встретиться лицом к лицу со многими людьми, с которыми общался только в Вебе. Так что, если вы будете на конференции, я надеюсь увидеть вас на моей лекции. (Если вы еще не догадались, она будет посвящена технологиям баз данных и разработке приложений.) Кроме того, я планирую принять участие во встречах с экспертами по продуктам ("Meet the Experts"); эти события организуются сайтом OTN – Oracle Technology Network. В минувшие годы я участвовал в них, и это всегда был большой форум для обсуждений "один на один" и групповых обсуждений. Проверьте в вашей программе даты и время моей лекции и OTN-событий.
Кроме того, обо всем, что происходит на конференции я на сайте tkyte.blogspot.com буду вести блог (с фотоснимками). И еще, на сайте OTN, будет выложено много демонстрационного контента, включая подкасты, потоковое видео и материалы презентаций.
Ведущий данной колонки Том Кайт (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), а также ряда других.
|
