Здравствуйте, KRA, Вы писали:
KRA>Запрос получается вида (пишу с ораклиным синтаксисом)
KRA>KRA>select rn from (
KRA>select e.*, rownum rn
KRA>from table e
KRA>order by
KRA> e.field1, e.field2
KRA>)
KRA>where id = :id
KRA>
KRA>при наличии индексов по полям сортировки даже при 100к может работать с приемлимой скоростью на соответствующем железе (по крайней мере оракл с rownum-ами неплохо работает).
Правильно уже сказали — тут фактическая ошибка. Oracle высчитывает rownum до выполнения order by. Т.е. в общем случае надо делать так (выносить rownum на уровень выше чем order by):
select t.*, rownum
from
(
select * from table1 order by field1
) t
KRA>После этого, зная номер записи (и расчитав на какой странице она находится), мы можем подчитать только нужную нам страницу запросом вида
KRA>KRA>select e.* from (
KRA>select e.*, rownum rn
KRA>from table e
KRA>order by
KRA> e.field1, e.field2
KRA>)
KRA>where rn between :start_index and :end_index
KRA>
Опять же не совсем правильно. О том как работает rownum и как правильно делать серверный пейджинг в оракле есть хорошая статья
Ask Tom: On ROWNUM and Limiting Results.
KRA>Опять же в оракле такой запрос не приводит к тому, что читаются все данные целиком. Оракл понимает, что такое rownum и как тут можно оптимизировать.
Также здесь я не уверен, что такое использование значения rownum в конструкции between может быть эффективно оптимизировано. Oracle может построить эффективный план для простых предикатов, подобных ... where rownum < :end_index (и использовать в этом месте такую операцию в плане как COUNT STOPKEY). Впрочем, вероятно и это вполне может хорошо работать.
Здравствуйте, MasterZiv, Вы писали:
MZ>Ээээ.. доказательства будут ?
MZ> Оракл понимает, что такое rownum и как тут можно
>> оптимизировать.
MZ>Как можно что-то оптимизировать по rownum, если он генерируется
MZ>в момент создания набора данных ?
MZ>Объясните, я не очень понимаю.
Попробую объяснить как все это происходит в оракле.
Рассмотрим два случая: с наличием индекса для сортировки, и без оного.
Для первного случая создаем простую таблицу:
SQL> create table t1 (i integer primary key);
Для первичного ключа будет создан нужный нам индекс.
Заполняем эту таблицу числами от 1 до 10000000.
SQL> insert into t1 select rownum from dual connect by level <= 10000000;
10000000 rows created.
Собираем статистику по таблице — для оптимизатора:
exec dbms_stats.gather_table_stats(ownname=>user, cascade=>true, tabname=>'t1');
Теперь собственно сам запрос. Для начала рассмотрим только запрос аналогичный конструкции TOP N, т.е. получение первых N записей в порядке сортировки.
Выглядить он будет так (N=100):
select t.i
from
(
select * from t1 order by i
) t
where rownum <= 100
с планом:
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1300 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 100 | 1300 | 3 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN| SYS_C0088663 | 9875K| 47M| 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=100)
План означает следующее. Сортировка была заменена на упорядоченное чтение индекса (INDEX FULL SCAN). Более того строки, последовательно читаемые (в порядке сортировки) из индекса передаются на вход операции COUNT STOPKEY (filter(ROWNUM<=100). Эта операция фактически считает кол-во строк, которые прошли через нее и когда достигается заранее заданное значение (в нашем случае 100), то дальнейшее выполнение запроса останавливается.
Т.е. весь запрос хоть и обращается к таблице с 10000000 записей, но читает лишь первые несколько блоков из индекса и останавливается, когда прочитает ровно то кол-во строк, которые нужно. Но отсюда сразу виден недостаток этого подхода — в нашем случае у нас есть преимущество в том, что мы задали для чтения лишь 100 строк. Если бы нам потребовался скажем миллион строк, то прочитать нужно намного больше, но опять же не всю таблицу/индекс. Но и в реальной жизни это тоже имеет некоторый смысл — в большинстве случаев любой большой список для пользователя будет показываться в виде небольшой части расположенной с начала этого списка, и лишь некоторые из пользователей будут долго и упорно переходить по страницам, чтобы увидеть конец списка.
Превращение вышеприведенного запроса в полноценный запрос с пейджингом ничего интересного не привнесет (выберем скажем страницы по 10 записей и отобразим 9-ую страницу — записи с 91-ой по 100-ую):
select t.* from
(
select t.i, rownum as rn
from
(
select * from t1 order by i
) t
where rownum <= 100
) t
where t.rn > 90
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2600 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 100 | 2600 | 3 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 100 | 1300 | 3 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN| SYS_C0088663 | 9875K| 47M| 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."RN">90)
2 - filter(ROWNUM<=100)
Фактически запрос выполняется абсолютно точно также как и первый — берется первые 100 записей по индексу, но затем эти 100 записей передаются на вход операции VIEW (filter("T"."RN">90)), которая выполняет самую простую фильтрацию по предикату "RN">90 — для того чтобы не возвращать не нужные записи с номерами меньше 90. Никаких преимуществ (например по чтению с диска) по сравнению с первым запросом здесь нет, разве что кроме того, что на клиент отправляется меньшее количество записей (10 вместо 100). Недостатки такие же как и в первом случае.
Теперь можно рассмотреть случай без использования индекса.
SQL> create table t2 (i integer);
Table created.
SQL> insert into t2 select rownum from dual connect by level <= 10000000;
10000000 rows created.
Запрос с первыми 100 записей (TOP 100).
select t.i
from
(
select * from t2 order by i
) t
where rownum <= 100
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1300 | | 53658 (5)| 00:10:44 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 10M| 126M| | 53658 (5)| 00:10:44 |
|* 3 | SORT ORDER BY STOPKEY| | 10M| 126M| 390M| 53658 (5)| 00:10:44 |
| 4 | TABLE ACCESS FULL | T2 | 10M| 126M| | 3607 (8)| 00:00:44 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=100)
3 - filter(ROWNUM<=100)
Здесь уже индекса нет, поэтому приходится читать всю таблицу — TABLE ACCESS FULL T2.
Однако полной сортировки не происходит, ввиду того что Oracle опять распознал использование rownum и понял что ему нужно лишь определенное фиксированное количество записей (100). Происходит это в операции SORT ORDER BY STOPKEY (filter(ROWNUM<=100)). Эта операция выделяет лишь небольшой буфер в памяти (у нас — на 100 элементов) и организует в нем что-то типа очереди с приоритетами. По мере того как будут читаться записи из таблицы, каждая запись будет сравниваться с теми записями, которые уже находятся в буфере, и оцениваться на то, попадает ли она в этот буфер (возможно вытесняя из него другие записи, помещенные в него раньше) или же просто отбрасывает новые строки. В любом случае на выходе этой операции будет ровно 100 нужных нам элементов отсортированных в нужном нам порядке. Это видно по трассировке:
Rows Row Source Operation
------- ---------------------------------------------------
100 COUNT STOPKEY (cr=15208 pr=0 pw=0 time=1992552 us)
100 VIEW (cr=15208 pr=0 pw=0 time=1992547 us)
100 SORT ORDER BY STOPKEY (cr=15208 pr=0 pw=0 time=1992541 us)
10000000 TABLE ACCESS FULL T2 (cr=15208 pr=0 pw=0 time=29 us)
Операция COUNT STOPKEY в данном случае не особо то и нужна, поскольку лишь дублирует функции SORT ORDER BY STOPKEY.
При относительно небольших N такая операция будет значительно менее затратной по ресурсам чем полная сортировка таблицы.
Пейджинг же реализуется здесь точно так же как и в первом случае, со всеми вытекающими недостатками.
select t.* from
(
select t.i, rownum as rn
from
(
select * from t2 order by i
) t
where rownum <= 100
) t
where t.rn > 90
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2600 | | 53658 (5)| 00:10:44 |
|* 1 | VIEW | | 100 | 2600 | | 53658 (5)| 00:10:44 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 10M| 126M| | 53658 (5)| 00:10:44 |
|* 4 | SORT ORDER BY STOPKEY| | 10M| 126M| 390M| 53658 (5)| 00:10:44 |
| 5 | TABLE ACCESS FULL | T2 | 10M| 126M| | 3607 (8)| 00:00:44 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."RN">90)
2 - filter(ROWNUM<=100)
4 - filter(ROWNUM<=100)
Некоторые выводы:
1) Для оптимизации необходимы индексы. Поскольку наибольшая скорость будет именно с использованием индексов вместо сортировок.
2) Если нам нужно получить записи с номерами от :start до :end, то критичным является именно номер последней строки (:end), поскольку запрос в общем случае "выполняется" для всех записей с номерами от 1 до :end. Т.е. другими словами, это будет эффективно лишь если мы показываем страницы записей с небольшими номерами. Чем дальше отображаемая страница находится к концу списка страниц тем медленне будет запрос.
Вот как то так...
Овощ пишет:
> Однако полной сортировки не происходит, ввиду того что Oracle опять
> распознал использование rownum и понял что ему нужно лишь определенное
> фиксированное количество записей (100). Происходит это в операции SORT
> ORDER BY STOPKEY (filter(ROWNUM<=100)). Эта операция выделяет лишь
> небольшой буфер в памяти (у нас — на 100 элементов) и организует в нем
> что-то типа очереди с приоритетами. По мере того как будут читаться
> записи из таблицы, каждая запись будет сравниваться с теми записями,
> которые уже находятся в буфере, и оцениваться на то, попадает ли она в
> этот буфер (возможно вытесняя из него другие записи, помещенные в него
> раньше) или же просто отбрасывает новые строки. В любом случае на выходе
> этой операции будет ровно 100 нужных нам элементов отсортированных в
> нужном нам порядке. Это видно по трассировке:
"Однако полной сортировки не происходит". Ну да, читается вся таблица,
а "полной сортировки не происходит".
Это -- просто специальный вид алгоритма сортировки, с урезанным
результатом, она дешевле полной сортировки, да, но всё равно это --
обработка всей таблицы/набора. Вы же сами написали:
"каждая запись будет сравниваться с теми записями,
которые уже находятся в буфере"
> При относительно небольших N такая операция будет значительно менее
> затратной по ресурсам чем полная сортировка таблицы.
Основные затраты сервера при формировании наборов -- чтение данных,
чтение строк. Да, сортировка особенно больших наборов накладна,
но это -- не то, что нужно минимизировать при работе с БД.
Нужно минимизировать кол-во чтений записей. Если записей
мало, сортировки не оказывают уже какого-то влияния на производительность.
> Некоторые выводы:
> 1) Для оптимизации необходимы индексы. Поскольку наибольшая скорость
> будет именно с использованием индексов вместо сортировок.
Подчеркну мысль: индексы для ORDER BY.
Теперь покритикую: если есть критерии поиска, индексы должны
использоваться для поиска, а не для ORDER BY. Тогда ваша мысль не
работает.
Posted via RSDN NNTP Server 2.1 beta
Овощ пишет:
> Также здесь я не уверен, что такое использование значения rownum в
> конструкции between может быть эффективно оптимизировано. Oracle может
> построить эффективный план для простых предикатов, подобных ... where
> rownum < :end_index (и использовать в этом месте такую операцию в плане
> как COUNT STOPKEY). Впрочем, вероятно и это вполне может хорошо работать.
Дело не в том, какая конструкция, between или нет. Я уверен, что between
уж как-нибудь оракл умеет обрабатывать правильно (он представляется
как начало и конец диапазона, field >= :beg and field <= :end),
а вот дело-то в том, что rownum не известен ДО выполнения запроса,
поэтому на его основе нельзя что-то оптимизировать в принципе.
Вообще rownum -- дебильная штука. Хотя и прикольная иногда.
Posted via RSDN NNTP Server 2.1 beta
