Oracle создать временную таблицу oracle

Обновлено: 15.01.2025

Временная таблица это таблица определение которой видно всем сессиям, но данные для каждой сессии только те, которые были добавлены этой сессией. Программисты могут использовать их для обработки больших данных. Синтаксис команды

CREATE GLOBAL TEMPORARY TABLE temp_tab_name

(column datatype [,column datatype] )

Определение столбцов такое же как и для обычной таблицы и так же может быть получено подзапросом. Дополнительная директива определяет время жизни добавленных в таблицу строк. По умолчанию используется удаление строк по окончании добавивших их транзакции, но можно использовать PRESERVE и данных будут хранится до конца сессии. В любом случае данные во временных таблицах свои для каждой сесии: разные пользователи могут добавлять свои строки в таблицу и они никогда не увидят строк другой сессии.

Временные таблицы во многом схожи с обычными. Вы можете выполнять DML или SELECT команды используя их. Можно создать индексы, ограничеиня и триггеры для этих таблицы. Для них можно создать представления, синонимы и все SQL команды к временным таблицам будут выполняться быстрее чем к обычным таблицам.

Первой причиной такого быстродействия является то что временные таблицы не являются сегментами в табличном пространстве. В идеале они существуют только в PGA сессии которая их использует, т.е. не используется жесткий диск и даже буфер БД. Если PGA не доступно памяти для хранения временной таблицы (к примеру если миллионы строк вставляются в таблицы – такое часто бывает при создании отчётов) то таблица записывается во временное табличное пространтсво. Операции чтения/записи во временном табличном пространстве гораздо быстрее так как не используется буфер БД: все операции производятся соотствующим серверным процессом сразу на диск.

Вторая причина быстродействия – DML команды дял временных таблиц не создают вектором изменений. Так как данные хранятся только во время жизни сессии (или транзакции) нет смысла создавать данные для отмены изменений. Это даёт двойной выигрыш – быстрое выполнение DML операция и снижение нагрузки на систему генерации отмены изменений, что могло бы плохо влиять на производительность в нагруженной системе с большим числом пользователей.

На рисунке 7-2 показано создание временной таблицы используя SQL *Plus. Также можно создать временную таблицу используя Database Control Table Creation Wizrad.

Главная /
Статьи /
Oracle /
Практическое администрирование Oracle - Аудит. Часть1.

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

В ременные таблицы впервые появились в Oracle8I. Они предназначались для хранения данных на протяжении сеанса или транзакции. Отличительной особенностью этих таблиц являлось то, что они располагались во временных сегментах и данные в этих таблицах хранились только на период сессии или транзакции в зависимости от реализации. Поэтому они нашли большое применение в качестве промежуточных таблиц при расчётах, отчетах и оптимизации сложных запросов.

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

В качестве примера попробуем создать временную таблицу на сеанс и заполнить её данными:

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

Данные из таблицы удалились сразу после завершения транзакции. Отличительной особенностью временной таблицы в данном случае является то, что данные таблицы не только удаляются, но и невидны из других сеансов. То есть пользователи могут одновременно использовать одну и туже временную таблицу, не пересекаясь данными. Отсюда вытекают некоторые ограничения при работе с временными таблицами. Так нельзя блокировать таблицу с помощью команды LOCK TABLE. Команда выполниться без ошибок, но блокировка не установиться:

Нельзя добавлять внешние ключи на временную таблицу и наоборот:

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

Временные таблицы при DML операциях не генерируют информации повторного выполнения, так как изначально создаются в режиме NOLOGGING (смотри Использование режима NOLOGGING. Часть II). Но при этом они поддерживают механизм отката изменений, как и для обыкновенной таблицы:

Кстати об этом забывают, и отсюда возникает предубеждение, что commit после заполнения временной таблицы (для таблицы на сеанс) можно не ставить. Это обычно приводит к проблемам функционирования сегментов отката. Данные, помещённые в сегмент отката после заполнения временной таблицы и не фиксации изменений транзакции, будут находиться там до отключения сеанса, препятствуя схлопыванию сегментов отката. Отсюда вывод: не забывайте ставить commit для временных таблиц.

Для ускорения работы с временными таблицами можно создавать индексы. Они также располагаются во временном табличном пространстве и заполняются при вставке данных в таблицу. Но если вы попытаетесь создать индекс, после того как какой либо сеанс уже вставил в неё данные, то вам выдастся ошибка:

Это утверждение верно также для всех DDL команд применяемых к временной таблице. Теперь рассмотрим оптимизацию SQL при работе с временными таблицами. Одним из заблуждений при работе с временными таблицами является то, что можно проводить их анализ с целью собрать статистику для оптимизатора. Временная таблица по своему определению не может иметь постоянную статистику, следовательно, оптимизатор при построении плана запроса строит план выполнения исходя не из статистики, а из предположения по умолчанию. К данному заблуждению может подтолкнуть безошибочное выполнение команды ANALYZE. Но, просмотрев представление DBA_TABLES после выполнения данной команды, мы не обнаружим статистики для данной таблицы. Более честно поступает в этом случае пакет dbms_stats, прямо сообщая нам, что не поддерживает сбор статистики для временных таблиц:

