Временные таблицы впервые появились в 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; Таблица создана SQL> INSERT INTO table1 (id,name) VALUES(1,'items1'); Вставлено: 1 строка SQL> commit Commit complete SQL> SELECT * FROM table1 ID NAME -- ------ 1 items1 Выбрано: 1 строка SQL> DISCONNECT; Отключение SQL> CONNECT TEST/*****@ALFA8174; Подключение SQL> SELECT * FROM table1; ID NAME -- ---- Выбрано: 0 строк
Видно, что после отключения сеанса, данные из таблицы удаляются. Теперь создадим и заполним временную таблицу на время транзакции:
SQL> CREATE GLOBAL TEMPORARY TABLE table2 (id NUMBER(5),name VARCHAR2(20)) ON COMMIT DELETE ROWS; Таблица создана SQL> INSERT INTO table2 (id,name) VALUES(1,'items1'); Вставлено: 1 строка SQL> SELECT * FROM table2; ID NAME -- ------ 1 items1 Выбрано: 1 строка SQL> COMMIT; Commit complete SQL> SELECT * FROM table2; ID NAME -- ---- Выбрано: 0 строк
Данные из таблицы удалились сразу после завершения транзакции. Отличительной особенностью временной таблицы в данном случае является то, что данные таблицы не только удаляются, но и невидны из других сеансов. То есть пользователи могут одновременно использовать одну и туже временную таблицу, не пересекаясь данными. Отсюда вытекают некоторые ограничения при работе с временными таблицами. Так нельзя блокировать таблицу с помощью команды LOCK TABLE. Команда выполниться без ошибок, но блокировка не установиться:
SQL> LOCK TABLE table1 IN EXCLUSIVE MODE;
Table(s) locked
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 строка
Нельзя добавлять внешние ключи на временную таблицу и наоборот:
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 строка SQL> SELECT * FROM table1; ID NAME -- ------ 1 items1 Выбрано: 1 строка SQL> ROLLBACK; Rollback complete SQL> SELECT * FROM table1; ID NAME -- ---- Выбрано: 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 SQL> COMMIT; Commit complete SQL> analyze table test.table1 compute statistics; Table analyzed SQL> SET AUTOTRACE TRACE EXPLAIN; SQL> select id from test.table1 where id < 999 Выбрано: 998 строк План выполнения: -------------------------------------------------- 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
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
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 строки
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 строки
SQL> SET AUTOTRACE TRACE EXPLAIN;
SQL> SELECT name FROM test.table1 where id > 9400;
Выбрано: 600 строк
План выполнения:
--------------------------------------------------
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 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)
2 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 строка SQL> SELECT tablespace_name, bytes, blocks FROM dba_free_space WHERE tablespace_name = 'TTEMP'; TABLESPACE_NAME BYTES BLOCKS --------------- ------- ------ TTEMP 5234688 639 Выбрано: 1 строка
Табличное пространство почти свободно. Теперь заполним таблицу 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 SQL> COMMIT; Commit complete
Экстенты для таблицы выделяются при выполнении операции вставки. Посмотрим, сколько осталось свободных блоков в табличном пространстве:
SQL> SELECT tablespace_name,bytes,blocks FROM dba_free_space WHERE tablespace_name = 'TTEMP'; TABLESPACE_NAME BYTES BLOCKS --------------- ------- ------ TTEMP 5070848 619 Выбрано: 1 строка
Таблица заняла в 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 строки
Для получения более точной информации по использованию табличного пространства сеансами нужно сделать запрос к следующему представлению:
SQL> SELECT tablespace,segtype,extents,blocks FROM v$sort_usage; TABLESPACE SEGTYPE EXTENTS BLOCKS ---------- ------- ------- ------ TTEMP DATA 1 10 TTEMP INDEX 1 10 Выбрано: 2 строки
Итак, таблица заняла 2 экстента по 10 блоков каждый, при этом один экстент выделен под данные, другой под индексы. Если мы теперь сделаем DISCONNECT, то увидим, что выделенные экстенты под временную таблицу table1 освободились.
SQL> SELECT tablespace,segtype,extents,blocks FROM v$sort_usage; TABLESPACE SEGTYPE EXTENTS BLOCKS ---------- ------- ------- ------ Выбрано: 0 строк
Но в тоже время мы видим, что число выделенных экстентов в табличном пространстве не уменьшилось:
SQL> SELECT tablespace_name,bytes,blocks FROM dba_free_space WHERE tablespace_name = 'TTEMP'; TABLESPACE_NAME BYTES BLOCKS --------------- ------- ------ TTEMP 5070848 619 Выбрано: 1 строка 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 строки
Отсюда может сложиться заблуждение, что экстенты не освобождены. На самом деле, как я предполагаю, информация в словаре (а представления 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.