Продовжую тему оптимізації запитів ORACLE, хотілося б торкнутися циклів по SQL запитам в PLSQL.
Можливо, багатьом це вже відомо, але все ж.
Найчастіше запити в plsql пишуться в такий спосіб:
У такого підходу є істотний мінус - для кожного запису з SELECT, ORACLE доводиться міняти контекст виконання з SQL на PLSQL.
Такого роду операцію можна виконати без зміни контексту 2 способами:
* UPDATE без циклу - в найпростішому варіанті ТАК, але не всі цикли так прості.
* Використовувати BULK COLLECT і FORALL
З BULK COLLECT і FORALL запит вийде наступний:
Розберемо код:
* Створюємо Тип "employee_ids_t" - це асоціативний масив, де ключ PLS_INTEGER, а значення = employees.employee_id% TYPE
* L_employee_ids - це змінна типу employee_ids_t
* BULK COLLECT INTO - помістити відібрані select ідентифікатори в колекцію l_employee_ids. Поміщаються всі записи разом, на відміну від звичайного INTO (один запис)
* Конструкція FORALL - виконує UPDATE стільки раз, скільки записів в колекції l_employee_ids і використовуючи дані з неї.
Варто зауважити, що FORALL це не цикл, а конструкція мови, так що він має ряд особливостей:
* Всередині FORALL може бути тільки 1 DML запит. Якщо потрібно кілька запитів, то потрібно використовувати кілька FORALL
* При виконанні FORALL не відбувається перемикання контексту. Весь UPDATE виконується за 1 раз, що дає значну перевагу в швидкості.
Варто зауважити, що час на зміну контексту також витрачається при виклику функцій користувача в DML запитах.
Наприклад:
* Контекст буде змінений стільки разів, скільки записів відбереться в SELECT
Щоб уникнути зміни контексту, можна:
* Спробувати оголосити функцію як INLINE "PRAGMA INLINE"
* Або як використовувану тільки в DML "PRAGMA UDF"
* Але найкращий варіант в даному випадку - відмовитися від функції і свідомо денормалізіровать запит до чистого SQL.