1. Тимчасові таблиці Oracle
2. Визначення
• Тимчасові таблиці призначені для зберігання
даних протягом сеансу або транзакції.
Відмінною особливістю цих таблиць є те,
що вони розташовуються в тимчасових сегментах і
дані в цих таблицях зберігаються тільки на період
сесії або транзакції в залежності від використовуваної
при їх визначенні опції. Вони знаходять широке
застосування в якості проміжних таблиць при
розрахунках, звітах і оптимізації складних запитів.
• Для створення тимчасових таблиць використовується оператор
CREATE GLOBAL TEMPORARY TABLE з ключовими
словами ON COMMIT PRESERVE ROWS (зберігання даних
на час сеансу) або ON COMMIT DELETE ROWS
(Зберігання даних на час транзакції).
3. Вправа 1
4. Вправа 2
5. Примітки ...
• Дані з таблиці table2 пішли відразу
після завершення транзакції (опція on
commit delete rows). Відмінною
особливістю тимчасових таблиці є
то, що дані таблиць не тільки видаляються, але
і не видно з інших сеансів. Тобто
користувачі можуть одночасно
використовувати одну й ту ж саму тимчасову таблицю,
перетинаючись даними. Звідси випливають
деякі обмеження при роботі з
тимчасовими таблицями.
6. Обмеження для тимчасових таблиць
• Не можна додавати зовнішні ключі на тимчасову
таблицю і посилатися на неї як на батьківську.
• Не можна створювати індекси і виконувати інші DDL
оператори після того, як в таблиці вже з'явилися
дані.
• Тимчасова таблиця не може бути
партіціонірована або організована як
індексна.
• Не можна распараллеливать запити до тимчасових
таблиць.
• Розподілені транзакції не можуть працювати з
тимчасовими таблицями.
7. Можливості тимчасових таблиць
• Тимчасові таблиці можуть використовувати
правила цілісності (за винятком
посилальних правил цілісності).
• Тимчасові таблиці можуть супроводжуватися
індексами.
• Примітка: і ті й інші можуть додаватися
тільки тоді, коли в таблиці немає записів ні в
однієї сесії або транзакції.
CREATE GLOBAL TEMPORARY TABLE CITY_DEPT
(
DEPTNO NUMBER (2,0),
DNAME VARCHAR2 (14),
CONSTRAINT PK_CITY_DEPT PRIMARY KEY (DEPTNO)
)
ON COMMIT DELETE ROWS;
================================================== ==============
COMMENT ON COLUMN CITY_DEPT.DEPTNO IS 'DEPARTMENT NUMBER';
COMMENT ON COLUMN CITY_DEPT.DNAME IS 'DEPARTMENT NAME';
• ================================================= ===============
CREATE UNIQUE INDEX IDX_DEPTNO_DNAME ON CITY_DEPT (DEPTNO, DNAME);
CREATE INDEX IDX_DNAME ON CITY_DEPT (DNAME);
===============================================
9. Використання статистики при виконанні запитів до тимчасових таблиць
• Існують два види статистики
стосовно тимчасовим таблиць:
• SESSION - рівня клієнтської сесії
• SHARED - колективна між
клієнтськими сесіями
10. SESSION і SHARED-статистики
• SESSION статистика збирається і використовується
тільки під час поточної клієнтської сесії.
• Якщо одночасно існує два види
статистики (SESSION і SHARED), то
оптимізатор віддасть перевагу SESSIONстатістіке.
• SESSION статистика видаляється як тільки
закінчується сесія.
• SHARED статистика зберігається після
завершення сесії.
11. Який параметр відповідає за обраний тип статистики?
параметр - GLOBAL_TEMP_TABLE_STATS
Як дізнатися його значення:
SELECT DBMS_STATS.get_prefs ( 'GLOBAL_TEMP_TABLE_STATS')
FROM dual;
12. Вправа
• Уточніть в ORACLE APEX тип встановленої
статистики для тимчасових таблиць.
13. Як змінити тип статистики?
• BEGIN
DBMS_STATS.set_global_prefs
(Pname => 'GLOBAL_TEMP_TABLE_STATS',
pvalue => 'SHARED');
• END;
• /
• BEGIN
DBMS_STATS.set_global_prefs
(Pname => 'GLOBAL_TEMP_TABLE_STATS',
pvalue => 'SESSION');
• END;
• /
• Примітка: виконання цих операцій можливо
тільки при наявності відповідних привілеїв !!
14. Як зібрати статистику?
• DBMS_STATS.gather_table_stats ( '
• Примітка: виклик процедури gather_table_stats доступний простим
користувачам APEX
15. Де можна подивитися зібрану статистику?
• DBA_TAB_STATISTICS
• DBA_IND_STATISTICS
• DBA_TAB_HISTOGRAMS
DBA_TAB_COL_STATISTICS
• Дивитися можна при наявності
достатніх адміністративних
привілеїв ...
16. Як виглядає весь цикл використання тимчасових таблиць в процедурах і функціях?
• BEGIN
чистимо тимчасову таблицю
заповнюємо тимчасову таблицю даними (як правило,
агрегованими)
збираємо (SESSION) або НЕ збирає статистику (SHARED)
вибираємо дані з тимчасової таблиці
• END
• Примітка: при цьому у функції повинна бути
оголошена автономна транзакція.
17. Приклад
CREATE TYPE t_tf_row AS OBJECT (id NUMBER, description VARCHAR2 (50));
/
CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
/
create or replace function get_tab_ptf (p_rows in number)
return t_tf_tab pipelined
is PRAGMA AUTONOMOUS_TRANSACTION;
begin
-- чистимо тимчасову таблицю
execute immediate ( 'truncate table table1');
-- розміщуємо дані в таблиці
for i in 1..p_rows loop
insert into table1 (id, name) values (i, 'Description for' || i);
end loop;
-- збираємо статистику
dbms_stats.gather_table_stats ( 'GRAFEEVA', 'TABLE1');
-- формуємо результат
for rec in (select * from table1) loop
pipe row (t_tf_row (rec.id, rec.name));
end loop;
return;
end;
/
select * from table (get_tab_ptf (10))
18. Вправа (5 балів) Створіть функцію, яка видає результат такого вигляду на основі таблиці EMP (використовуйте тимчасові таблиці):
1980
CLERK
SALEMAN
MANAGER
PRESIDENT
Разом
1981
+1982
1 983
Разом
19. Домашнє завдання 13 (10 балів)