Рассмотрим всё это на примере. Для этого создадим индекс на таблицу table1, заполним данными, проанализируем её и выберем почти все записи, предварительно включив вывод плана выполнения:

Как видно, оптимизатор не выдал никакой оценки стоимости и использовал предположения по умолчанию. Иногда этого недостаточно. Но выход в этом случае есть. На самом деле статистику для временной таблицы можно установить принудительно, используя для этого пакет dbms_stats. Попробуем сделать это, получив статистику с аналогичной не временной таблицы table3:

Как показывают планы выполнения двух последних запросов, статистика для таблицы table1 всё же была установлена, и при этом оптимизатор её использовал (правило 5 %). Последнее что нам осталось это рассмотреть, как выделяется и освобождается табличное пространство для временных таблиц.

Исходя из своего определения, временной таблице нельзя заранее выделить необходимое пространство во временном сегменте. Это делает сам Oracle при команде INSERT. Рассмотрим, как это происходит на примере. Для начала посмотрим, сколько блоков всего и свободно в табличном пространстве TTEMP, где расположена временная таблица table1:

Табличное пространство почти свободно. Теперь заполним таблицу table1:

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

Таблица заняла в TTEMP 20 блоков. Это 2 экстента. Проверим:

Для получения более точной информации по использованию табличного пространства сеансами нужно сделать запрос к следующему представлению:

Итак, таблица заняла 2 экстента по 10 блоков каждый, при этом один экстент выделен под данные, другой под индексы. Если мы теперь сделаем DISCONNECT, то увидим, что выделенные экстенты под временную таблицу table1 освободились.

Но в тоже время мы видим, что число выделенных экстентов в табличном пространстве не уменьшилось:

Отсюда может сложиться заблуждение, что экстенты не освобождены. На самом деле, как я предполагаю, информация в словаре (а представления dba_free_space и dba_extents построены именно на нём) меняется для временных табличных пространств только при первом выделении экстентов. Это вполне может, связано с большими накладными расходами по обновлению словаря. Представление v$sort_usage наоборот построено на x$ таблице и поэтому информация в нём более верна.

В качестве примера попробуем создать временную таблицу на сеанс и заполнить её данными: Видно, что после отключения сеанса, данные из таблицы удаляются. Теперь создадим и заполним временную таблицу на время транзакции. Данные из таблицы удалились сразу после завершения транзакции. Отличительной особенностью временной таблицы в данном случае является то, что данные таблицы не только удаляются, но и невидны из других сеансов. То есть пользователи могут одновременно использовать одну и туже временную таблицу, не пересекаясь данными. Отсюда вытекают некоторые ограничения при работе с временными таблицами. Так нельзя блокировать таблицу с помощью команды LOCK TABLE. Команда выполниться без ошибок, но блокировка не установится. Нельзя добавлять внешние ключи на временную таблицу и наоборот. Не поддерживается так же перенос временной таблицы в другое табличное пространство. Временные таблицы при DML операциях не генерируют информации повторного выполнения, так как изначально создаются в режиме NOLOGGING (смотри Использование режима NOLOGGING. Часть II). Но при этом они поддерживают механизм отката изменений, как и для обыкновенной таблицы. Кстати об этом забывают, и отсюда возникает предубеждение, что commit после заполнения временной таблицы (для таблицы на сеанс) можно не ставить. Это обычно приводит к проблемам функционирования сегментов отката. Данные, помещённые в сегмент отката после заполнения временной таблицы и не фиксации изменений транзакции, будут находиться там до отключения сеанса, препятствуя схлопыванию сегментов отката. Отсюда вывод: не забывайте ставить commit для временных таблиц. Для ускорения работы с временными таблицами можно создавать индексы. Они также располагаются во временном табличном пространстве и заполняются при вставке данных в таблицу. Но если вы попытаетесь создать индекс, после того как какой либо сеанс уже вставил в неё данные, то вам выдастся ошибка: Это утверждение верно также для всех DDL команд применяемых к временной таблице. Теперь рассмотрим оптимизацию SQL при работе с временными таблицами. Одним из заблуждений при работе с временными таблицами является то, что можно проводить их анализ с целью собрать статистику для оптимизатора. Временная таблица по своему определению не может иметь постоянную статистику, следовательно, оптимизатор при построении плана запроса строит план выполнения исходя не из статистики, а из предположения по умолчанию. К данному заблуждению может подтолкнуть безошибочное выполнение команды ANALYZE. Но, просмотрев представление DBA_TABLES после выполнения данной команды, мы не обнаружим статистики для данной таблицы. Более честно поступает в этом случае пакет dbms_stats, прямо сообщая нам, что не поддерживает сбор статистики для временных таблиц. Рассмотрим всё это на примере. Для этого создадим индекс на таблицу table1, заполним данными, проанализируем её и выберем почти все записи, предварительно включив вывод плана выполнения. Как видно, оптимизатор не выдал никакой оценки стоимости и использовал предположения по умолчанию. Иногда этого недостаточно. Но выход в этом случае есть. На самом деле статистику для временной таблицы можно установить принудительно, используя для этого пакет dbms_stats. Попробуем сделать это, получив статистику с аналогичной не временной таблицы table3. Как показывают планы выполнения двух последних запросов, статистика для таблицы table1 всё же была установлена, и при этом оптимизатор её использовал (правило 5 %). Последнее что нам осталось это рассмотреть, как выделяется и освобождается табличное пространство для временных таблиц. Исходя из своего определения, временной таблице нельзя заранее выделить необходимое пространство во временном сегменте. Это делает сам Oracle при команде INSERT. Рассмотрим, как это происходит на примере. Для начала посмотрим, сколько блоков всего и свободно в табличном пространстве TTEMP, где расположена временная таблица table1. Табличное пространство почти свободно. Теперь заполним таблицу table1. Экстенты для таблицы выделяются при выполнении операции вставки. Посмотрим, сколько осталось свободных блоков в табличном пространстве. Таблица заняла в TTEMP 20 блоков. Это 2 экстента. Проверим. Для получения более точной информации по использованию табличного пространства сеансами нужно сделать запрос к следующему представлению Итак, таблица заняла 2 экстента по 10 блоков каждый, при этом один экстент выделен под данные, другой под индексы. Если мы теперь сделаем DISCONNECT, то увидим, что выделенные экстенты под временную таблицу table1 освободились. Но в тоже время мы видим, что число выделенных экстентов в табличном пространстве не уменьшилось.

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

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

