Вибір проміжних записів з результуючого безлічі, Інші СУБД, Бази даних, статті

Ця стаття присвячена вибору записів з результуючого безлічі по частинах. Наприклад, для розміщення на 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 (

+ ” 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

В кінцевому підсумку, вирішувати вам. Я тільки можу показати, що мій варіант працює в кілька разів швидше. Вибирайте …


У вашому випадку, можу припустити наступне:



У загальному випадку, я хочу сказати ось що:


Використання “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


Привіт, Том

Представлений тобою запит дуже хороший і працює швидко в діапазоні від 100 000 до 150000 рядків, але при спробі вибірки рядків після 500 000-й він працює більше хвилини.


Запит:

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) По одному з первинних ключів. Не міг би ти порадити, як підвищити продуктивність в цьому випадку???


Відповідь Тома Кайта


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 😡 := 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 😡 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.

Помилка при використанні дат. Коментар від 2 березня 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 рядків” (наприклад), а при поверненні двадцять перше видати “>> ще мінімум 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 => 😡 );
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 => 😡 );
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 => 😡 );
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). Для мене і “стан” сеансу – просто рядок у таблиці.


Приховані поля, ключики – теж підійдуть.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*