1. Главная страница » Компьютеры » Oracle создание временной таблицы

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

Автор: | 16.12.2019

добавлено: 14 окт 12
понравилось:0
просмотров: 63101
комментов: 0

Что же такое временные таблицы, и зачем они нужны?
Давайте разберемся.
Временные таблицы используются в Oracle для хранения данных, которые относятся к одной сессии или одной транзакции.
Причем применение Oracle временных таблиц существенно отличается от применения временных таблиц в том же MS SQL. ORACLE временная таблица это тот же DDL обьект со всеми ограничениями , в MS SQL подобных ограничений нет.
итак подробнее

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

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

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

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

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

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

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

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

Существует несколько способов, чтобы сделать сессию несвязанной с временной таблицей:
1. Использовать команду truncate для данной таблицы.
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 PRESERVE ROWS используется во временных таблицах, данные которой существуют в пределах одной сессии. СУБД Oracle удаляет все строки из временной таблицы — очищает таблицу после завершения сессии.

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

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

Читайте также:  Am4 материнская плата какую выбрать

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

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

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

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

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

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

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

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

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

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

1
2
3

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

1
2
3

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

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

1
2
3

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

Отличие временной таблицы ОN COMMIT PRESERVE ROWS от временной таблицы ОN COMMIT DELETE ROWS
Создаем две таблицы ON COMMIT PRESERVE ROWS
Добавляем данные в созданные таблицы
Смотрим

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

1
2
3

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Главная /
  • Статьи /
  • Oracle /
  • RMAN В ПРИМЕРАХ — Использование RMAN. Глава 3. Часть 3

Временные таблицы 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, прямо сообщая нам, что не поддерживает сбор статистики для временных таблиц:

Читайте также:  Any video converter как конвертировать

Рассмотрим всё это на примере. Для этого создадим индекс на таблицу 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$ таблице и поэтому информация в нём более верна.

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

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *