Выбор промежуточных записей из результирующего множества

Источник: ln

Эта статья посвящена выбору записей из результирующего множества по частям. Например, для размещения на Web-страницах. Очередной выпуск по мотивам ответов Тома Кайт на вопросы посетителей его сайта.

Кстати, всем интересующимся хочу сообщить, что сегодня перевод второй части книги Тома Кайта "Expert one-on-one: Oracle" закончен. Последние файлы приложений сегодня утром отправлены в издательство...

Как выдавать результат постранично?

Я хотел бы выбирать данные после соединения трех таблиц и сортировки по некоторому полю. Поскольку этот запрос возвращает примерно 100 записей, я хотел бы разбить результирующее множество на 4 части, по 25 записей каждая. И я хотел бы пронумеровать записи. Можно ли это сделать в SQL*Plus?

Ответ Тома Кайта

В Oracle8i, release 8.1 - да.

select * 
  from (select a.*, rownum rnum
           from (ВАШ_ЗАПРОС -- включая конструкцию order by) 
          where rownum <= MAX_ROWS)
 where rnum >= MIN_ROWS
/

И все. В версиях до 8.0 включительно это не сработает.

Комментарий читателя от 1 апреля 2002 года

Замечательный запрос. Я просто хотел удостоверится, что понимаю его. Запрос выполняется 4 раза и каждый раз максимальный и минимальный номера строк меняются. Правильно?

Ответ Тома Кайта

Да, значения min и max меняются, чтобы получать разные диапазоны строк.

А как насчет between?

Вернемся к старой дискуссии о различии между запросом

select p.*, rownum rnum
    from (select * from hz_parties) p
          where rownum between 90 and 100

и

select * from ( 
   select p.*, rownum rnum
           from (select * from hz_parties) p
          where rownum < 100
) where rnum >= 90

Я утверждаю, что, с точки зрения производительности, они идентичны. В самом деле, план выполнения первого запроса:

  SELECT STATEMENT  20/100
      VIEW  20/100
          Filter Predicates
              from$_subquery$_001.RNUM>=90
          COUNT (STOPKEY)  
              Filter Predicates
                  ROWNUM<=100
              TABLE ACCESS (FULL) hz_parties 20/3921

Кажется более быстрым, чем

  SELECT STATEMENT  20/100
      COUNT (STOPKEY)  
          Filter Predicates
              ROWNUM<=100
          FILTER  
              Filter Predicates
                  ROWNUM>=90
              TABLE ACCESS (FULL) hz_parties 20/3921

Но, обратите внимание, что все шаги плана - не блокирующие!. Поэтому не имеет значения, какое условие проверяется раньше...

Ответ Тома Кайта

Пожалуйста, не надо утверждать - ставьте эксперименты и ДОКАЗЫВАЙТЕ (давайте, я же всегда так делаю).

Ваш первый запрос, "where rownum between 90 and 100" никогда не возвращает НИКАКИХ данных. Это условие - всегда ложно, всегда.

Я уже доказал, отвечая на другой вопрос (видимо, тоже ваш), что:

select * from ( 
   select p.*, rownum rnum
           from (select * from hz_parties) p
          where rownum < 100
) where rnum >= 90

работает быстрее, чем:

select * from ( 
   select p.*, rownum rnum
           from (select * from hz_parties) p
) where rnum between 90 and 100

Именно это, я надеюсь, вы СОБИРАЛИСЬ набрать. Все связано со способом выполнения COUNT(STOPKEY) и тем фактом, что мы должны выполнить:

   select p.*, rownum rnum
           from (select * from hz_parties) p

А ЗАТЕМ применить фильтр, тогда как в первом случае мы выбираем первые 100 строк И ВСЕ.

Итак, пусть имеется непроиндексированная таблица:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from big_table;

  COUNT(*)
----------
   1099008

(она получена в результате трехкратного копирования всего содержимого представления all_objects). Я выполню к ней три запроса. Ваш, чтобы показать, что он не работает (не возвращает данные), тот, который, думаю, вы имели ввиду, и мой вариант:

select p.*, rownum rnu
  from (select * from big_table) p
 where rownum between 90 and 100

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        1      6.17      15.31      14938      14985         81           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      6.17      15.31      14938      14985         81           0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 216

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  COUNT STOPKEY
      0   FILTER
1099009    TABLE ACCESS FULL BIG_TABLE

Ваш запрос - данные не найдены... Обратите внимание, однако, на количество просмотренных строк

select *
from (
select p.*, rownum rnum
  from (select * from big_table) p
)
 where rnum between 90 and 100

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      7.93      17.03      14573      14986         81          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      7.93      17.03      14573      14986         81          11

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 216

Rows     Row Source Operation
-------  ---------------------------------------------------
     11  VIEW
1099008   COUNT
1099008    TABLE ACCESS FULL BIG_TABLE

Это, я надеюсь, вы имели ввиду - и на этот раз обратите внимание, сколько строк просмотрено!

Теперь запрос, который я всем рекомендую выполнять:

select * from (
   select p.*, rownum rnum
           from (select * from big_table) p
          where rownum < 100
) where rnum >= 90

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.00       0.01          1          7         12          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.01          1          7         12          10

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 216

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  VIEW
     99   COUNT STOPKEY
     99    TABLE ACCESS FULL BIG_TABLE

БОЛЬШАЯ разница. Готов поспорить...

Утверждения - они кому не нужны.
Тесты, эксперименты, статистика - мне они нравятся, они мне нужны.

Комментарий читателя от 3 апреля 2002 года

Спасибо, Том. Я, наконец, заметил, что в одном условии используется rownum, а в другом - rnum, и что они отличаются :-)

sql>select * from ( 
  2     select p.*, rownum rnum
  3             from (select * from hz_parties ) p
  4            where rownum < 100
  5  ) where rnum >= 90  

Statistics
----------------------------------------------------------
          7  consistent gets
          5  physical reads

Лучшее решение, которое я сам придумал:

appsmain>select * from (
  2   select * from ( 
  3     select p.*, rownum rnum
  4             from (select * from hz_parties ) p
  5   ) where rnum between 90 and 100
  6  ) where rownum < 10

Statistics
----------------------------------------------------------
         15  consistent gets
          5  physical reads

Оно и работает медленнее, и явно менее элегантно :-(

Вопрос читателя от 3 апреля 2002 года

Том!

Извини, но я не вижу разницы:

public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    System.out.println(execute("select * from (select p.*, rownum rnum "
+ "  from (select * from hz_parties ) p "
+ "           where rownum < 100 "
+ " ) where rnum >= 90  "));
    System.out.println(execute("select * from ( \n"
+ "   select p.*, rownum rnum "
+ "             from (select * from hz_parties ) p "
+ "   ) where rnum between 90 and 100"));
  
}
static long execute(String query) throws Exception {
    Connection con = 
DriverManager.getConnection("jdbc:oracle:thin:@dlserv7:1524:main","apps","apps");

    con.setAutoCommit(false);

    con.createStatement().execute("alter system flush shared_pool");
    long t1 = System.currentTimeMillis();
    ResultSet rs = con.createStatement().executeQuery(query);
    rs.next();
    rs.next();
    rs.next();
    rs.next();
    rs.next();
    rs.next();
    long t2 = System.currentTimeMillis();
        
    con.rollback();        
    con.close();
    return t2 - t1;
}

Оба запроса выполняются 0.6 секунды. Я интерпретирую это так:
Запрос с конструкцией "between" в случае, если мы открываем курсор, читаем первые строки, а затем игнорируем остальные, по сути, совпадает с запросом "between" со счетчиком выбранных строк (тот прикольный sql-оператор в моем предыдущем комментарии).Сервер не пойдет дальшек и не будет проверять условие between для всей таблицы, или как?

Ответ Тома Кайта

TKPROF, TKPROF, TKPROF.

Вот что вам нужно использовать.

Следующий запрос:

select * 
  from ( select p.*, rownum rnum 
           from ( ВАШ_ЗАПРОС )
          where rownum < 100
       )
  where rnum >= 90

выполняет ваш запрос, выбирает первые 100 строк результата и останавливается. Если ВАШ_ЗАПРОС должен получить все строки, прежде чем сможет вернуть первую (например, включает конструкции типа group by), то различие в вашем случае может оказаться небольшим, но оно есть. Используйте TKPROF, чтобы отсеять время ряботы java-кода (замер времени на клиенте подобным образом может сильно искажать результаты).

Рассмотрим следующий пример:

Здесь нам не нужно получать последнюю строку, прежде чем возвращать первую - все работает очень "быстро"

select *
  from ( select p.*, rownum rnum
           from ( select owner, object_name, object_type
                    from big_table
                ) p
          where rownum <= 100
       )
 where rnum >= 90

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.01       0.00         63          7         12          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.00         63          7         12          11

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 216

Rows     Row Source Operation
-------  ---------------------------------------------------
     11  VIEW
    100   COUNT STOPKEY
    100    TABLE ACCESS FULL BIG_TABLE

Теперь давайте добавим функцию агрегирования - теперь придется обработать все строки в таблице. ОДНАКО поскольку rownum вынесен как можно глубже, можно ускорить работу за счет определенных оптимизаций

select *
  from ( select p.*, rownum rnum
           from ( select owner, object_name, object_type, count(*)
                    from big_table
                   group by owner, object_name, object_type
                ) p
          where rownum <= 100
       )
 where rnum >= 90

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      5.78      18.08      14794      14985         81          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.79      18.08      14794      14985         81          11

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 216

Rows     Row Source Operation
-------  ---------------------------------------------------
     11  VIEW
    100   COUNT STOPKEY
    100    VIEW
    100     SORT GROUP BY STOPKEY
1099008      TABLE ACCESS FULL BIG_TABLE

Наконец, сделаем по-вашему - мы не выносим rownum как можно глубже, оптимизировать ничего нельзя, и запрос работает действительно медленно

select *
  from ( select p.*, rownum rnum
           from ( select owner, object_name, object_type, count(*)
                    from big_table
                   group by owner, object_name, object_type
                ) p
       )
 where rnum between 90 and 100

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2     20.15     112.44      24136      14985        184          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5     20.15     112.47      24136      14985        184          11

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 216

Rows     Row Source Operation
-------  ---------------------------------------------------
     11  VIEW
  17172   COUNT
  17172    VIEW
  17172     SORT GROUP BY
1099008      TABLE ACCESS FULL BIG_TABLE

В конечном итоге, решать вам. Я только могу показать, что мой вариант работает в несколько раз быстрее. Выбирайте...

В вашем случае, могу предположить следующее:

  • hz_parties - это представление (узнаю)
  • Это представление полжно получить все строки, прежде чем из него можно будет выбрать первую
  • Вы выбираеет не так уж много строк (порядка тысячи - они все помещаются в оперативной памяти)
  • Оптимизация rownum в вашем случае многого не дает - с помощью tkprof вы сможете выяснить, что именно она дает.

В общем случае, я хочу сказать вот что:

Использование "where rnum between a and b" будет ошибкой, если можно вынести rownum во внутренний запрос и получить ФЕНОМЕНАЛЬНОЕ, в общем случае, повышение производительности. Но выбирать вам.

Комментарий читателя от 25 июля 2002 года

Первый запрос, представленный ниже, работает вдвое быстрее, чем второй. Не могли бы вы объяснить причину?

select b.*
(Select * from A Order by A.Id) b
where rownum<100

select * from 
(select b.*,rownum rnum
(Select * from A Order by A.Id) b
where rownum<100)
and rnum >= 50

Ответ Тома Кайта

Вдвое быстрее... Странно... Не знаю, что это значит.

Могу только сказать, что (после исправления ваших запросов) я получил другие результаты. В моем случае, когда в таблице big_table - порядка 1000000 строк, я получил:

big_table@ORA920.US.ORACLE.COM> set autotrace traceonly
big_table@ORA920.US.ORACLE.COM> select b.*
  2  from (Select * from big_table A Order by A.Id) b
  3  where rownum<100
  4  /

99 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=15735 Card=99 Bytes=141000000)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=15735 Card=1000000 Bytes=141000000)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (Cost=15735 
Card=1000000 Byte
          s=89000000)

   4    3         INDEX (FULL SCAN) OF 'BIG_TABLE_PK' (UNIQUE) (Cost=2090 
Card=1000000)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
       9701  bytes sent via SQL*Net to client
        565  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed

big_table@ORA920.US.ORACLE.COM> select * from
  2  (select b.*,rownum rnum
  3  from (Select * from big_table A Order by A.Id) b
  4  where rownum<100)
  5  where rnum >= 50
  6  /

50 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=15735 Card=99 Bytes=15246)
   1    0   VIEW (Cost=15735 Card=99 Bytes=15246)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=15735 Card=1000000 Bytes=141000000)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (Cost=15735 
Card=1000000 By
          tes=89000000)

   5    4           INDEX (FULL SCAN) OF 'BIG_TABLE_PK' (UNIQUE) (Cost=2090 
Card=1000000)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
       5667  bytes sent via SQL*Net to client
        532  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

big_table@ORA920.US.ORACLE.COM> set autotrace off
big_table@ORA920.US.ORACLE.COM> spool off

Второй запрос более эффективен, чем первый.

Пища для размышлений. Комментарий от 10 сентября 2002 года

... Предположим, кто-то хочет применить твой метод для выбора строк с N по M ближе к концу большой таблицы. Возможно, не для постраничного просмотра результатов, а по какой-то другой непонятной причине. Следует ли ожидать снижения производительности SELECT, когда диапазон строк выбирается ближе к концу таблицы? Если да, то я понимаю, что происходит. А вот если нет... значит, я по-прежнему ничего не понимаю.

Ответ Тома Кайта

Если выбирать надо лиже к онцу, я бы посоветовал получать результирующее множество в другом порядке, и выбирать ближе к началу (поменять порядок сортировки данных).

Да, последних N строк, в общем случае, выбирать дольше, чем первых N (не всегда, но этого вполне можно ожидать)

Комментарий читателя от 21 сентября 2002 года

Я попытался выполнить такой запрос к упорядоченному представлению, в котором около 7000 строк имеют значение eventseverity 64.

select * from 
( select fmeventsview.* , rownum rnum from 
(select * from fmeventsview where EventSeverity = 64 )fmeventsview where rownum 
<=500 ) where rnum >0;

Но в результате получил только 234 строки.

Если выполнить вложенный запрос

"select fmeventsview.* , rownum rnum from 
(select * from fmeventsview where EventSeverity = 64 )fmeventsview where rownum 
<=500 "

я получаю 500 строк со значениями RNUM от 1 до 500

Я не понимаю, где облажался :-( Посоветуй что-нибудь

Ответ Тома Кайта

Я ненавижу представления с конструкцией order by. Добавляйте order by в запрос. Для сложных запросов порядок данных в представлениях поддерживать не обязательно. Скорее всего, значение rownum где-то теряется, но при отсутствии воспроизводимого теста я не могу сказать точно, что происходит.

Комментарий читателя от 22 сентября 2002 года

Привет, Том

Представленный тобой запрос очень хорош и работает быстро в диапазоне от 100000 до 150000 строк, но при попытке выборки строк после 500000-й он работает более минуты.

Запрос:

select fatwaid,fatwatitle
   from (select a.*,rownum r
           from (select * from fatwa order by fatwaid) a
   where rownum <= &upperbound )
where r >= &lowerbound

при выполнении со значениями 150001 и 150010, дает следующий результат и план

10 rows selected.

Elapsed: 00:00:02.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=150010 Byte
          s=11700780)
   1    0   VIEW (Cost=826 Card=150010 Bytes=11700780)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=826 Card=1282785 Bytes=83381025)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'FATWA' (Cost=826 C
          ard=1282785 Bytes=2837520420)
   5    4           INDEX (FULL SCAN) OF 'PK_FATWA' (UNIQUE) (Cost=26
          Card=1282785)

А при выполнении со значениями 1000001 и 1000010 результат и план показаны ниже:

10 rows selected.

Elapsed: 00:01:01.08

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=1000010 Byt
          es=78000780)
   1    0   VIEW (Cost=826 Card=1000010 Bytes=78000780)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=826 Card=1282785 Bytes=83381025)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'FATWA' (Cost=826 C
          ard=1282785 Bytes=2837520420)
   5    4           INDEX (FULL SCAN) OF 'PK_FATWA' (UNIQUE) (Cost=26
          Card=1282785)

Нельзя ли ускорить процесс получения последних строк?

Нельзя. Запрос хорошо подходит для постраничного просмотра результирующего множества. Если учесть, что страницы просматривают ЛЮДИ, размер типичной страницы составляет 10-25 строк, и у людей нет и НЕ БУДЕТ миллиарда лет времени и терпения, чтобы просматривать 100000 страниц - этот запрос очень даже подходит.

Может, лучше добавить конструкцию order by DESC и получить первую страницу?

(Подумайте - чтобы получить "последнюю страницу", необходимо пройти по всем предыдущим. При сортировке desc, скорее всего, просто будет во обратном порядке просмотрен индекс.)

Комментарий читателя от 21 октября 2002 года

Привет, Том!