Oracle позволяет создавать временные таблицы для хранения данных только на протяжении сеанса или даже одной транзакции. После окончания сеанса или транзакции таблица очищается (все строки автоматически удаляются). Временные таблицы удобны, когда вы имеете дело со сложными запросами или транзакциями, которые требуют хранения временной информации перед записью ее в постоянные таблицы.

Данные во временных таблицах не могут быть резервированы как данные постоянных таблиц. Временным таблицам или индексам до их создания никаких сегментов данных или индексов автоматически не выделяются, как это происходит с постоянными таблицами и индексами. Место для временных таблиц выделяется во временных сегментах только после первого применения команды INSERT с этими таблицами.

Временные таблицы повышают производительность транзакций, включающих сложные запросы. Одним из традиционных альтернатив сложным запросам является использование представлений для упрощения сложных запросов, но представление должно выполняться при каждом обращении к нему, что во многих случаях сводит на нет все преимущества. Временные таблицы — отличное решение для подобных случаев, поскольку они могут быть созданы как результат сложного оператора SELECT, использованного в конкретном сеансе или транзакции, и автоматически очищаются после сеанса.

Важно! Хотя Oracle не анализирует данные временных таблиц на предмет распределения данных, это не представляет проблемы для эффективной обработки запросов, потому что временные таблицы могут хранить постоянно доступные соединения и прочую информацию в одном удобном месте. Вместо многократного выполнения сложных запросов к постоянным таблицам можно многократно обращаться к этим таблицам.

Временные таблицы создаются во временном табличном пространстве пользователя и им выделяются временные сегменты только после первого оператора INSERT, обращенного к временной таблице. После завершения транзакции или окончания сеанса они освобождаются — в зависимости от того, как временная таблица была определена.

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

Временные таблицы существенно сокращают объем действий, связанных с журналами повторного выполнения, которые генерируются транзакциями. Журналы повторного выполнения заполняются не так быстро при использовании временных таблиц во время сложных транзакций.
Временные таблицы могут быть проиндексированы для повышения производительности.
Сеансы могут обновлять, вставлять и удалять данные во временных таблицах точно так же, как и в постоянных.
Данные автоматически удаляются из временной таблицы по окончании сеанса или транзакции.
На временных таблицах можно определять ограничения.
Разные пользователи могут обращаться к одной и той же временной таблице, причем каждый из них видит данные только своего собственного сеанса.
Временные таблицы обеспечивают эффективный доступ к данным, поскольку сложные запросы не приходится выполнять многократно.
Минимальный объем блокировок временных таблицы означает более эффективную обработку запросов.
Структура таблицы сохраняется после удаления данных, что помогает их использовать в будущем.

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

Рассмотрим пример создания временной таблицы Oracle Database на SQL, которая существует на протяжении сеанса; для этого используется опция ON COMMIT DELETE ROWS:

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

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

В отличие от временной таблицы сеанса, временные таблицы транзакций специфичны для отдельной транзакции. Как только транзакция фиксируется или откатывается, данные из этой таблицы удаляются. Как создать временную таблицу транзакции на языке SQL рассмотрим на примере:

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

Читайте также: