Тимчасові таблиці в Oracle

Відгуки (0) | Трекбеки (0)

Тимчасові таблиці вперше з'явилися в Oracle8I. Вони призначалися для зберігання даних протягом сеансу або транзакції. Відмінною особливістю цих таблиць було те, що вони розташовувалися в тимчасових сегментах і дані в цих таблицях зберігалися тільки на період сесії або транзакції в залежності від реалізації. Тому вони знайшли велике застосування в якості проміжних таблиць при розрахунках, звітах і оптимізації складних запитів.

Для створення тимчасових таблиць використовується оператор CREATE GLOBAL TEMPORARY TABLE з ключовими словами ON COMMIT PRESERVE ROWS (зберігання даних на час сеансу) або ON COMMIT DELETE ROWS (зберігання даних на час транзакції).

Як приклад спробуємо створити тимчасову таблицю на сеанс і заповнити її даними: 
SQL> CONNECT TEST/*****@ALFA8174;
Підключення:
Oracle8i Enterprise Edition Release 8.1.7.0.0 – Production
With the Partitioning option
JServer Release 8.1.7.0.0 – Production
SQL> CREATE GLOBAL TEMPORARY TABLE table1 (id NUMBER (5), name VARCHAR2 (20))
ON COMMIT PRESERVE ROWS;
Таблиця створена
Витрачено часу: 0.141 секунд (и)
SQL> INSERT INTO table1 (id,name) VALUES(1,”items1″);
Вставлено: 1 рядок
Витрачено часу: 1.109 секунд (и) SQL> commit
Commit complete
Витрачено часу: 0.0 секунд (и)
SQL> SELECT * FROM table1 ID NAME
— ——
1 items1
Обрано: 1 рядок
Витрачено часу: 0.16 секунд (и)
SQL> DISCONNECT;
Відключення
SQL> CONNECT TEST/*****@ALFA8174;
Підключення SQL> SELECT * FROM table1; ID NAME
— —-
Обрано: 0 рядків
Витрачено часу: 0.0 секунд (и)
Видно, що після відключення сеансу, дані з таблиці видаляються. Тепер створимо і заповнимо тимчасову таблицю на час транзакції. 
SQL> CREATE GLOBAL TEMPORARY TABLE table2 (id NUMBER (5), name VARCHAR2 (20))
ON COMMIT DELETE ROWS;
Таблиця створена
Витрачено часу: 0.16 секунд (и)
SQL> INSERT INTO table2 (id,name) VALUES(1,”items1″);
Вставлено: 1 рядок
Витрачено часу: 0.0 секунд (и)
SQL> SELECT * FROM table2; ID NAME
— ——
1 items1
Обрано: 1 рядок
Витрачено часу: 0.0 секунд (и)
SQL> COMMIT;
Commit complete
Витрачено часу: 0.0 секунд (и)
SQL> SELECT * FROM table2; ID NAME
— —-
Обрано: 0 рядків
Витрачено часу: 0.16 секунд (и)
Дані з таблиці віддалилися відразу після завершення транзакції. Відмінною особливістю тимчасової таблиці в даному випадку є те, що дані таблиці не тільки віддаляються, а й не видно з інших сеансів. Тобто користувачі можуть одночасно використовувати одну й ту ж саму тимчасову таблицю, не перетинаючись даними. Звідси випливають деякі обмеження при роботі з тимчасовими таблицями. Так не можна блокувати таблицю за допомогою команди LOCK TABLE. Команда виконатися без помилок, але блокування не встановиться. 
SQL> LOCK TABLE table1 IN EXCLUSIVE MODE;
Table(s) locked
Витрачено часу: 0.0 секунд (и) SQL> SELECT sid, type FROM v $ lock WHERE sid = (SELECT sid FROM v $ session WHERE
audsid=to_char(userenv(“SESSIONID”))) SID TYPE
— —-
9 TO
Обрано: 1 рядок
Витрачено часу: 0.31 секунд (и)
Не можна додавати зовнішні ключі на тимчасову таблицю і навпаки. 
SQL> ALTER TABLE table1 ADD CONSTRAINT table1_fk FOREIGN KEY (id) REFERENCES
test.table3;
ALTER TABLE table1 ADD CONSTRAINT table1_fk FOREIGN KEY (id) REFERENCES
test.table3
*
Помилка в рядку 1:
ORA-14455: attempt to create referential integrity constraint on temporary
table SQL> ALTER TABLE table3 ADD CONSTRAINT table3_fk FOREIGN KEY (id) REFERENCES
test.table1;
ALTER TABLE table3 ADD CONSTRAINT table3_fk FOREIGN KEY (id) REFERENCES
test.table1
*
Помилка в рядку 1:
ORA-14454: attempt to reference temporary table in a referential integrity
constraint
Не підтримується так само перенесення тимчасової таблиці в інше табличний простір. 
SQL> ALTER TABLE table1 MOVE TABLESPACE TEMP;
ALTER TABLE table1 MOVE TABLESPACE TEMP
*
Помилка в рядку 1:
ORA-14451: unsupported feature with temporary table
Тимчасові таблиці при DML операціях не генерують інформації повторного виконання, так як спочатку створюються в режимі NOLOGGING (дивися Використання режиму NOLOGGING. Частина II). Але при цьому вони підтримують механізм відкоту змін, як і для звичайної таблиці. 
SQL> INSERT INTO table1 (id,name) VALUES(1,”items1″);
Вставлено: 1 рядок
Витрачено часу: 0.0 секунд (и) SQL> SELECT * FROM table1; ID NAME
— ——
1 items1
Обрано: 1 рядок
Витрачено часу: 0.0 секунд (и) SQL> ROLLBACK;
Rollback complete
Витрачено часу: 0.31 секунд (и) SQL> SELECT * FROM table1; ID NAME
— —-
Обрано: 0 рядків
Витрачено часу: 0.0 секунд (и)
До речі про це забувають, і звідси виникає упередження, що commit після заповнення тимчасової таблиці (для таблиці на сеанс) можна не ставити. Це звичайно призводить до проблем функціонування сегментів відкату. Дані, вміщені в сегмент відкоту після заповнення тимчасової таблиці та не фіксації змін транзакції, будуть перебувати там до відключення сеансу, перешкоджаючи хляпанню сегментів відкоту. Звідси висновок: не забувайте ставити commit для тимчасових таблиць. Для прискорення роботи з тимчасовими таблицями можна створювати індекси. Вони також розташовуються в тимчасовому табличному просторі і заповнюються при вставці даних у таблицю. Але якщо ви спробуєте створити індекс, після того як будь-якої сеанс уже вставив у неї дані, то вам видасться помилка: 
SQL> CREATE INDEX test.table1_ind ON test.table1 (id);
CREATE INDEX test.table1_ind ON test.table1 (id)
*
Помилка в рядку 1:
ORA-14452: attempt to create, alter or drop an index on temporary table
already in use
Це твердження вірно також для всіх DDL команд застосовуваних до тимчасової таблиці. Тепер розглянемо оптимізацію SQL при роботі з тимчасовими таблицями. Одним з помилок при роботі з тимчасовими таблицями є те, що можна проводити їх аналіз з метою зібрати статистику для оптимізатора. Тимчасова таблиця за своїм визначенням не може мати постійну статистику, отже, оптимізатор при побудові плану запиту будує план виконання виходячи не із статистики, а з припущення за замовчуванням. До даного омані може підштовхнути безпомилкове виконання команди ANALYZE. Але, переглянувши виставу DBA_TABLES після виконання цієї команди, ми не виявимо статистики для даної таблиці. Більш чесно надходить в цьому випадку пакет dbms_stats, прямо повідомляючи нам, що не підтримує збір статистики для тимчасових таблиць. 
SQL> execute sys.dbms_stats.gather_table_stats (ownname => "test", tabname =>
"Table1", estimate_percent => 20, block_sample => TRUE, method_opt => "for all
indexed columns”,cascade => TRUE);
BEGIN
*
Помилка в рядку 1:
ORA-20000: Gathering statistics for a temporary table is not supported
ORA-06512: at “SYS.DBMS_STATS”, line 4474
ORA-06512: at line 2
Розглянемо все це на прикладі. Для цього створимо індекс на таблицю table1, заповнимо даними, проаналізуємо її і виберемо майже всі записи, попередньо включивши виведення плану виконання. 
SQL> DECLARE
2> i INTEGER;
3> BEGIN
4> FOR i IN 1..10000 LOOP
5> INSERT INTO table1 (id,name) VALUES (i,”ITEM”//i);
6> END LOOP;
7> END;
PL/SQL procedure successfully completed
Витрачено часу: 3.843 секунд (и) SQL> COMMIT;
Commit complete
Витрачено часу: 0.0 секунд (и)
SQL> analyze table test.table1 compute statistics;
Table analyzed
Витрачено часу: 0.16 секунд (и) SQL> SET AUTOTRACE TRACE EXPLAIN;
SQL> select id from test.table1 where id < 999
Обрано: 998 рядків
Витрачено часу: 0.0 секунд (и)
План виконання:
————————————————–
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF TABLE1_IND (NON-UNIQUE)
Як видно, оптимізатор не видав жодної оцінки вартості і використовував припущення за замовчуванням. Іноді цього недостатньо. Але вихід в цьому випадку є. Насправді статистику для тимчасової таблиці можна встановити примусово, використовуючи для цього пакет dbms_stats. Спробуємо зробити це, отримавши статистику з аналогічною не тимчасової таблиці table3. 
SQL> analyze table table3 compute statistics;
Table analyzed
Витрачено часу: 0.313 секунд (и) SQL> DECLARE
2> sr sys.dbms_stats.statrec;
3> distcnt NUMBER;
4> density NUMBER;
5> nullcnt NUMBER;
6> avgclen NUMBER;
7> numrows NUMBER;
8> numlblks NUMBER;
9> numdist NUMBER;
10> avglblk NUMBER;
11> avgdblk NUMBER;
12> clstfct NUMBER;
13> indlevel NUMBER;
14> BEGIN
15> dbms_stats.get_column_stats ("test", "table3", "id", NULL, NULL, NULL,
distcnt,density,nullcnt,sr,avgclen,NULL);
16> dbms_stats.set_table_stats (ownname => "test", tabname =>
“table1”,numrows => distcnt,numblks => 25);
17> dbms_stats.set_column_stats (ownname => "test", tabname =>
"Table1", colname => "id", srec => sr, distcnt => distcnt);
18> dbms_stats.get_index_stats ("test", "TABLE3_ID_IND", NULL, NULL, NULL,
numrows,numlblks,numdist,avglblk,avgdblk,clstfct,indlevel,NULL);
19> dbms_stats.set_index_stats ("test", "TABLE1_IND", NULL, NULL, NULL,
numrows,numlblks,numdist,avglblk,avgdblk,clstfct,indlevel,NULL);
20> END;
PL/SQL procedure successfully completed
Витрачено часу: 0.16 секунд (и)
SQL> SELECT table_name, column_name, num_distinct, low_value, high_value FROM
dba_tab_col_statistics; TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE
———- ———– ———— ———- —————-
TABLE1 ID 10000 C102 C302
TABLE3 ID 10000 C102 C302
Обрано: 3 рядки
Витрачено часу: 0.468 секунд (и) 
SQL> SELECT owner, table_name, num_rows, blocks FROM dba_tables WHERE owner =
“TEST”; OWNER TABLE_NAME NUM_ROWS BLOCKS
—– ———- ——– ——
TEST TABLE1 10000 25
TEST TABLE3 10000 25
Обрано: 4 рядка
Витрачено часу: 0.0 секунд (и) SQL> SET AUTOTRACE TRACE EXPLAIN;
SQL> SELECT name FROM test.table1 where id > 9400;
Обрано: 600 рядків
Витрачено часу: 0.0 секунд (и)
План виконання:
————————————————–
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 4 Card = 601 Bytes = 15025)
1 0 TABLE ACCESS (FULL) OF TABLE1 (Cost=4 Card=601 Bytes=15025) 
SQL> SELECT name FROM test.table1 WHERE id > 9500;
Обрано: 500 рядків
Витрачено часу: 0.0 секунд (и)
План виконання:
————————————————–
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 4 Card = 501 Bytes = 12525)
1 0 TABLE ACCESS (BY INDEX ROWID) OF TABLE1 (Cost = 4 Card = 501 Bytes = 12525)
1 лютому INDEX (RANGE SCAN) OF TABLE1_IND (NON-UNIQUE) (Cost = 2 Card = 501) SQL> SET AUTOTRACE OFF;
Як показують плани виконання двох останніх запитів, статистика для таблиці table1 все ж таки була встановлена, і при цьому оптимізатор її використовував (правило 5%). Останнє що нам залишилося це розглянути, як виділяється і звільняється табличний простір для тимчасових таблиць. Виходячи зі свого визначення, тимчасової таблиці не можна заздалегідь виділити необхідний простір в часовому сегменті. Це робить сам Oracle при команді INSERT. Розглянемо, як це відбувається на прикладі. Для початку подивимося, скільки блоків всього і вільно в табличному просторі TTEMP, де розташована тимчасова таблиця table1. 
SQL> SELECT tablespace_name, bytes, blocks FROM dba_data_files WHERE
tablespace_name = “TTEMP”; TABLESPACE_NAME BYTES BLOCKS
————— ——- ——
TTEMP 5242880 640
Обрано: 1 рядок
Витрачено часу: 0.78 секунд (и)
SQL> SELECT tablespace_name, bytes, blocks FROM dba_free_space WHERE
tablespace_name = “TTEMP”; TABLESPACE_NAME BYTES BLOCKS
————— ——- ——
TTEMP 5234688 639
Обрано: 1 рядок
Витрачено часу: 0.15 секунд (и)
Табличний простір майже вільно. Тепер заповнимо таблицю table1. 
SQL> DECLARE
2> i INTEGER;
3> BEGIN
4> FOR i IN 1..1000 LOOP
5> INSERT INTO test.table1 (id,name) VALUES (i,”ITEM”);
6> END LOOP;
7> END;
PL/SQL procedure successfully completed
Витрачено часу: 0.390 секунд (и) SQL> COMMIT;
Commit complete
Витрачено часу: 0.0 секунд (и)
Екстенти для таблиці виділяються при виконанні операції вставки. Подивимося, скільки залишилося вільних блоків у табличному просторі. 
SQL> SELECT tablespace_name, bytes, blocks FROM dba_free_space WHERE
tablespace_name = “TTEMP”; TABLESPACE_NAME BYTES BLOCKS
————— ——- ——
TTEMP 5070848 619
Обрано: 1 рядок
Витрачено часу: 0.0 секунд (и)
Таблиця зайняла в TTEMP 20 блоків. Це 2 екстент. Перевіримо. 
SQL> SELECT tablespace_name, extent_id, blocks FROM dba_extents WHERE
tablespace_name = “TTEMP”; TABLESPACE_NAME EXTENT_ID BLOCKS
————— ——— ——
TTEMP 0 10
TTEMP 1 10
Обрано: 2 рядки
Витрачено часу: 0.16 секунд (и)
Для отримання більш точної інформації щодо використання табличного простору сеансами потрібно зробити запит до наступного поданням 
SQL> SELECT tablespace, segtype, extents, blocks FROM v $ sort_usage; 


TABLESPACE SEGTYPE EXTENTS BLOCKS
———- ——- ——- ——
TTEMP DATA 1 10
TTEMP INDEX 1 10
Обрано: 2 рядки
Витрачено часу: 0.16 секунд (и)
Отже, таблиця посіла 2 екстент по 10 блоків кожен, при цьому один екстент виділена під дані, інший під індекси. Якщо ми тепер зробимо DISCONNECT, то побачимо, що виділені екстенти під тимчасову таблицю table1 звільнилися. 
SQL> SELECT tablespace, segtype, extents, blocks FROM v $ sort_usage; 
TABLESPACE SEGTYPE EXTENTS BLOCKS
———- ——- ——- ——
Обрано: 0 рядків
Витрачено часу: 0.0 секунд (и)
Але в той же час ми бачимо, що кількість виділених екстент в табличному просторі не зменшилася. 
SQL> SELECT tablespace_name, bytes, blocks FROM dba_free_space WHERE
tablespace_name = “TTEMP”; 
TABLESPACE_NAME BYTES BLOCKS
————— ——- ——
TTEMP 5070848 619
Обрано: 1 рядок
Витрачено часу: 0.0 секунд (и) 
SQL> SELECT tablespace_name, extent_id, blocks FROM dba_extents WHERE
tablespace_name = “TTEMP”; 
TABLESPACE_NAME EXTENT_ID BLOCKS
————— ——— ——
TTEMP 0 10
TTEMP 1 10
Обрано: 2 рядки
Витрачено часу: 0.16 секунд (и)

Звідси може скластися помилкова думка, що екстенти не звільнені. Насправді, як я припускаю, інформація в словнику (а уявлення dba_free_space і dba_extents побудовані саме на ньому) змінюється для тимчасових табличних просторів тільки при першому виділення екстент. Це цілком може, пов'язане з великими накладними витратами з оновлення словника. Представлення v $ sort_usage навпаки побудовано на x $ таблиці і тому інформація в ньому більш правильна.


Висновки:


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

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

Метки: , , , , , ,
Рубрики: Інші СУБД

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

Ваш отзыв

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

*

*