Я активно использую твой запрос при работе с базой данных, читая по 1000 записей. При 100000 записей (это вполне нормально, как говорят мне пользователи :-) ), выборка первых 1000 строк занимает 50 секунд :-(. (Сервер работает на ОС Solaris, а для доступа к базе используется интерфейс JDBC). Я выполняю сортировку (order by) по одному из первичных ключей. Не мог бы ты посоветовать, как повысить производительность в этом случае???

Ответ Тома Кайта

http://asktom.oracle.com/~tkyte/tkprof.html

Используйте это средство (sql_trace + TIMED_STATISTICS) для получения плана выполнения запроса, количества строк, обработанных на каждом шаге плана, и на основе этой информации начинайте настройку.

Возможно, необходимо выполнять оптимизацию в режиме FIRST_ROWS.

Кстати, почему 1000 строк, - 25 или 100 будет более понятно. Но, в любом случае, вам, скорее всего, приходится сортировать каждый раз 100000 строк. Проверьте также размер sort_area size.

Комментарий читателя от 18 декабря 2002

Том,

Доступны ли курсоры с прокруткой (9.2) в pl/sql и jdbc, или только в pro c/c++?

Если нет, когда эта возможность появится в pl/sql?

Ответ Тома Кайта

Они есть в jdbc.

Я не представляю себе ситуацию, в которой они нужны/желательны в plsql. Они полезны для подключений в среде клиент/сервер, когда необходимо просматривать результирующее множество постранично, но plsql для этой среды не предназначен - он работает на сервере. Мы предполагаем, что это будет делать клиент (например, forms или jdbc). Когда в хранимой процедуре надо "возвращаться" к обработанным строкам?

Как сделать это в sql? Комментарий от 22 февраля 2003 года

Том,

Как возвращать множество n из каждой группы записей по ключу, например, для таких данных...

store     customer   qty
1         10         10
1         100        20
1         1000       30
.......................
2         20        20
2         200       200
...........
...........

Я хочу возвращать две записи из каждой группы по полю store - по две записи для каждого магазина.

select *
  from ( select t.*, row_number() over (partition by store order by customer) rn
           from t
       )
 where rn <= 2;

даст требуемый результат.

Комментарий читателя от 28 февраля 2003 года

Том,

Я решил провести дальнейшее исследование предложенного тобой подхода. А если кто-то захочет получить записи с M-ой по N-ую из Dept, а не из Emp:

select * from (select tmp1.*, rownum1 rnum
            from (select e.* from scott.emp e, scott.dept d where e.deptno = d.deptno) tmp1,
            (select deptno, rownum rownum1 from scott.dept)tmp2
            where tmp1.deptno = tmp2.deptno and 
        rownum1 <= :End ) where rnum >= :Start;
/

Ну, эту рыбку можно зажарить и по-другому.

Аналитические функции - вот в чем сила:

scott@ORA920> select dept.deptno, dname, ename,
  2         dense_rank() over ( order by dept.deptno ) dr
  3    from emp, dept
  4   where emp.deptno = dept.deptno
  5  /

    DEPTNO DNAME          ENAME              DR
---------- -------------- ---------- ----------
        10 ACCOUNTING     CLARK               1
        10 ACCOUNTING     KING                1
        10 ACCOUNTING     MILLER              1
        20 RESEARCH       SMITH               2
        20 RESEARCH       ADAMS               2
        20 RESEARCH       FORD                2
        20 RESEARCH       SCOTT               2
        20 RESEARCH       JONES               2
        30 SALES          ALLEN               3
        30 SALES          BLAKE               3
        30 SALES          MARTIN              3
        30 SALES          JAMES               3
        30 SALES          TURNER              3
        30 SALES          WARD                3

14 rows selected.

scott@ORA920> variable x number
scott@ORA920> variable y number
scott@ORA920> exec :x := 2; :y := 3;

PL/SQL procedure successfully completed.

scott@ORA920> select *
  2    from (
  3  select dept.deptno, dname, ename,
  4         dense_rank() over ( order by dept.deptno ) dr
  5    from emp, dept
  6   where emp.deptno = dept.deptno
  7         )
  8   where dr between :x and :y
  9  /

    DEPTNO DNAME          ENAME              DR
---------- -------------- ---------- ----------
        20 RESEARCH       SMITH               2
        20 RESEARCH       ADAMS               2
        20 RESEARCH       FORD                2
        20 RESEARCH       SCOTT               2
        20 RESEARCH       JONES               2
        30 SALES          ALLEN               3
        30 SALES          BLAKE               3
        30 SALES          MARTIN              3
        30 SALES          JAMES               3
        30 SALES          TURNER              3
        30 SALES          WARD                3

11 rows selected. 

Ошибка при использовании дат. Комментарий от 02 марта 2003 года

Том!

Приятно видеть разные подходы к потсраничному просмотру результатов.

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

Проблема #1.

См. следующий код:

SQL> create or replace procedure sp(out_cvGenric OUT 
PKG_SWIP_CommDefi.GenCurTyp) is
  2  begin
  3  
  4  OPEN out_cvGenric FOR 
  5  select *
  6      from (
  7    select dept.deptno, dname, ename,to_char(hiredate,'dd-mm-yyyy'),
  8           dense_rank() over ( order by dept.deptno ) dr
  9      from emp, dept
 10     where emp.deptno = dept.deptno and hiredate between '17-DEC-80' and '17-DEC-82'
 11           )
 12  where dr between 2 and 3;

 19  end ;
 20  /

Warning: Procedure created with compilation errors.
SQL> show err;

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/28     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         , from

Я решил эту проблему, вынеся запрос в строку (OPEN out_cvGenric FOR 'select * from ... ' ) и задав конструкцию USING. Все работает прекрасно.

С чем связана эта ошибка?

Issue #2.

См. следующий код. Это моя реальная реализация твоего метода в PL/SQL:

procedure sp_clips_reports_soandso (
                in_noperationcenterid in number,
                in_dreportfromdt in  date , 
                in_dreporttodt in date ,
                in_cusername in varchar2,
                in_ntirestatuscode in number,
                in_cwipaccount in varchar2,
                in_npagestart in  number,
                in_npageend in  number ,
                out_nrecordcnt out number ,
                out_nstatuscode out number,
                out_cvgenric out pkg_clips_commdefi.gencurtyp,
                out_cerrordesc out varchar2) is

            v_tempstart    number(5) ;
            v_tempend    number(5) ;
begin
        out_nstatuscode := 0;

            select count(tire_trn_number) into out_nrecordcnt
            from    t_clips_tire 
            where     redirect_operation_center_id = in_noperationcenterid
                and    tire_status_id = in_ntirestatuscode
                and    tire_date >= in_dreportfromdt
                and tire_date <= in_dreporttodt
                and wip_account = in_cwipaccount ;

        if in_npagestart =  -1 and in_npageend = -1 then
        
            v_tempstart    := 1;
            v_tempend    := out_nrecordcnt ;
        else
              v_tempstart :=   in_npagestart ;
              v_tempend :=    in_npageend ;

        end if ;
open out_cvgenric for 
'select *
    from (
  select tire.tire_trn_number tiretrnnumber,
                    to_char(tire.tire_date,''mm/dd/yy''),
                    tire.tire_time,
                    tire.direct_submitter_name user_name,
                dense_rank() over ( order by tire.tire_trn_number ) dr
            from    t_clips_tire tire,
                t_clips_afs_transaction transactions,
                t_clips_transaction_code transactionscd
            where
                tire.tire_trn_number = transactions.tire_trn_number and
                transactions.tran_code = transactionscd.tran_code and 
                redirect_operation_center_id = :opp and
                tire.tire_status_id = :stcode  and
                tire.wip_account = :wip and
                tire.tire_date > :reportfromdt and
                tire.tire_date < :reporttodt and
            order by transactions.tire_trn_number,tran_seq
         )
where dr between :start and :end' using 
in_noperationcenterid,in_ntirestatuscode,in_cwipaccount,v_tempstart,v_tempend;

end sp_clips_reports_soandso;
/
show err;
no errors.
sql> var out_cvgenric refcursor;
sql> var out_nstatuscode  number; 
sql> declare
  2  out_cerrordesc varchar2(2000) ;
  3  --var out_nrecordcnt number ;
  4  begin
  5  sp_clips_reports_soandso(4,'16-feb-02', 
'16-feb-03',null,2,'0293450720',1,10,:out_nrecordcnt, :out_nstatuscode 
,:out_cvgenric,out_cerrordesc);
  6  dbms_output.put_line(out_cerrordesc);
  7  end ;
  8  /
declare
*
error at line 1:
ora-00936: missing expression
ora-06512: at "CLIPStest2.sp_clips_reports_soandso", line 40
ora-06512: at line 5

В представленном выше коде запрос вынесен в строку, процедура успешно скомпилирована. Но при вызове возникают ошибки.

Если удалить условие "tire.tire_date > :ReportFromDt and tire.tire_date < :ReportToDt" из конструкции WHERE, запрос работает нормально и дает результаты. Если в запросе указаны даты, все ломается.

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

Не мог бы ты предложить решение?

Ответ Тома Кайта

1) Cм. это обсуждение

та же проблема, и тот же способ решения в 8i и более ранних версиях - динамический sql или представление.

2) Зачем вы считаете - это очень, очень плохая идея. Во-первых, ответ может изменИться, и измЕнится (ваше значение - лишь "предположение"). Во-вторых, так проще всего загрузить систему до полной остановки. "Ой, я все время делаю одно и то же, и так долго". Пора увеличивать производительность машины вдвое...

У вас ошибка в sql-операторе. Я сразу увидел две проблемы:

                tire.tire_date < :reporttodt and
            order by transactions.tire_trn_number,tran_seq

AND ORDER BY - тут выражения после and не хватает.

Во вторых, не хватает пары связываемых переменых. В списке using - пять переменных, а связывать надо 7.

Проблема не в датах - запрос просто неправильный.

Совет (я так и нашел эти ошибки) - скопируйте запрос в sqlplus, замените в запросе глобально '' на ' и выполните его после команд variable:

ops$tkyte@ORA920> variable opp varchar2(20)
ops$tkyte@ORA920> variable stcode varchar2(20)
ops$tkyte@ORA920> variable wip varchar2(20)
ops$tkyte@ORA920> variable reportfromdt varchar2(20)
ops$tkyte@ORA920> variable reporttodt varchar2(20)
ops$tkyte@ORA920> variable start varchar2(20)
ops$tkyte@ORA920> variable end varchar2(20)
ops$tkyte@ORA920> select *
  2      from (
  3    select tire.tire_trn_number tiretrnnumber,
  4                      to_char(tire.tire_date,'mm/dd/yy'),
  5                      tire.tire_time,
  6                      tire.direct_submitter_name user_name,
  7                  dense_rank() over ( order by tire.tire_trn_number ) dr
  8              from    t_clips_tire tire,
  9                  t_clips_afs_transaction transactions,
 10                  t_clips_transaction_code transactionscd
 11              where
 12                  tire.tire_trn_number = transactions.tire_trn_number and
 13                  transactions.tran_code = transactionscd.tran_code and
 14                  redirect_operation_center_id = :opp and
 15                  tire.tire_status_id = :stcode  and
 16                  tire.wip_account = :wip and
 17                  tire.tire_date > :reportfromdt and
 18                  tire.tire_date < :reporttodt and
 19              order by transactions.tire_trn_number,tran_seq
 20           )
 21  where dr between :start and :end
 22  /
            order by transactions.tire_trn_number,tran_seq
            *
ERROR at line 19:
ORA-00936: missing expression

Теперь ясно, где ошибка.

Разбиение на страницы при поиске по разным полям

У меня есть форма с 18 полями, по которым можно делать поиск. В PL/SQL я формирую окончательный запрос с помощью динамического SQL и rвозвращаю результаты (1-10, 11-20), вставляя их во временную таблицу. Но при использовании этого метода производительность снижается, поскольку запрос выполняется каждый раз. Как реализовать постраничный просмотр при использовании динамического sql.

Ответ Тома Кайта

ВАШ_ЗАПРОС можно формулировать как динамический sql.

Каждый раз, когда вы выполняете поиск на сайте asktom именно так и делается. Динамически, плюс фокус с rownum.

Комментарий читателя от 7 апреля 2003 года

Я - АБД и иногда сталкиваюсь с проблемами при поддержке web-приложений.

Разработчики web-приложений спросили меня, как реализовать постраничную выдачу, поскольку при подключении не сохраняется информация о состоянии.

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

select * from
  (select b.*,rownum rnum
   from (Select * from big_table a order by a.id) b
   where rownum < :max )
where rnum >= :min ;

1) Насколько я знаю, каждый раз при этом происходит повторный разбор, повторное выполнение и повторная выборка данных. Значения связываемых переменных сохраняются и увеличиваются для каждой страницы приложения. Это хороший подход?

2) Не будет ли лучше возвращать все множество (с оптимизацией first_rows)?

3) Как это реализовать?

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

Ответ Тома Кайта

1) Да, я тоже так делаю. Не хватает постоянного подключения и работы как в среде клиент-сервер, а что делать...

Я лично лишний раз выполню разбор (мягкий) для каждой страницы, чем буду поддерживать отдельное физическое подключение (со всеми соотвествующими ресурсами) для каждого пользователя на случай, если ЕМУ ЗАХОЧЕТСЯ посмотреть следующую страницу.

2) и вы получите 500 строк, а пользователь посмотрит первых 10 и никогда не перейдет на вторую страницу... Так что вы выполнили в 50 раз больше ввода-вывода (LIO), чем нужно? Скорее всего, раз в 40 больше, чем понадобится когда-либо (страинцы размером 10 строк, а пользователь НИКОГДА на страницу 11 не дойдет).

Надо делать как можно меньше, помня, что людям надоедает искать и они уходят после просмотра одной-двух страниц.

3) Делайте сами...

4) Это означало бы возврат к архитектуре клиент-сервер, с постоянным подключением, пожирающим массу ресурсов машины даже когда они не используются.

Комментарий читателя от 8 апреля 2003 года

Я только что изменил массу кода, удаляя все выборки count(*) перед выполнением фактического запроса - это мне досталось от прежнего разработчика.

Удивительные аргументы в пользу этого я услышал от web- (PHP) разработчика... Я просто все поменял и дал пользователям решать, понравится ли им более высокая производительность больше, чем отсутствие ненужной информации. Угадайте, что они выбрали!

Чего не хватает, так это строки "результаты 1-10 из, примерно, 500" - это пригодилось бы. Пользователю хотелось бы знать, осталось ли всего лишь несколько записей, - тогда стоит перейти на следующую страницу, или их там тысячи, так что лучше уточнить критерий поиска.

Я знаю, что компонент Oracle Text позволяет такие вещи сделать, но нельзя ли что-то придумать в "стандартном" Oracle? Для использования Oracle Text придется многое в системе переписывать.

Можно выбирать из приложения 21 строку данных. Если курсор вернул записи 10-20, на экран можно выдать ">> еще минимум 7 строк" (например), а при возврате 21-ой выдать ">> еще минимум 11 строк".

Followup:

...
Чего не хватает, так это строки "результаты 1-10 из, примерно, 500"
...

Если использовать запросы Oracle Text (как я делаю у себя на сайте), то там есть соответствующие функции.

Если используется стоимостной оптимизатор сервера 9i, то можно получить примерное количество строк, которое вернет запрос, в представлении v$sql_plan...

Постраничная выдача в web-среде

Ниже приведен код (спецификация и тело) пакета посраничной выдачи, который мы собираемся использовать в web-среде. Нельзя ли улучшить эту реализацию?

... тут был явный мусор, см. ответ Тома Кайта и оригинал обсуждения на его сайте...

Да, парень, кое-что у тебя сделано неправильно.

a) Продолжай НЕ использовать связываемые переменные. Лучший способ угробить любое приложение.

b) Ты пишешь процедурный код там, где ДОСТАТОЧНО одного sql-оператора.

c) Эта процедурная обработка ведется построчно.

d) Ты поместил даннные во временнуцю таблицу по совсем уж непонятной причине.

