вопрос к тем, кто показывает много данных в таблицах
есть у меня запрос
SELECT Id, ....
FROM ... WHERE .... ORDER BY ...
задача — узнать каким номером будет идти строка с Id=N
по специфике приложения, должна использоваться встроенная БД, я смотрел SQLite, FireBird, MS SQL CE — нигде этого сделать нельзя кроме как полным "выкачиванием" результатов и ручным поиском.
в полноценной версии MS SQL SERVER 2005 вроде есть ROW_NUMBER(), при помощи которой это делается.
а как быть с более простыми базами, есть идеи?
зачем все это надо: чтобы в гриде с сортировкой и фильтрацией восстановить выделенную строчку после смены сортировки. так как записей может быть много, то чтение идет кусками и хотелось бы сразу прочитать нужный кусок.
Здравствуйте, ov, Вы писали:
ov>а как быть с более простыми базами, есть идеи?
Есть идея, как можно в firebird попытаться проэмулировать rownum. Можно попытаться использовать для этого последовательности, приблизительно так
create sequence temp_seq;
select gen_id(temp_seq,1) rownum from ....
Тут правда возникают другие проблемы, которые нужно ещё подумать как решить
1. недопущение использования одной последовательности в паралельных запросах
2. превышение максимального значения в последовательности (я не уверен, как оно устроено в firebird. вероятно, начнётся опять с нуля(?))
Здравствуйте, KRA, Вы писали:
ov>>а как быть с более простыми базами, есть идеи?
KRA>Есть идея, как можно в firebird попытаться проэмулировать rownum. Можно попытаться использовать для этого последовательности, приблизительно так
Поправки для FB.
KRA>create sequence temp_seq;
CREATE GENERATOR TEMP_GEN;
SET GENERATOR TEMP_GEN TO 0;
COMMIT;
KRA>select gen_id(temp_seq,1) rownum from ....
Угу.
KRA>Тут правда возникают другие проблемы, которые нужно ещё подумать как решить KRA>1. недопущение использования одной последовательности в паралельных запросах
Генераторы в FB работают вне контекста транзакций, поэтому при параллельной работе придется делать по генератору на каждый запрос. Потом — удалять. Т.к. пользователь будет владельцем обьекта — все должно пройти нормально (не будет накладок с правами).
KRA>2. превышение максимального значения в последовательности (я не уверен, как оно устроено в firebird. вероятно, начнётся опять с нуля(?))
В FB 2.x генераторы — INT64. Должно хватить по уши. Насчет установки начального значения — смотри выше.
P.S. Более универсальное решение для FB — ххранимая процедура:
I=0;
for SELECT ... FROM ... WHERE ...
INTO ....
do BEGIN
I=I+1;
SUSPEND;
END
Здравствуйте, DarkMaster, Вы писали:
DM>P.S. Более универсальное решение для FB — ххранимая процедура:
Забыл еще про EXECUTE BLOCK;
execute block
returns (AROWNUM INT64, AVALUE varchar(10))
as
begin
AROWNUM=0;
for select VALUE
from MYTABLE
where VALUE LIKE 'A%'
order by VALUEDATE
into :AVALUE
do begin
AROWNUM=AROWNUM+1;
suspend;
end
end
> зачем все это надо: чтобы в гриде с сортировкой и фильтрацией > восстановить выделенную строчку после смены сортировки. так как записей > может быть много, то чтение идет кусками и хотелось бы сразу прочитать > нужный кусок.
Какая разница, какой по порядку идёт нужная вам строка ?
У каждой строки есть первичный ключ (должен быть, иначе
нужно делать редизайн БД), вы до смены сортировки запоминаете значение
ключа, делаете сортировку, и находите нужную запись по запомненному
значению ключа.
>вы до смены сортировки запоминаете значение ключа, делаете сортировку, и находите нужную запись по запомненному >значению ключа.
все бы хорошо, да надо читать блоками. для чтения целиком проблем нет — все так и делается и прекрасно работает.
>> может быть много, то чтение идет кусками и хотелось бы сразу прочитать нужный кусок. MZ>А это сделать не получится.
прочитать кусок не проблема. SQLite и FireBird позволяют в SELECT'е указать "окно". MS SQL CE, правда, не позволяет, но его старший брат может.
по моим тестам чтение 20К записей (на SQLite) не тормозит и можно читать целиком, но если записей будет 100К?
я отдаю себе отчет, что негоже юзеру показывать 100К записей, но в этом суть программы: длинный список с сортировкой и фильтрацией.
ov пишет:
> прочитать кусок не проблема. SQLite и FireBird позволяют в SELECT'е > указать "окно". MS SQL CE, правда, не позволяет, но его старший брат может. > по моим тестам чтение 20К записей (на SQLite) не тормозит и можно читать > целиком, но если записей будет 100К?
Какими такими блоками ?
Если речь о всяких там LIMIT-ах или TOP-ах,
то их не имеет смысла применять, если вам нужны не
первые N записей.
MZ>Если речь о всяких там LIMIT-ах или TOP-ах,
да, о них. MZ>то их не имеет смысла применять, если вам нужны не первые N записей.
почему? в SQLite есть конструкция LIMIT 100 OFFSET 1000, которая вернет 100 строк с 1000-й позиции. в FireBird есть FIRST/SKIP, кажется.
и как тогда "правильно" считать результат запроса в 100К строк? за раз чтоли??
ov пишет:
> MZ>Если речь о всяких там LIMIT-ах или TOP-ах, > да, о них. > MZ>то их не имеет смысла применять, если вам нужны не первые N записей. > почему? в SQLite есть конструкция LIMIT 100 OFFSET 1000, которая вернет > 100 строк с 1000-й позиции. в FireBird есть FIRST/SKIP, кажется.
Потому что LIMIT-ы или TOP-ы в общем случая обрабатывают
сначала ВЕСЬ набор данных, а потом возвращают только его часть.
Экономия -- только на операции FETCH на клиента.
При этом экономии можно достичь только :
0) выбирая только N записей от начала набора данных,
при этом оптимизатор может понять, что от запроса нужно только
первые N строк, и построит план так, чтобы только они обрабатывались,
и то при этом нужно, чтобы операция ORDER BY оптимизировалась за счёт индекса,
а не производилась реальная сортировка, иначе опять-таки будет обрабатываться
весь потенциальный набор данных
1) выбирая N записей начиная с M-ой СУБД придётся обработать подобным же
образом M+N записей, на M записей получится экономия только на FETCH-е на
клиента, и на ещё (размер таблицы — (M+N)) -- может быть экономия, как
в случае (0)
> и как тогда "правильно" считать результат запроса в 100К строк? за раз > чтоли??
Нужно не питать иллюзий, что есть какой-то магический способ выборки части
набора данных из всего набора. Набор нужно обрабатывать целиком.
Единственный реальный способ оптимизации выборки -- это отбросить
невыбранную часть данных, он работает (почти) всегда и везде.
Но даже в этом случае СУБД может обрабатывать весь большой невыбираемый
набор данных.
Да, вы тут говорили о SQL Lite, возможно, есть какие-то специфичные
для него аспекты этой проблемы, я его к сож. не знаю.
MZ>Нужно не питать иллюзий, что есть какой-то магический способ выборки части MZ>набора данных из всего набора. Набор нужно обрабатывать целиком.
да не в иллюзиях дело. просто хочется решить вопрос на уровне БД, оптимальным для нее способом. чтобы я мог ей сказать "хочу такие-то записи, с такой-то позиции", а она уже сама думала как их мне наиболее оптимально выдать.
иначе, если так рассуждать, можно дойти и до того, что сортировать результаты работы надо самому и не питать иллюзий, что БД с этим оптимально справится.
MZ>Да, вы тут говорили о SQL Lite, возможно, есть какие-то специфичные MZ>для него аспекты этой проблемы, я его к сож. не знаю.
да нет, там своих тараканов хватает. он окно делает как раз пропуская лишние строчки, в результате окно, близкое к началу выборки, летает, а если начинать с полумиллионной строчки — тормозит. MS SQL Server с хитрой конструкцией из ROW_NUMBER'ов летает и на таких количествах. файрберд тупил и на гораздо меньших цифрах, но там, возможно, я с индеками перемудрил.
MS SQL Server с > хитрой конструкцией из ROW_NUMBER'ов летает и на таких количествах. > файрберд тупил и на гораздо меньших цифрах, но там, возможно, я с > индеками перемудрил.
Это что за конструкция такая ?
Вообще, как вы узнаете ROW_NUMBER() строки априори, не выполняя запроса ?
MZ>Это что за конструкция такая ?
что-то типа этого. сервера под рукой нет проверить точно:
SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY ...) AS ROW FROM ...)
WHERE ROW>=... AND ROW<...
MZ>Вообще, как вы узнаете ROW_NUMBER() строки априори, не выполняя запроса ?
запрос, конечно, выполняется. но внутри БД и я не задумываюсь как его наиболее оптимально выполнить с ее точки зрения.
Здравствуйте, ov, Вы писали:
ov>что-то типа этого. сервера под рукой нет проверить точно: ov>
ov>SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY ...) AS ROW FROM ...)
ov>WHERE ROW>=... AND ROW<...
ov>
это аналитика
если был insert/update/delete между двумя выполнениями этого запроса, то номер строки — это средняя температура по больнице
Вам ясно сказали: по ID
про генераторы/сиквенсы для нумерации результатов упоминать не будем, т.к. за такое в любой софтостроительной конторе рвут руки, и это справедливо
N>Вам ясно сказали: по ID
про ID речь шла в контексте поиска выделенной строки после применения новой сортировки.
а пример запроса с ROW_NUMBER этого всего лишь эмуляция окна в MS SQL сервере. возможно кривая, я не вникал подробно, т.к. Compact Edition ее не поддерживает.
поиск выделенной строки по ID после сортировки у меня прекрасно работает пока я считываю результат запроса полностью.
а я хочу читать блоками, чтобы не фетчить все 100К/500К записей разом.
а после смены сортировки неплохо бы еще и блок правильный найти с первого раза.
и тут, похоже, все очень запущено...
N>про генераторы/сиквенсы для нумерации результатов упоминать не будем, т.к. за такое в любой софтостроительной конторе рвут руки, и это справедливо
согласен.
ov пишет:
> поиск выделенной строки по ID после сортировки у меня прекрасно работает > пока я считываю результат запроса полностью. > а я хочу читать блоками, чтобы не фетчить все 100К/500К записей разом. > а после смены сортировки неплохо бы еще и блок правильный найти с > первого раза. > и тут, похоже, все очень запущено...
Ещё раз -- вы хотите невозможного. Почти невозможного.
Фетчить наборы данных можно только целиком. Вообще, если вы выбрали
набор, потом тот же запросы выполнили, и выбрали набор ещё раз --
это уже другой набор, к первому не имеющий никакого отношения.
Фетчить большие наборы тоже плохо -- они никому не нужны, даже отсортированные.
Вам следует не заниматься поисками эфимерности, а улучшить
критерии выборки, чтобы сократить кол-во выбераемых с сервера
данных.
Здравствуйте, MasterZiv, Вы писали:
MZ>Фетчить наборы данных можно только целиком. Вообще, если вы выбрали MZ>набор, потом тот же запросы выполнили, и выбрали набор ещё раз -- MZ>это уже другой набор, к первому не имеющий никакого отношения.
Может я чего не понимаю, но чем плох вариант: сначала определяем на какую страницу попадает выбраная пользователем строка (по сути то с чего началась тема — нужно определить порядковый номер записи с даным идентификатором). Запрос получается вида (пишу с ораклиным синтаксисом)
select rn from (
select e.*, rownum rn
from table e
order by
e.field1, e.field2
)
where id = :id
при наличии индексов по полям сортировки даже при 100к может работать с приемлимой скоростью на соответствующем железе (по крайней мере оракл с rownum-ами неплохо работает).
После этого, зная номер записи (и расчитав на какой странице она находится), мы можем подчитать только нужную нам страницу запросом вида
select e.* from (
select e.*, rownum rn
from table e
order by
e.field1, e.field2
)
where rn between :start_index and :end_index
Опять же в оракле такой запрос не приводит к тому, что читаются все данные целиком. Оракл понимает, что такое rownum и как тут можно оптимизировать.
Естественно оба запроса в одной транзакции.
MZ>Фетчить большие наборы тоже плохо -- они никому не нужны, даже отсортированные. MZ>Вам следует не заниматься поисками эфимерности, а улучшить MZ>критерии выборки, чтобы сократить кол-во выбераемых с сервера MZ>данных.
В теории согласен. Но вот на практике как это реализовать непонятно. Делать предварительную выборку количества без учёта сортировок и если в результате получается много — говорить пользователю, чтоб задал более жёсткие ограничения? Обычно пользователи хотят задавать любые критерии и иметь пейджинг.
KRA>Может я чего не понимаю, но чем плох вариант:
вариант хорош. плохо то, что нет "настольной" БД, способной это сделать не подохнув...
KRA>Обычно пользователи хотят задавать любые критерии и иметь пейджинг.
угу. об этом и речь.
KRA пишет:
> Может я чего не понимаю, но чем плох вариант: сначала определяем на > какую страницу попадает выбраная пользователем строка (по сути то с чего > началась тема — нужно определить порядковый номер записи с даным > идентификатором). Запрос получается вида (пишу с ораклиным синтаксисом) > > > select rn from ( > select e.*, rownum rn > from table e > order by > e.field1, e.field2 > ) > where id = :id > > > > при наличии индексов по полям сортировки даже при 100к может работать с > приемлимой скоростью на соответствующем железе (по крайней мере оракл с > rownum-ами неплохо работает).
Вот этот запрос:
select e.*, rownum rn > from table e > order by > e.field1, e.field2 > )
который, между прочим, у вас всю таблицу обрабатывает,
должен сначала весь выбраться, отсортироваться,
и потом выбраться из него уже с id = :id.
Что тут может быть быстрого ?
Где тут поводы для оптимизации ? (кроме оптимизации ORDER BY
по индексу)
Кстати, по идее, order by в подзапросе СУБД может запросто
выкинуть, оно ни на что не влияет. Псевдоколонка rownum
же имеет смысл только в строке _возвращаемого_ набора данных,
она по идее там и должна генерироваться. Я не говорю, что
оракл так будет делать, просто логически тут всё очешь шатко.
> После этого, зная номер записи (и расчитав на какой странице она > находится),
Как ?
мы можем подчитать только нужную нам страницу запросом вида > > select e.* from ( > select e.*, rownum rn > from table e > order by > e.field1, e.field2 > ) > where rn between :start_index and :end_index > > > > Опять же в оракле такой запрос не приводит к тому, что читаются все > данные целиком.
Ээээ.. доказательства будут ?
Оракл понимает, что такое rownum и как тут можно > оптимизировать.
Как можно что-то оптимизировать по rownum, если он генерируется
в момент создания набора данных ?
Объясните, я не очень понимаю.
> > Естественно оба запроса в одной транзакции.
> В теории согласен. Но вот на практике как это реализовать непонятно. > Делать предварительную выборку количества без учёта сортировок и если в > результате получается много — говорить пользователю, чтоб задал более > жёсткие ограничения?
Да, например. Можно по любому запросу выводить, скажем, только первые 100
записей, и если есть ещё, писать, что часть данных были отброшены.
Больше 100-1000 записей никто читать не будет. Если это не выгрузка
данных куда-то для экспорта, или не отчёт какой-то для складирования, бесполезно
выводить более 100-1000 записей. Конкретные 100-1000 конечно варьируются в
зависимости от специфики приложения, но в общем идея такая.
Обычно пользователи хотят задавать любые критерии и > иметь пейджинг.
Нахрена пейджинг нужен, если ты получаешь 10-100 нужных тебе записей ?
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>
если так Вы хотите получить порядковый номер в сортировке e.field1, e.field2, то ошибаетесь
KRA>Опять же в оракле такой запрос не приводит к тому, что читаются все данные целиком. Оракл понимает, что такое rownum и как тут можно оптимизировать.
никакой магии
данные читаются целиком
Здравствуйте, 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.
Собираем статистику по таблице — для оптимизатора:
Теперь собственно сам запрос. Для начала рассмотрим только запрос аналогичный конструкции 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 нужных нам элементов отсортированных в нужном нам порядке. Это видно по трассировке:
Операция 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. Тогда ваша мысль не
работает.
Овощ пишет:
> Также здесь я не уверен, что такое использование значения rownum в > конструкции between может быть эффективно оптимизировано. Oracle может > построить эффективный план для простых предикатов, подобных ... where > rownum < :end_index (и использовать в этом месте такую операцию в плане > как COUNT STOPKEY). Впрочем, вероятно и это вполне может хорошо работать.
Дело не в том, какая конструкция, between или нет. Я уверен, что between
уж как-нибудь оракл умеет обрабатывать правильно (он представляется
как начало и конец диапазона, field >= :beg and field <= :end),
а вот дело-то в том, что rownum не известен ДО выполнения запроса,
поэтому на его основе нельзя что-то оптимизировать в принципе.
Вообще rownum -- дебильная штука. Хотя и прикольная иногда.
Только я не понял, что именно вы предлагаете взамен?
Увидел только это:
MZ>Да, например. Можно по любому запросу выводить, скажем, только первые 100 MZ>записей, и если есть ещё, писать, что часть данных были отброшены.
MZ>Больше 100-1000 записей никто читать не будет. Если это не выгрузка MZ>данных куда-то для экспорта, или не отчёт какой-то для складирования, бесполезно MZ>выводить более 100-1000 записей. Конкретные 100-1000 конечно варьируются в MZ>зависимости от специфики приложения, но в общем идея такая.
MZ>>Обычно пользователи хотят задавать любые критерии и MZ>>иметь пейджинг.
MZ>Нахрена пейджинг нужен, если ты получаешь 10-100 нужных тебе записей ?
Если у нас есть "первые"/"последние" то видимо есть какой-то порядок (который наверняка задается через order by).
Если нам нужны только 100 записей, то видимо у нас есть ограничение на выборку в виде TOP/rownum.
Получается, что для этого нам надо использовать точно такую же конструкцию из rownum + order by (или top + order by), которая вам не понравилась.
И в конечно счете все получается абсолютно точно также, как и в случаях, которые я расписал выше.
Разницы между пейджинг/непейджинг здесь нет никакой, разве что в микродеталях реализации (зададим жестко, что номер последней строки для выборки не должен быть больше 100 и все — нет пейджинга, а есть предложенный вами вариант).
Не вижу разницы. Она здесь есть?
Путь для "оптимизации" я вижу только один — выбирать не первые 100, а случайные 100. Тогда можно отказаться от сортировки.
Но обычно сортировка результатов поиска просто обязательна в любом приложении (да и само поле сортировки может меняться).
Варинт же с отображением пользователю вместо результатов выборки сообщения вида "Найдено 100000 записей удовлетворяющих критериям поиска. Это слишком много для отображения. Уточните критерии поиска." вообще мне кажется неприемлемым.
Здравствуйте, Овощ, Вы писали:
О>Правильно уже сказали — тут фактическая ошибка. Oracle высчитывает rownum до выполнения order by. Т.е. в общем случае надо делать так (выносить rownum на уровень выше чем order by):
Спасибо за уточнения. Я привёл приблизительный вид запросов, т.к. это не главная мысль. Главная мысль такая, что оптимизатор oracle отлично понимает семантику rownum.
