DeepEdit!

Описание 

Что же такое временные таблицы, и зачем они нужны?

Временные таблицы используются в Oracle для хранения данных, которые относятся к одной сессии или одной транзакции.

Так, например, целесообразно использовать временные таблицы для хранения данных об открытых неким клиентским приложением или процессом файлах, об открытых дочерних формах. Можно так же сохранять во временных таблицах, данные матриц преобразований в сложных математических задачах.

Итак, временные таблицы(GLOBAL TEMPORARY), а отличие от таблиц регулярных целесообразно использовать в тех случаях, когда сохраняемее данные часто изменяются, и непостоянны.

Временные таблицы (GLOBAL TEMPORARY TABLE) отличаются от обычных регулярных таблиц тем, что эти таблицы предназначены только для хранения временных для некоторой сессии данных.

Данные во временной таблицы будут видны только в той сессии, которая вставила эти данные в таблицу.

После создания временной таблицы ее описание сохраняется в словаре данных ORACLE, но в этот момент не определяется сегмент , где будут сохранятся данные из этой таблицы.

Место в соответствующем сегменте под данные, выделяется динамически, в момент обращения первой команды манипулирования данными - DML (select, insert, update) к этой временной таблице.

Временная таблица описывается таким же образом, что и обычная регулярная таблица, но сегменты таблицы, и все данные такой таблицы, подразделяются :

- данные используемые только в данной сессии 

- или же данные используемые только в данной транзакции

Специфику поведения данных относительно сессии определяет ключевые слова ON COMMIT и ON RESERVE в команде CREATE TABLE

Можно использовать операторы определения данных DDL такие как (ALTER TABLE, CREATE INDEX ) для временных таблиц, но только тогда когда сессия не обращется к временной таблице, не связана с ней. Сессия связана с временной таблицей и при выполнении команды INSERT над данными таблицы.

Существует несколько способов, чтобы сделать сессию несвязанной с временной таблицей:

1. Использовать команду TURNICATE для данной таблицы.

2. Аннулировать данную сессию

3. Использовать команды фиксации работы транзакции COMMIT или ROLLBACK

Ограничения для временных таблиц в ORACLE

На временные таблицы в ORACLE распространяются следующие ограничения :

1. Временная таблица в Oracle не может быть партицирована particioned, кластеризована clustered, или быть организованной по индексу index organized.

2. Нельзя связывать вторичные ключи с колонками временной таблицы

3. Временная таблица не может включать в себя вложенных таблиц (nested table)

4. Нельзя использовать в описании временных таблиц следующие команды :LOB_storage_clause: TABLESPACE, storage_clause, или logging_clause

5. Подсказка Parallel и параллельные запросы не поддерживаются во временных таблицах

6. Распределенные транзакции так же не могут работать с временными таблицами

7. Сегментирование не работает в временных таблицах

Создание временной таблицы

Синтаксис для создания временных таблиц практически аналогичен синтаксису для создания регулярных таблиц, но есть некоторые операторы, которые используются только для временных таблиц:

ОN COMMIT DELETE ROWS используется во временных таблицах, данные которой существуют в пределах одной транзакции.

Oracle удаляет все строки - все данные из временной таблицы после завершения транзакции, после выполнения команды COMMIT.

Данная инструкция подразумевается по умолчанию, то есть при создании временной таблицы - она всегда будет ОN COMMIT DELETE ROWS

ОN COMMIT RESERVE ROWS используется во временных таблицах, данные которой существуют в пределах одной сессии. СУБД Oracle удаляет все строки из временной таблицы - очищает таблицу после завершения сессии.

Примеры

 

Данные примеры продемонстрируют создание и  использование временных таблиц, отличия временных таблиц ОN COMMIT DELETE ROWS и ОN COMMIT RESERVE ROWS,  отличие временных таблиц от таблиц регулярных.

 

Все примеры построены на основе стандартной демонстрационной схемы SCOTT БД ORACLE

Пример создания временной таблицы:

CREATE GLOBAL TEMPORARY TABLE SCOTT.TEMP_DEPT

(          

DEPTNO NUMBER(2,0),       

DNAME VARCHAR2(14)          

)     ON COMMIT PRESERVE ROWS;

Данный пример демонстрирует создание временной таблицы, данные которой, относятся к сессии. 

Пример создания временной таблицы с использованием поздапроса

CREATE GLOBAL TEMPORARY

TABLE min_salesemp  

ON COMMIT PRESERVE ROWS   

AS SELECT * FROM EMP WHERE sal < 2000

Данный пример демонстрирует создание временной таблицы на основе подзапроса, заполненные данные для этой таблицы актуальны только для текущей сессии. Если попробовать прочитать таблицу min_salesemp из другой сессий, то таблица будет пустой. 

Пример создания временной таблицы с объектным типом колонки

CREATE TYPE SCOTT.PERSON_T AS OBJECT (name VARCHAR2(100), ssn NUMBER); 

CREATE GLOBAL TEMPORARY TABLE SCOTT.TMP_OBJ    

(    

PERSON PERSON_T      

)    

