До речі, всім, хто цікавиться хочу повідомити, що сьогодні переклад другої частини книги Тома Кайта "Expert one-on-one: Oracle" закінчений. Останні файли додатків сьогодні вранці відправлені до видавництва.
Як видавати результат посторінково?
Я хотів би вибирати дані після з'єднання трьох таблиць і сортування по деякому полю. Оскільки цей запит повертає приблизно 100 записів, я хотів би розбити результуюче безліч на 4 частини, по 25 записів кожна. І я хотів би пронумерувати записи. Чи можна це зробити в SQL * Plus?
Відповідь Тома Кайта
У Oracle8i, release 8.1 - да.
І все. У версіях до 8.0 включно це не спрацює.
Чудовий запит. Я просто хотів упевнитися, що розумію його. Запит виконується 4 рази і кожен раз максимальний і мінімальний номера рядків змінюються. Правильно?
Відповідь Тома Кайта
Так, значення min і max змінюються, щоб отримувати різні діапазони рядків.
А як щодо between?
Повернемося до старої дискусії про відмінність між запитом
Я стверджую, що, з точки зору продуктивності, вони ідентичні. Справді, план виконання першого запиту:
Здається більш швидким, ніж
Але, зверніть увагу, що всі кроки плану - не блокуватимуть. Тому не має значення, яке умова перевіряється раніше.
Відповідь Тома Кайта
Будь ласка, не треба стверджувати - ставте експерименти і доводить (давайте, я ж завжди так роблю).
Ваш перший запит, "where rownum between 90 and 100" ніколи не повертає НІЯКИХ даних. Ця умова - завжди помилково, завжди.
Я вже довів, відповідаючи на інше запитання (мабуть, теж ваш), що:
працює швидше, ніж:
Саме це, я сподіваюся, ви ЗБИРАЛИСЯ набрати. Все пов'язано зі способом виконання COUNT (STOPKEY) і тим фактом, що ми повинні виконати:
А ПОТІМ застосувати фільтр, тоді як в першому випадку ми вибираємо перші 100 рядків І ВСЕ.
Отже, нехай є непроіндексованої таблиця:
(Вона отримана в результаті триразового копіювання всього вмісту уявлення all_objects). Я виконаю до неї три запиту. Ваш, щоб показати, що він не працює (не повертає дані), той, який, думаю, ви мали на увазі, і мій варіант:
Тепер запит, який я всім рекомендую виконувати:
Велика різниця. Готовий посперечатися.
Твердження - вони кому не потрібні.
Тести, експерименти, статистика - мені вони подобаються, вони мені потрібні.
Спасибі, Том. Я, нарешті, помітив, що в однієї умови використовується rownum. а в іншому - rnum. і що вони відрізняються :-)
Краще рішення, яке я сам придумав:
Воно і працює повільніше, і явно менш елегантно :-(
Вибач, але я не бачу різниці:
Відповідь Тома Кайта
Ось що вам потрібно використовувати.
виконує ваш запит, вибирає перші 100 рядків результату і зупиняється. Якщо ВАШ_ЗАПРОС повинен отримати всі рядки, перш ніж зможе повернути першу (наприклад, включає конструкції типу group by), то відмінність у вашому випадку може виявитися невеликим, але воно є. Використовуйте TKPROF. щоб відсіяти час Рябота java-коду (завмер часу на клієнті подібним чином може сильно спотворювати результати).
Розглянемо наступний приклад:
Тут нам не потрібно отримувати останній рядок, перш ніж повертати першу - все працює дуже "швидко"
Тепер давайте додамо функцію агрегування - тепер доведеться обробити всі рядки в таблиці. ОДНАК оскільки rownum винесено якомога глибше, можна прискорити роботу за рахунок певних оптимізацій
Нарешті, зробимо по-вашому - ми не виносимо rownum якомога глибше, оптимізувати нічого не можна, і запит працює дійсно повільно
В кінцевому підсумку, вирішувати вам. Я тільки можу показати, що мій варіант працює в кілька разів швидше. Вибирайте.
У вашому випадку, можу припустити наступне:
- hz_parties - це уявлення (дізнаюся)
- Це уявлення полжно отримати всі рядки, перш ніж з нього можна буде вибрати першу
- Ви вибіраеет не так вже й багато рядків (близько тисячі - вони все поміщаються в оперативній пам'яті)
- Оптимізація rownum в вашому випадку багато чого не дає - за допомогою tkprof ви зможете з'ясувати, що саме вона дає.
У загальному випадку, я хочу сказати ось що:
Використання "where rnum between a and b" буде помилкою, якщо можна винести rownum у внутрішній запит і отримати феноменальний, в загальному випадку, підвищення продуктивності. Але вибирати вам.
Перший запит, поданий нижче, працює вдвічі швидше, ніж другий. Чи не могли б ви пояснити причину?
Відповідь Тома Кайта
Удвічі швидше. Дивно. Не знаю, що це означає.
Можу тільки сказати, що (після виправлення ваших запитів) я отримав інші результати. У моєму випадку, коли в таблиці big_table - близько 1000000 рядків, я отримав:
Другий запит більш ефективний, ніж перший.
Відповідь Тома Кайта
Якщо вибирати треба ліже до онцу, я б порадив отримувати результуючий безліч в іншому порядку, і вибирати ближче до початку (поміняти порядок сортування даних).
Так, останніх N рядків, в загальному випадку, вибирати довше, ніж перше N (не завжди, але цього цілком можна очікувати)
Я спробував виконати такий запит до впорядкованого поданням, в якому близько 7000 рядків мають значення eventseverity 64.
Але в результаті отримав лише 234 рядки.
Якщо виконати вкладений запит
я отримую 500 рядків зі значеннями RNUM від 1 до 500
Я не розумію, де облажався :-( Порадь що-небудь
Відповідь Тома Кайта
Я ненавиджу уявлення з конструкцією order by. Додавайте order by в запит. Для складних запитів порядок даних в уявленнях підтримувати не обов'язково. Швидше за все, значення rownum десь втрачається, але при відсутності відтвореного тесту я не можу сказати точно, що відбувається.
Представлений тобою запит дуже хороший і працює швидко в діапазоні від 100000 до 150000 рядків, але при спробі вибірки рядків після 500000-й він працює більше хвилини.
при виконанні зі значеннями 150001 і 150010, дає наступний результат і план
А при виконанні зі значеннями 1000001 та 1000010 результат і план показані нижче:
Чи не можна прискорити процес отримання останніх рядків?
Може, краще додати конструкцію order by DESC і отримати першу сторінку?
Я активно використовую твій запит при роботі з базою даних, читаючи по 1000 записів. При 100000 записів (це цілком нормально, як кажуть мені користувачі :-)), вибірка перші 1000 рядків займає 50 секунд :-(. (Сервер працює на ОС Solaris, а для доступу до бази використовується інтерфейс JDBC). Я виконую сортування (order by) по одному з первинних ключів. Чи не міг би ти порадити, як підвищити продуктивність в цьому випадку.
Відповідь Тома Кайта
Використовуйте цей засіб (sql_trace + TIMED_STATISTICS) для отримання плану виконання запиту, кількості рядків, оброблених на кожному кроці плану, і на основі цієї інформації починайте налаштування.
Можливо, необхідно виконувати оптимізацію в режимі FIRST_ROWS.
До речі, чому 1000 рядків, - 25 або 100 буде більш зрозуміло. Але, в будь-якому випадку, вам, швидше за все, доводиться сортувати кожен раз 100000 рядків. Перевірте також розмір sort_area size.
Чи доступні курсори з прокруткою (9.2) в pl / sql і jdbc, або тільки в pro c / c ++?
Якщо немає, коли ця можливість з'явиться в pl / sql?
Відповідь Тома Кайта
Вони є в jdbc.
Я не уявляю собі ситуацію, в якій вони потрібні / бажані в plsql. Вони корисні для підключень в середовищі клієнт / сервер, коли необхідно переглядати результуюче безліч посторінково, але plsql для цього середовища не призначений - він працює на сервері. Ми припускаємо, що це буде робити клієнт (наприклад, forms або jdbc). Коли в збереженій процедурі треба "повертатися" до оброблених рядків?
Як повертати безліч n з кожної групи записів по ключу, наприклад, для таких даних.
Я хочу повертати два записи з кожної групи по полю store - по два записи для кожного магазину.
дасть необхідний результат.
Я вирішив провести подальше дослідження запропонованого тобою підходу. А якщо хтось захоче отримати записи з M-го по N-ую з Dept. а не з Emp:
Ну, цю рибку можна засмажити і по-іншому.
Аналітичні функції - ось в чому сила:
При спробі реалізації одного з твоїх методів у мене виникли проблеми.
Див. Наступний код:
Я вирішив цю проблему, винісши запит в рядок (OPEN out_cvGenric FOR 'select * from.') І задавши конструкцію USING. Все працює чудово.
З чим пов'язана ця помилка?
Див. Наступний код. Це моя реальна реалізація твого методу в PL / SQL:
У представленому вище коді запит винесено в рядок, процедура успішно скомпільована. Але при виклику виникають помилки.
Якщо видалити умова "tire.tire_date>: ReportFromDt and tire.tire_date <:ReportToDt " из конструкции WHERE. запрос работает нормально и дает результаты. Если в запросе указаны даты, все ломается.
До речі, це розбиття на сторінки в збереженій процедурі істотно полегшить роботу сервера додатків. Але я, на жаль, не можу вирішити другу проблему.
Чи не міг би ти запропонувати рішення?
Відповідь Тома Кайта
та ж проблема, і той же спосіб вирішення в 8i і більш ранніх версіях - динамічний sql або уявлення.
2) Навіщо ви вважаєте - це дуже, дуже погана ідея. По-перше, відповідь може змінилося, і змінилося (ваше значення - лише "припущення"). По-друге, так найпростіше завантажити систему до повної зупинки. "Ой, я весь час роблю одне і те ж, і так довго". Пора збільшувати продуктивність машини вдвічі.
У вас помилка в sql-операторі. Я відразу побачив дві проблеми:
AND ORDER BY - тут вираження після and бракує.
По-друге, не вистачає пари пов'язуються мінливих. У списку using - п'ять змінних, а пов'язувати треба 7.
Проблема не в датах - запит просто неправильний.
Рада (я так і знайшов ці помилки) - скопіюйте запит в sqlplus. замініть в запиті глобально '' на 'і виконайте його після команд variable:
Тепер ясно, де помилка.
Розбиття на сторінки при пошуку по різних полях
Відповідь Тома Кайта
ВАШ_ЗАПРОС можна формулювати як динамічний sql.
Кожен раз, коли ви виконуєте пошук на сайті asktom саме так і робиться. Динамічно, плюс фокус з rownum.
Я - АБД і іноді стикаюся з проблемами при підтримці web-додатків.
Розробники web-додатків запитали мене, як реалізувати посторінкову видачу, оскільки при підключенні не зберігається інформація про стан.
Я хотів би виконувати запит тільки один раз, але довелося створити щось на кшталт такого, що призводить до повторного розбору, повторного виконання і повторної вибірці для кожної сторінки:
1) Наскільки я знаю, кожен раз при цьому відбувається повторний розбір, повторне виконання і повторна вибірка даних. Значення пов'язують змінних зберігаються і збільшуються для кожної сторінки додатка. Це хороший підхід?
2) Чи не буде краще повертати все безліч (з оптимізацією first_rows)?
3) Як це реалізувати?
4) Використовуючи другий підхід, чи не можна організувати свого роду конвеєр, щоб рядки поверталися з додатком поступово з одного запиту, але не всі безліч відразу - таблиця занадто велика.
Відповідь Тома Кайта
1) Так, я теж так роблю. Бракує постійного підключення і роботи як в середовищі клієнт-сервер, а що робити.
Я особисто зайвий раз виконаю розбір (м'який) для кожної сторінки, ніж буду підтримувати окреме фізичне підключення (з усіма соотвествующих ресурсами) для кожного користувача на випадок, якщо ЙОМУ ЗАХОЧЕТЬСЯ подивитися наступну сторінку.
2) і ви отримаєте 500 рядків, а користувач подивиться перших 10 і ніколи не перейде на другу сторінку. Так що ви виконали в 50 разів більше введення-виведення (LIO), ніж потрібно? Швидше за все, раз в 40 більше, ніж знадобиться коли-небудь (страінци розміром 10 рядків, а користувач НІКОЛИ на сторінку 11 не дійде).
4) Це означало б повернення до архітектури клієнт-сервер, з постійним підключенням, що пожирає масу ресурсів машини навіть коли вони не використовуються.
Я тільки що змінив масу коду, видаляючи всі вибірки count (*) перед виконанням фактичного запиту - це мені дісталося від колишнього розробника.
Дивовижні аргументи на користь цього я почув від web- (PHP) розробника. Я просто все поміняв і дав користувачам вирішувати, чи сподобається їм більш висока продуктивність більше, ніж відсутність непотрібної інформації. Вгадайте, що вони вибрали!
Чого не вистачає, так це рядки "результати 1-10 з, приблизно, 500" - це знадобилося б. Користувачеві хотілося б знати, чи залишилося всього лише кілька записів, - тоді варто перейти на наступну сторінку, або їх там тисячі, так що краще уточнити критерій пошуку.
Я знаю, що компонент Oracle Text дозволяє такі речі зробити, але чи не можна щось придумати в "стандартному" Oracle? Для використання Oracle Text доведеться багато в системі переписувати.
Можна вибирати з програми 21 рядок даних. Якщо курсор повернув записи 10-20, на екран можна видати ">> ще мінімум 7 рядків" (наприклад), а при поверненні 21-ої видати ">> ще мінімум 11 рядків".
Чого не вистачає, так це рядки "результати 1-10 з, приблизно, 500"
.
Якщо використовувати запити Oracle Text (як я роблю у себе на сайті), то там є відповідні функції.
Якщо використовується вартісної оптимізатор сервера 9i, то можна отримати приблизну кількість рядків, яке поверне запит, в поданні v $ sql_plan.
Посторінкова видача в web-середовищі
Нижче наведено код (специфікація і тіло) пакета посранічной видачі, який ми збираємося використовувати в web-середовищі. Чи не можна поліпшити цю реалізацію?
Так, хлопець, дещо у тебе зроблено неправильно.
a) Продовжуй НЕ використовувати зв'язуються змінні. Кращий спосіб угробити будь-який додаток.
b) Ти пишеш процедурний код там, де ДОСИТЬ одного sql-оператора.
c) Ця процедурна обробка ведеться за рядком.
d) Ти помістив Данн у временнуцю таблицю по зовсім вже незрозумілої причини.
e) Ти вважаєш загальна кількість рядків по одній - в попередній відповіді написано, що я про це підході думаю (я його неприйнятний в принципі).
Якщо все це виправити, отримаємо:
Саме ця процедура і використовується на твоєму сайті?
Якщо ти зв'язуєш змінні в сеансі, а для http-підключення інформація про стан не підтримується, як же и передаєш дані?
Відповідь Тома Кайта
Так, саме така процедура на ньому і використовується.
Приховані поля, ключики - теж підійдуть.