Тимчасові таблиці в Oracle
Тимчасові таблиці вперше з'явилися в 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))Дані з таблиці віддалилися відразу після завершення транзакції. Відмінною особливістю тимчасової таблиці в даному випадку є те, що дані таблиці не тільки віддаляються, а й не видно з інших сеансів. Тобто користувачі можуть одночасно використовувати одну й ту ж саму тимчасову таблицю, не перетинаючись даними. Звідси випливають деякі обмеження при роботі з тимчасовими таблицями. Так не можна блокувати таблицю за допомогою команди LOCK TABLE. Команда виконатися без помилок, але блокування не встановиться.
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 секунд (и)
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;Тимчасові таблиці при DML операціях не генерують інформації повторного виконання, так як спочатку створюються в режимі NOLOGGING (дивися Використання режиму NOLOGGING. Частина II). Але при цьому вони підтримують механізм відкоту змін, як і для звичайної таблиці.
ALTER TABLE table1 MOVE TABLESPACE TEMP
*
Помилка в рядку 1:
ORA-14451: unsupported feature with temporary table
SQL> INSERT INTO table1 (id,name) VALUES(1,”items1″);До речі про це забувають, і звідси виникає упередження, що commit після заповнення тимчасової таблиці (для таблиці на сеанс) можна не ставити. Це звичайно призводить до проблем функціонування сегментів відкату. Дані, вміщені в сегмент відкоту після заповнення тимчасової таблиці та не фіксації змін транзакції, будуть перебувати там до відключення сеансу, перешкоджаючи хляпанню сегментів відкоту. Звідси висновок: не забувайте ставити commit для тимчасових таблиць. Для прискорення роботи з тимчасовими таблицями можна створювати індекси. Вони також розташовуються в тимчасовому табличному просторі і заповнюються при вставці даних у таблицю. Але якщо ви спробуєте створити індекс, після того як будь-якої сеанс уже вставив у неї дані, то вам видасться помилка:
Вставлено: 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 секунд (и)
SQL> CREATE INDEX test.table1_ind ON test.table1 (id);Це твердження вірно також для всіх DDL команд застосовуваних до тимчасової таблиці. Тепер розглянемо оптимізацію SQL при роботі з тимчасовими таблицями. Одним з помилок при роботі з тимчасовими таблицями є те, що можна проводити їх аналіз з метою зібрати статистику для оптимізатора. Тимчасова таблиця за своїм визначенням не може мати постійну статистику, отже, оптимізатор при побудові плану запиту будує план виконання виходячи не із статистики, а з припущення за замовчуванням. До даного омані може підштовхнути безпомилкове виконання команди ANALYZE. Але, переглянувши виставу DBA_TABLES після виконання цієї команди, ми не виявимо статистики для даної таблиці. Більш чесно надходить в цьому випадку пакет dbms_stats, прямо повідомляючи нам, що не підтримує збір статистики для тимчасових таблиць.
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
SQL> execute sys.dbms_stats.gather_table_stats (ownname => "test", tabname =>Розглянемо все це на прикладі. Для цього створимо індекс на таблицю table1, заповнимо даними, проаналізуємо її і виберемо майже всі записи, попередньо включивши виведення плану виконання.
"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
SQL> DECLAREЯк видно, оптимізатор не видав жодної оцінки вартості і використовував припущення за замовчуванням. Іноді цього недостатньо. Але вихід в цьому випадку є. Насправді статистику для тимчасової таблиці можна встановити примусово, використовуючи для цього пакет dbms_stats. Спробуємо зробити це, отримавши статистику з аналогічною не тимчасової таблиці table3.
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)
SQL> analyze table table3 compute statistics;Як показують плани виконання двох останніх запитів, статистика для таблиці table1 все ж таки була встановлена, і при цьому оптимізатор її використовував (правило 5%). Останнє що нам залишилося це розглянути, як виділяється і звільняється табличний простір для тимчасових таблиць. Виходячи зі свого визначення, тимчасової таблиці не можна заздалегідь виділити необхідний простір в часовому сегменті. Це робить сам Oracle при команді INSERT. Розглянемо, як це відбувається на прикладі. Для початку подивимося, скільки блоків всього і вільно в табличному просторі TTEMP, де розташована тимчасова таблиця table1.
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;
SQL> SELECT tablespace_name, bytes, blocks FROM dba_data_files WHEREТабличний простір майже вільно. Тепер заповнимо таблицю table1.
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 секунд (и)
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Таблиця зайняла в TTEMP 20 блоків. Це 2 екстент. Перевіримо.
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 секунд (и)
SQL> SELECT tablespace, segtype, extents, blocks FROM v $ sort_usage;Отже, таблиця посіла 2 екстент по 10 блоків кожен, при цьому один екстент виділена під дані, інший під індекси. Якщо ми тепер зробимо DISCONNECT, то побачимо, що виділені екстенти під тимчасову таблицю table1 звільнилися.TABLESPACE SEGTYPE EXTENTS BLOCKS
———- ——- ——- ——
TTEMP DATA 1 10
TTEMP INDEX 1 10
Обрано: 2 рядки
Витрачено часу: 0.16 секунд (и)
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 $ таблиці і тому інформація в ньому більш правильна.
Висновки:
- Фіксуйте транзакцію після заповнення тимчасової таблиці. Це запобіжить проблеми з сегментом відкату.
- Для прискорення роботи з великою тимчасової таблицею створюйте індекси.
- Не намагайтеся, збирайте статистику таблиці за допомогою команди ANALYZE. Це не має сенсу. Якщо статистика все ж потрібна, використовуйте пакет dbms_stats для її примусової установки.
- Не створюйте стовпці типу VARRAY або вкладені таблиці, а також зовнішні ключі.
- Ви не зможете перемістити таблицю в інше табличний простір за допомогою команди ALTER TABLE MOVE, а також самостійно заблокувати таблицю за допомогою команди LOCK TABLE.
- Не застосовуйте DDL команди, якщо таблицю вже використовують. Буде видаватися помилка. У деяких версіях Oracle є баги. У цьому випадку навіть якщо ніхто не використовує цю таблицю, застосувати DDL команду вам не вдасться. Допоможе тільки перезавантаження Oracle.
- Для перегляду виділеного тимчасового табличного простору по сеансам використовуйте подання v $ sort_usage.
Схожі статті:
- Горизонтальне і вертикальне центрування контейнера (0)
- Коротке порівняння Oracle SQL і ANSI SQL (0)
- Що нового в DB2 Viper (0)
- Контроль запитів за допомогою SQL Monitor. (0)
- «Розумний» SQL (0)
- Причини переходу від BDE до ADO (0)
- Вчимо говорити по-російськи: AllFusion Component Modeler (раніше Paradigm Plus) (0)
Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.
Коментарів поки що немає.
Ваш отзыв
Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>