ON COMMIT DELETE ROWS;

Этот пример демонстрирует, что использование объектных типов данных, вполне правомерно для временных таблиц.  

Использование индексов во временных таблицах

DROP TABLE SCOTT.CITY_DEPT;  

CREATE GLOBAL TEMPORARY TABLE SCOTT.CITY_DEPT    

(      

DEPTNO NUMBER(2,0),   DNAME VARCHAR2(14),

    CONSTRAINT PK_CITY_DEPT PRIMARY KEY (DEPTNO)       )

    ON COMMIT DELETE ROWS; 

COMMENT ON COLUMN SCOTT.CITY_DEPT.DEPTNO IS 'DEPARTMENT NUMBER';  COMMENT ON COLUMN SCOTT.CITY_DEPT.DNAME  IS 'DEPARTMENT NAME';    CREATE UNIQUE INDEX SCOTT.PK_CITY_DEPT_DNAME       ON SCOTT.CITY_DEPT (DEPTNO,DNAME) ; 

CREATE INDEX SCOTT.IDX_DNAME      

ON SCOTT.CITY_DEPT (DNAME)

  Данный пример показывает, что во временных таблицах можно использовать индексы и ограничения - CONSTRAINT , как и в регулярных таблицах.

Отличие временной таблицы от таблицы регулярной

Демонстрация отличия временной таблицы, от таблицы регулярной.

Соединяемся с БД, создаем две таблицы

временную

CREATE GLOBAL TEMPORARY TABLE SCOTT.TMP_T    

(    

ID NUMBER(32)      

)     ON COMMIT DELETE ROWS;

регулярную

CREATE TABLE SCOTT.REG_T    

(    

ID NUMBER(32)  

     ) ;  

-- Не будем нагружать скрипты излишним синтаксисом

Добавляем данные

INSERT INTO TMP_T(ID) VALUES (1);INSERT INTO TMP_T(ID) VALUES (2);INSERT INTO TMP_T(ID) VALUES (3);INSERT INTO REG_T(ID) VALUES (1);INSERT INTO REG_T(ID) VALUES (2);INSERT INTO REG_T(ID) VALUES (3);

Выполняем запрос к временной таблице

SELECT * FROM TMP_T;

Результат

---

1

2

3

к регулярной таблице

SELECT * FROM REG_T;

Результат

---

1

2

3

Закрываем сессию, соединяемся с БД снова. Выполняем запрос к временной таблице

SELECT * FROM TMP_T;

Результат

--- 

К регулярной таблице

SELECT * FROM REG_T;

Результат

---

1

2

3

Итак, данные во временной таблице сохраняются только в текущей сессии или транзакции в зависимости от выражения ON COMMIT в скрипте создания таблицы.

  

Отличие временной таблицы ОN COMMIT PRESERVE ROWS от временной таблицы ОN COMMIT DELETE ROWS

Создаем две таблицы ON COMMIT PRESERVE ROWS

CREATE GLOBAL TEMPORARY TABLE SCOTT.TMP_PRE_ROWS    

(     ID NUMBER(32)       )     ON COMMIT PRESERVE ROWS;

ОN COMMIT DELETE ROWS

CREATE GLOBAL TEMPORARY TABLE SCOTT.TMP_DEL_ROWS    

(     ID NUMBER(32)       )     ON COMMIT DELETE ROWS;

Добавляем данные в созданные таблицы

INSERT INTO SCOTT.TMP_DEL_ROWS(ID) VALUES (1);

INSERT INTO SCOTT.TMP_DEL_ROWS(ID) VALUES (2);

INSERT INTO SCOTT.TMP_DEL_ROWS(ID) VALUES (3);   

INSERT INTO SCOTT.TMP_PRE_ROWS(ID) VALUES (1);

INSERT INTO SCOTT.TMP_PRE_ROWS(ID) VALUES (2);

INSERT INTO SCOTT.TMP_PRE_ROWS(ID) VALUES (3);  

Смотрим

SELECT * FROM SCOTT.TMP_PRE_ROWS;

Реультат запроса

--

1

2

3

SELECT * FROM SCOTT.TMP_DEL_ROWS;

Результат запроса

--

1

2

3 

Выполняем команду COMMIT после чего вновь выполняем запросы

SELECT * FROM SCOTT.TMP_PRE_ROWS;

Результат запроса

--

1

2

3 

SELECT * FROM SCOTT.TMP_DEL_ROWS;

Результат запроса

-- 

Итак , данные в таблице ОN COMMIT DELETE ROWS не сохраняются после выполнения команды COMMIT, в отличие от временной таблицы ON COMMIT PRESERVE ROWS

Итог : после прочтения данного материала становиться понятно следующее:

что такое временные таблицы в ORACLE

назначение временных таблиц

область применения временных таблиц в ORACLE

ограничения для временных таблиц

отличие временных таблиц от регулярных таблиц

отличие временных таблиц с данными уровня сессии от временных таблиц с данными уровня транзакции (ON COMMIT PRESERVE ROWS ,ОN COMMIT DELETE ROWS).