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

Обновлено: 21.11.2024

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

Если вы перешли в Oracle с другого ядра базы данных, такого как SQL Server, вас может смутить идея Oracle о временных таблицах. Разработчики SQL Server регулярно создают временную таблицу для выполнения некоторой работы и удаляют ее. В Oracle глобальная временная таблица (GTT) — это постоянный объект метаданных, который содержит строки во временных сегментах на основе транзакции или сеанса. Не считается нормальным создавать и удалять GTT на лету. С введением закрытых временных таблиц в Oracle появилась возможность, аналогичная той, что используется в других механизмах, где сам объект таблицы является временным, а не только данные.

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

Oracle поддерживает два типа временных таблиц.

  • Глобальные временные таблицы: доступно, начиная с Oracle 8i. Обсуждается здесь.
  • Частные временные таблицы: доступны начиная с Oracle 18c и являются предметом этой статьи.

Именование частных временных таблиц

Параметр инициализации PRIVATE_TEMP_TABLE_PREFIX, который по умолчанию равен "ORA$PTT_", определяет префикс, который должен использоваться в имени при создании частной временной таблицы. В следующем примере мы создаем частную временную таблицу без использования правильного префикса в имени, что приводит к ошибке.

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

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

Предложение ON COMMIT DROP DEFINITION по умолчанию указывает, что таблица должна быть удалена в конце транзакции или в конце сеанса.

Напротив, предложение ON COMMIT PRESERVE DEFINITION указывает, что таблица и все данные должны сохраняться после завершения транзакции. Таблица будет удалена в конце сеанса.

Приведенный выше вывод показывает, что таблица сохраняется после фиксации, но удаляется, когда мы отключаемся и создаем новый сеанс.

Мы также можем создавать частные временные таблицы с помощью метода CTAS.

Частные временные таблицы и PL/SQL

Для постоянного объекта PL/SQL нет смысла напрямую ссылаться на временный объект, так как он не будет существовать во время компиляции. Если вы хотите использовать частную временную таблицу из постоянного объекта, это нужно сделать с помощью динамического SQL. В следующем глупом примере создается хранимая функция, которая использует приватную временную таблицу.

Функция работает должным образом.

Просмотры

Частные временные таблицы размещаются в памяти, поэтому метаданные не записываются в словарь данных. В результате вы не можете использовать представление USER_TABLES для отображения списка частных временных таблиц в текущем сеансе. Следующие представления доступны для отображения информации о частных временных таблицах.

    : все частные временные таблицы в базе данных. : Частные временные таблицы в текущем сеансе.

Ограничения

Частные временные таблицы имеют те же ограничения, что и глобальные временные таблицы (см. здесь), но есть и дополнительные ограничения.

Приложения часто используют некоторую форму временного хранилища данных для процессов, которые сложно выполнить за один проход. Часто эти временные хранилища определяются как таблицы базы данных или таблицы PL/SQL. Начиная с версии Oracle 8i обслуживание и управление временными таблицами можно делегировать серверу с помощью глобальных временных таблиц.

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

Oracle поддерживает два типа временных таблиц.

  • Глобальные временные таблицы: доступны начиная с Oracle 8i и являются предметом этой статьи.
  • Частные временные таблицы: доступно, начиная с Oracle 18c. Обсуждается здесь.

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

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

Предложение ON COMMIT DELETE ROWS указывает, что данные должны быть удалены в конце транзакции или в конце сеанса.

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

Глобальные временные таблицы и отмена

Несмотря на то, что данные в GTT записываются во временное табличное пространство, связанная отмена по-прежнему записывается в обычное табличное пространство отмены, которое само защищено повтором, поэтому использование GTT не уменьшает количество отмен и повторов, связанных с защитой отменить табличное пространство.

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

Теперь мы повторяем предыдущий тест, но на этот раз с использованием GTT.

Мы видим, что нет существенной разницы в используемой отмене.

В Oracle 12c появилась концепция временной отмены, позволяющая записывать отмену для GTT во временное табличное пространство, тем самым уменьшая количество отмен и повторов.

Глобальные временные таблицы и повтор

Если вы читали предыдущий раздел, вы уже знаете взаимосвязь между глобальными временными таблицами и повторами. Данные в GTT записываются во временное табличное пространство, которое напрямую не защищено повторением, поэтому использование GTT повышает производительность за счет уменьшения повторного выполнения. К сожалению, до версии Oracle 12c все операции отмены, связанные с DML в отношении GTT, записываются в обычное табличное пространство отмены операций, которое само защищено функцией повторного выполнения. В результате использование GTT уменьшает количество повторных операций, но не устраняет их. Еще одна причина описания этого заключается в том, что использование GTT удаляет прямое генерирование повторов, но не косвенное генерирование повторов, вызванное отменой.

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

Теперь мы повторяем предыдущий тест, но на этот раз с использованием GTT.

Мы видим, что при использовании GTT мы создали на порядок меньше повторных операций, но мы не устранили их.

Разное

  • Если оператор TRUNCATE используется для временной таблицы, усекаются только данные, относящиеся к сеансу. Это не влияет на данные других сеансов.
  • Данные во временных таблицах хранятся во временных сегментах временного табличного пространства.
  • Данные во временных таблицах автоматически удаляются в конце сеанса базы данных, даже если он завершается аварийно.
  • Индексы можно создавать для временных таблиц. Содержимое индекса и область действия индекса совпадают с сеансом базы данных.
  • Представления можно создавать для временных таблиц и комбинаций временных и постоянных таблиц.
  • С временными таблицами могут быть связаны триггеры.
  • Утилиты экспорта и импорта можно использовать для передачи определений таблиц, но строки данных не обрабатываются.
  • Статистика по временным таблицам является общей для всех сеансов. Oracle 12c позволяет вести статистику по сеансам.
  • Существует ряд ограничений, связанных с временными таблицами, но они зависят от версии.

Частные временные таблицы (18c+)

В Oracle 18c появился новый вариант временных таблиц. Частная временная таблица — это временная таблица в памяти, которая удаляется в конце сеанса или транзакции в зависимости от настройки. Подробнее о них можно прочитать здесь.

Вывод: в этом руководстве вы узнаете о частной временной таблице Oracle и о том, как использовать оператор CREATE PRIVATE TEMPORARY TABLE для создания новой частной временной таблицы.

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

Если вы знакомы с другими продуктами баз данных, такими как SQL Server, PostgreSQL и MySQL, вас может смутить концепция временных таблиц в базе данных Oracle.

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

В Oracle 18c появились частные временные таблицы, в которых и определение таблицы, и данные являются временными и удаляются в конце транзакции или сеанса. Кроме того, Oracle хранит частные временные таблицы в памяти, и каждая временная таблица видна только для создавшего ее сеанса.

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

Все частные временные таблицы имеют префикс, определяемый параметром инициализации PRIVATE_TEMP_TABLE_PREFIX, который по умолчанию равен ORA$PTT_ .

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

Чтобы создать новую частную временную таблицу, используйте оператор CREATE PRIVATE TEMPORARY TABLE:

Сначала укажите имя временной таблицы в соответствии с упомянутым выше правилом именования.

Во-вторых, укажите список столбцов с их определениями.

В-третьих, используйте предложение ON COMMIT, чтобы указать, относится ли таблица к транзакции или сеансу:

  • Параметр ON COMMIT DROP DEFINITION создает частную временную таблицу, зависящую от транзакции. В конце транзакции Oracle удаляет как определение таблицы, так и данные.
  • Параметр ON COMMIT PRESERVE DEFINITION создает частную временную таблицу, зависящую от сеанса. Oracle удаляет все данные и удаляет таблицу в конце сеанса.

По умолчанию Oracle использует ON COMMIT DROP DEFINITION, если вы не укажете параметр ON COMMIT.

Частные временные таблицы и глобальные временные таблицы

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

ХарактеристикаГлобальные временные таблицыЧастные временные таблицы
Правило именованияТо же, что и для постоянных таблицПо умолчанию должно иметь префикс ORA$PTT_ .
ВидимостьВсе сеансыТолько сеанс, создавший таблицу.
ХранилищаДиск td>Только память
Типы таблицЗависит от транзакции (ON COMMIT DELETE ROWS) или зависит от сеанса (ON COMMIT PRESERVE ROWS)< /td>Зависит от транзакции ( ON COMMIT DROP DEFINITION ) или зависит от сеанса ( ON COMMIT PRESERVE DEFINITION )

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

Давайте рассмотрим несколько примеров использования оператора CREATE PRIVATE TEMPORARY TABLE.

1) Пример создания частной временной таблицы, зависящей от транзакции

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

Далее вставьте строку в таблицу ora$ppt_temp1:

Затем просмотрите содержимое таблицы ora$ppt_temp1:

После этого зафиксируйте транзакцию:

Наконец, запросите данные из таблицы ora$ppt_temp1:

Oracle выдал следующую ошибку, так как уже удалил таблицу ora$ppt_temp1 в конце транзакции.

2) Создание частной временной таблицы, зависящей от сеанса

Сначала создайте новую временную таблицу для конкретного сеанса:

Далее вставьте строку в таблицу ora$ppt_temp2:

Затем просмотрите данные таблицы ora$ppt_temp2:

После этого зафиксируйте транзакцию и проверьте содержимое таблицы ora$ppt_temp2:

Наконец, переподключитесь и просмотрите содержимое таблицы ora$ppt_temp2:

Oracle выдал следующую ошибку, поскольку удалил таблицу ora$ppt_temp2 в конце сеанса.

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

Частные временные таблицы имеют те же ограничения, что и глобальные временные таблицы, и следующие дополнительные ограничения:

  • Имя таблицы должно иметь префикс, определенный в параметре инициализации PRIVATE_TEMP_TABLE_PREFIX, который по умолчанию равен ORA$PTT_ .
  • Постоянные объекты базы данных не могут напрямую ссылаться на частные временные таблицы. а материализованные представления нельзя создавать для частных временных таблиц.
  • Столбцы частной временной таблицы не могут иметь значений по умолчанию.
  • Доступ к частным временным таблицам через ссылки на базу данных невозможен.

из этого руководства вы узнали, как использовать оператор Oracle CREATE PRIVATE TEMPORARY TABLE для создания новой частной временной таблицы, зависящей от транзакции или сеанса.

Временная таблица Oracle, которая была введена в Oracle 8i, может быть определена как постоянный объект, в котором данные хранятся на диске, и эти конкретные данные немедленно удаляются после того, как конкретный сеанс или транзакция подошли к концу, а для Oracle частная временная таблицы хранятся в памяти базы данных Oracle, и каждая из этих таблиц видна только сеансу, который создал эту конкретную таблицу, и эти таблицы обычно представляют собой программы SQL, встроенные в сеансы Oracle.

Синтаксис:

Hadoop, наука о данных, статистика и др.

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

Синтаксис глобальной временной таблицы Oracle показан ниже:

CREATE GLOBAL TEMPORARY TABLE имя_таблицы
(тип данных столбца 1 [ ограничение ],
тип данных столбца 2 [ ограничение ],
. );

Параметры:

  • table_name: относится к имени временной таблицы, которую создает пользователь.
  • Столбец1, столбец2: относится к названиям столбцов.
  • Ограничение: относится к ограничению столбца, которое мы можем добавлять или не добавлять.

Как работает временная таблица в Oracle?

Временная таблица в Oracle, как определено ранее, используется для хранения данных для какой-либо конкретной задачи, поскольку данные временной таблицы удаляются, как только транзакция или сеанс завершаются или заканчиваются. Предположим, мы хотим извлечь некоторые данные, которые не хранятся в конкретной таблице и отсутствуют в базе данных. В этом случае мы можем использовать процедуру, чтобы сначала извлечь данные из разных таблиц и вставить эти данные во временную таблицу во время начала сеанса или транзакции.Данные из временной таблицы, созданной во время запуска сеанса или транзакции, могут использоваться на протяжении всего сеанса или на протяжении всей транзакции в зависимости от разрешения, предоставленного при создании таблицы. Если мы указываем ON COMMIT DELETE ROWS, то таблица зависит от транзакции, но если мы указываем ON COMMIT PRESERVE ROWS, то таблица зависит от сеанса.

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

В предыдущем разделе этой статьи мы узнали, как работает временная таблица в Oracle. В этом разделе мы увидим, как мы можем создать временную таблицу. Временная таблица похожа на другую таблицу, но мы должны использовать ключевое слово «GLOBAL TEMPORARY» в запросе на создание таблицы. В этом примере мы создадим временную таблицу с именем temp_table, имеющую столбцы с именами id и description. Столбец id будет первичным ключом. Давайте теперь рассмотрим запрос на создание временной таблицы.

CREATE GLOBAL TEMPORARY TABLE temp_table (
id NUMBER PRIMARY KEY,
description VARCHAR2(20)
)
ON COMMIT DELETE ROWS;

В приведенном выше запросе мы видим, что оператор создания таблицы заканчивается ON COMMIT DELETE ROWS, что означает, что таблица будет удаляться после каждой транзакции. Так что это основано на транзакциях. Если мы хотим, чтобы созданная таблица была основана на сеансе, это означает, что данные будут присутствовать во временной таблице для всего сеанса, а не только для текущей транзакции. Затем нам нужно изменить запрос SELECT, добавив предложение ON COMMIT PRESERVE ROWS в конце инструкции.

CREATE GLOBAL TEMPORARY TABLE temp_table (
id NUMBER PRIMARY KEY,
description VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

Теперь запустим запрос в SQL Developer.

Как видно на снимке экрана выше, временная таблица создана.

Как вставить данные во временную таблицу Oracle?

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

ВСТАВИТЬ В temp_table VALUES (1, "Первая запись");

Теперь запустим запрос в SQL Developer.

Как мы видим на снимке экрана выше, строка была вставлена ​​в таблицу.

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

ДО СОВЕРШЕНИЯ

ВЫБРАТЬ * ИЗ temp_table;

Запустим запрос в SQL Developer.

На скриншоте выше мы видим содержимое таблицы. Давайте теперь зафиксируем транзакцию и проверим строки, присутствующие в таблице.

ПОСЛЕ СОВЕРШЕНИЯ

ВЫБРАТЬ COUNT(*) FROM temp_table;

Давайте посмотрим на результат в SQL Developer.

Как видно на снимке экрана, данные успешно удалены, так как счетчик равен нулю.

Как удалить?

Если таблица создана с клешнями ON COMMIT DELETE ROWS, то если мы просто зафиксируем, данные будут удалены, как уже было показано в предыдущем разделе. Если таблица создана с помощью ON DELETE PRESERVE ROWS, мы будем использовать оператор DELETE для удаления строк временной таблицы. Давайте посмотрим на запрос для того же самого.

УДАЛИТЬ из temp_table2 ГДЕ >

Запустим запрос в SQL Developer.

Как видно на снимке экрана, строка успешно удалена.

ВЫБЕРИТЕ COUNT(*) ИЗ TEMP_TABLE2;

Как мы видим на скриншоте, количество присутствующих равно нулю. Следовательно, оператор удаления успешно удалил содержимое таблицы.

Рекомендуемые статьи

Это руководство по временным таблицам Oracle. Здесь мы также обсуждаем введение и как временная таблица работает в оракуле? вместе с тем, как удалять и вставлять данные во временную таблицу оракула. Вы также можете ознакомиться со следующими статьями, чтобы узнать больше –

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