определить позицию строки в запросе - Базы данных

Здравствуйте, 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. Т.е. другими словами, это будет эффективно лишь если мы показываем страницы записей с небольшими номерами. Чем дальше отображаемая страница находится к концу списка страниц тем медленне будет запрос.

Вот как то так...

Здравствуйте, 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>данных.

В теории согласен. Но вот на практике как это реализовать непонятно. Делать предварительную выборку количества без учёта сортировок и если в результате получается много — говорить пользователю, чтоб задал более жёсткие ограничения? Обычно пользователи хотят задавать любые критерии и иметь пейджинг.

Здравствуйте, MasterZiv, Вы писали:

Только я не понял, что именно вы предлагаете взамен?
Увидел только это:

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.

вопрос к тем, кто показывает много данных в таблицах
есть у меня запрос

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

ov пишет:

> зачем все это надо: чтобы в гриде с сортировкой и фильтрацией
> восстановить выделенную строчку после смены сортировки. так как записей
> может быть много, то чтение идет кусками и хотелось бы сразу прочитать
> нужный кусок.

Какая разница, какой по порядку идёт нужная вам строка ?
У каждой строки есть первичный ключ (должен быть, иначе
нужно делать редизайн БД), вы до смены сортировки запоминаете значение
ключа, делаете сортировку, и находите нужную запись по запомненному
значению ключа.

Posted via RSDN NNTP Server 2.1 beta

ov пишет:

так как записей
> может быть много, то чтение идет кусками и хотелось бы сразу прочитать
> нужный кусок.

А это сделать не получится.

Posted via RSDN NNTP Server 2.1 beta

>вы до смены сортировки запоминаете значение ключа, делаете сортировку, и находите нужную запись по запомненному
>значению ключа.
все бы хорошо, да надо читать блоками. для чтения целиком проблем нет — все так и делается и прекрасно работает.

>> может быть много, то чтение идет кусками и хотелось бы сразу прочитать нужный кусок.
MZ>А это сделать не получится.
прочитать кусок не проблема. SQLite и FireBird позволяют в SELECT'е указать "окно". MS SQL CE, правда, не позволяет, но его старший брат может.
по моим тестам чтение 20К записей (на SQLite) не тормозит и можно читать целиком, но если записей будет 100К?

я отдаю себе отчет, что негоже юзеру показывать 100К записей, но в этом суть программы: длинный список с сортировкой и фильтрацией.

ov пишет:

> прочитать кусок не проблема. SQLite и FireBird позволяют в SELECT'е
> указать "окно". MS SQL CE, правда, не позволяет, но его старший брат может.
> по моим тестам чтение 20К записей (на SQLite) не тормозит и можно читать
> целиком, но если записей будет 100К?

Какими такими блоками ?
Если речь о всяких там LIMIT-ах или TOP-ах,
то их не имеет смысла применять, если вам нужны не
первые N записей.

Posted via RSDN NNTP Server 2.1 beta

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, возможно, есть какие-то специфичные
для него аспекты этой проблемы, я его к сож. не знаю.

Posted via RSDN NNTP Server 2.1 beta

MZ>Нужно не питать иллюзий, что есть какой-то магический способ выборки части
MZ>набора данных из всего набора. Набор нужно обрабатывать целиком.
да не в иллюзиях дело. просто хочется решить вопрос на уровне БД, оптимальным для нее способом. чтобы я мог ей сказать "хочу такие-то записи, с такой-то позиции", а она уже сама думала как их мне наиболее оптимально выдать.

иначе, если так рассуждать, можно дойти и до того, что сортировать результаты работы надо самому и не питать иллюзий, что БД с этим оптимально справится.

MZ>Да, вы тут говорили о SQL Lite, возможно, есть какие-то специфичные
MZ>для него аспекты этой проблемы, я его к сож. не знаю.
да нет, там своих тараканов хватает. он окно делает как раз пропуская лишние строчки, в результате окно, близкое к началу выборки, летает, а если начинать с полумиллионной строчки — тормозит. MS SQL Server с хитрой конструкцией из ROW_NUMBER'ов летает и на таких количествах. файрберд тупил и на гораздо меньших цифрах, но там, возможно, я с индеками перемудрил.

ov wrote:

MS SQL Server с
> хитрой конструкцией из ROW_NUMBER'ов летает и на таких количествах.
> файрберд тупил и на гораздо меньших цифрах, но там, возможно, я с
> индеками перемудрил.

Это что за конструкция такая ?

Вообще, как вы узнаете ROW_NUMBER() строки априори, не выполняя запроса ?

Posted via RSDN NNTP Server 2.1 beta

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К записей разом.
> а после смены сортировки неплохо бы еще и блок правильный найти с
> первого раза.
> и тут, похоже, все очень запущено...

Ещё раз -- вы хотите невозможного. Почти невозможного.

Фетчить наборы данных можно только целиком. Вообще, если вы выбрали
набор, потом тот же запросы выполнили, и выбрали набор ещё раз --
это уже другой набор, к первому не имеющий никакого отношения.

Фетчить большие наборы тоже плохо -- они никому не нужны, даже отсортированные.
Вам следует не заниматься поисками эфимерности, а улучшить
критерии выборки, чтобы сократить кол-во выбераемых с сервера
данных.

Posted via RSDN NNTP Server 2.1 beta

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 нужных тебе записей ?

Posted via RSDN NNTP Server 2.1 beta

Здравствуйте, 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>

если так Вы хотите получить порядковый номер в сортировке 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). Впрочем, вероятно и это вполне может хорошо работать.

Овощ пишет:

> Однако полной сортировки не происходит, ввиду того что 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

От:	Овощ	http://www.google.com
Дата:	02.09.09 07:52
Оценка:	98 (2)

	От:	KRA
	Дата:	01.09.09 14:38
	Оценка:	+1

От:	Овощ	http://www.google.com
Дата:	02.09.09 10:01
Оценка:	+1

	От:	KRA
	Дата:	02.09.09 10:06
	Оценка:	+1

	От:	ov
	Дата:	28.08.09 13:17
	Оценка:

От:	DarkMaster	http://www.bdslib.at.ua
Дата:	28.08.09 15:42
Оценка:

	От:	MasterZiv
	Дата:	29.08.09 06:47
	Оценка:

	От:	MasterZiv
	Дата:	29.08.09 06:48
	Оценка:

	От:	MasterZiv
	Дата:	29.08.09 16:22
	Оценка:

	От:	MasterZiv
	Дата:	31.08.09 06:55
	Оценка: