![]() |
+7 (495) 229-0436 | ![]() |
shopadmin@itshop.ru | 119334, г. Москва, ул. Бардина, д. 4, корп. 3 | ![]() |
![]() |
![]() |
|
|
Выбор промежуточных записей из результирующего множества30.03.2010 16:11
Эта статья посвящена выбору записей из результирующего множества по частям. Например, для размещения на 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 секунды. Я интерпретирую это так: Ответ Тома Кайта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 В конечном итоге, решать вам. Я только могу показать, что мой вариант работает в несколько раз быстрее. Выбирайте... В вашем случае, могу предположить следующее:
В общем случае, я хочу сказать вот что: Использование "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:... Если использовать запросы 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). Для меня и "состояние" сеанса - просто строка в таблице. Скрытые поля, ключики - тоже подойдут. Ссылки по теме |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
О нас |
Интернет-магазин ITShop.ru предлагает широкий спектр услуг информационных технологий и ПО.
На протяжении многих лет интернет-магазин предлагает товары и услуги, ориентированные на бизнес-пользователей и специалистов по информационным технологиям. Хорошие отзывы постоянных клиентов и высокий уровень специалистов позволяет получить наивысший результат при совместной работе. В нашем магазине вы можете приобрести лицензионное ПО выбрав необходимое из широкого спектра и ассортимента по самым доступным ценам. Наши менеджеры любезно помогут определиться с выбором ПО, которое необходимо именно вам. Также мы проводим учебные курсы. Мы приглашаем к сотрудничеству учебные центры, организаторов семинаров и бизнес-тренингов, преподавателей. Сфера сотрудничества - продвижение бизнес-тренингов и курсов обучения по информационным технологиям.
|
119334, г. Москва, ул. Бардина, д. 4, корп. 3 +7 (495) 229-0436 shopadmin@itshop.ru |
|
© ООО "Interface Ltd." Продаем программное обеспечение с 1990 года |