Эта статья посвящена выбору записей из результирующего множества по частям. Например, для размещения на 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). Для меня и "состояние" сеанса - просто строка в таблице.
Скрытые поля, ключики - тоже подойдут.