Временные таблицы в Oracle

Источник: cyberguru

Временные таблицы впервые появились в 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)
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 строка
Затрачено времени: 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$ таблице и поэтому информация в нём более верна.

Выводы:

Фиксируйте транзакцию после заполнения временной таблицы. Это предотвратит проблемы с сегментом отката.
Для ускорения работы с большой временной таблицей создавайте индексы.
Не пытайтесь, собирайте статистику таблицы с помощью команды ANALYZE. Это не имеет смысла. Если статистика всё же нужна, используйте пакет dbms_stats для ёё принудительной установки.
Не создавайте столбцы типа VARRAY или вложенные таблицы, а также внешние ключи.
Вы не сможете переместить таблицу в другое табличное пространство с помощью команды ALTER TABLE MOVE, а также самостоятельно заблокировать таблицу с помощью команды LOCK TABLE.
Не применяйте DDL команды, если таблицу уже используют. Будет выдаваться ошибка. В некоторых версиях Oracle имеются баги. В этом случае даже если никто не использует эту таблицу, применить DDL команду вам не удастся. Поможет только перезагрузка Oracle.
Для просмотра выделенного временного табличного пространства по сеансам используйте представление v$sort_usage.

Ссылки по теме