e) Ты считаешь общее количество строк по одной - в предыдущем ответе написано, что я об этом подходе думаю (я его неприемлю в принципе).

Если все это исправить, получим:

ops$tkyte@ORA920> create or replace context my_ctx using my_pkg
  2  /

Context created.

ops$tkyte@ORA920> create or replace package my_pkg
  2  as
  3      type rc is ref cursor;
  4
  5      procedure search_emp( p_ename in varchar2 default null,
  6                            p_hiredate in date default null,
  7                            p_sal in number default null,
  8                            p_start_row in number,
  9                            p_end_row in number,
 10                            p_rc in out rc );
 11  end;
 12  /

Package created.

ops$tkyte@ORA920> create or replace package body my_pkg
  2  as
  3  procedure search_emp( p_ename in varchar2 default null,
  4                        p_hiredate in date default null,
  5                        p_sal in number default null,
  6                        p_start_row in number,
  7                        p_end_row in number,
  8                        p_rc in out rc )
  9  is
 10      l_query long := 'select *
 11                         from ( select a.*, rownum r
 12                                  from ( select /*+ FIRST_ROWS */ ename, empno, job, sal
 13                                           from emp where 1=1 ';
 14  begin
 15
 16      if ( p_ename is not null )
 17      then
 18          dbms_session.set_context('my_ctx', 'ename', upper(p_ename) // '%');
 19          l_query := l_query // ' and ename like sys_context("my_ctx","ename") ';
 20      end if;
 21      if ( p_hiredate is not null )
 22      then
 23          dbms_session.set_context( 'my_ctx', 'hiredate', to_char(p_hiredate,'yyyymmddhh24miss') );
 24          l_query := l_query //
 25              ' and hiredate = to_date(sys_context("my_ctx","hiredate"),"yyyymmddhh24miss") ';
 26      end if;
 27      if ( p_sal is not null )
 28      then
 29          dbms_session.set_context( 'my_ctx', 'sal', p_sal );
 30          l_query := l_query // ' and sal > to_number( sys_context( "my_ctx", "sal" ) )';
 31      end if;
 32
 33      l_query := l_query     // ' ) a where rownum <= :max_row ) where r >= :min_row';
 34
 35      open p_rc for replace( l_query, '"', '''' ) using p_end_row, p_start_row;
 36  end;
 37
 38  end;
 39  /

Package body created.

ops$tkyte@ORA920> variable x refcursor
ops$tkyte@ORA920> set autoprint on
ops$tkyte@ORA920> exec my_pkg.search_emp(p_start_row => 4, p_end_row => 8, p_rc => :x);

PL/SQL procedure successfully completed.

ENAME           EMPNO JOB              SAL          R
---------- ---------- --------- ---------- ----------
JONES            7566 MANAGER         2975          4
MARTIN           7654 SALESMAN        1250          5
BLAKE            7698 MANAGER         2850          6
CLARK            7782 MANAGER         2450          7
SCOTT            7788 ANALYST         3000          8

ops$tkyte@ORA920> exec my_pkg.search_emp( p_ename => 'B', p_start_row => 1, 
p_end_row => 2, p_rc => :x );

PL/SQL procedure successfully completed.

ENAME           EMPNO JOB              SAL          R
---------- ---------- --------- ---------- ----------
BLAKE            7698 MANAGER         2850          1

ops$tkyte@ORA920> exec my_pkg.search_emp( p_hiredate => '09-dec-1982', 
p_start_row => 1, p_end_row => 2, p_rc => :x );

PL/SQL procedure successfully completed.


ENAME           EMPNO JOB              SAL          R
---------- ---------- --------- ---------- ----------
SCOTT            7788 ANALYST         3000          1

ops$tkyte@ORA920> exec my_pkg.search_emp( p_sal => 5, p_start_row => 5, 
p_end_row => 9, p_rc => :x );

PL/SQL procedure successfully completed.

ENAME           EMPNO JOB              SAL          R
---------- ---------- --------- ---------- ----------
MARTIN           7654 SALESMAN        1250          5
BLAKE            7698 MANAGER         2850          6
CLARK            7782 MANAGER         2450          7
SCOTT            7788 ANALYST         3000          8
KING             7839 PRESIDENT       5000          9

Именно эта процедура и используется на твоем сайте?

Если ты связываешь переменные в сеансе, а для http-подключения информация о состоянии не поддерживается, как же ы передаешь даные?

Ответ Тома Кайта

Да, именно такая процедура на нем и используется...

"Связываемые переменные", разумеется, передаются с одной страницы на другую - я использую для этого sessionid (это большое число в адресе URL). Для меня и "состояние" сеанса - просто строка в таблице.

Скрытые поля, ключики - тоже подойдут.


Страница сайта http://test.interface.ru
Оригинал находится по адресу http://test.interface.ru/home.asp?artId=23932