Открыть сервис
Главная → Блог → Временная таблица

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

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

История

Концепция временных таблиц возникла вместе с развитием реляционных баз данных в 1970-х годах. Первые реализации появились в коммерческих СУБД, таких как Oracle и IBM DB2, в 1980-х годах. В стандарте SQL временные таблицы были впервые описаны в версии SQL:1992 (SQL2), где были определены два основных типа: локальные временные таблицы (видимые только в рамках одного сеанса) и глобальные временные таблицы (доступные всем сеансам, но с данными, привязанными к конкретному сеансу). В последующих версиях стандарта (SQL:1999, SQL:2003) спецификации были уточнены, включая поддержку временных таблиц с автоматическим удалением строк по окончании транзакции.

В российских разработках, например, в СУБД «Линтер» (АО «НПО РусБИТех») и «Postgres Professional» (компания-разработчик СУБД PostgreSQL для России, признана Минцифры РФ отечественным ПО), временные таблицы реализованы в соответствии с международными стандартами и активно используются в государственных и корпоративных информационных системах.

Классификация

Временные таблицы классифицируются по двум основным признакам: по области видимости и по времени жизни данных.

По области видимости

  • Локальные временные таблицы (local temporary tables). Создаются с использованием ключевого слова LOCAL TEMPORARY (или # в некоторых СУБД, например, в Microsoft SQL Server). Видимы только в рамках одного сеанса (сессии) пользователя. При завершении сеанса таблица автоматически удаляется. Разные пользователи могут создавать локальные временные таблицы с одинаковыми именами, не влияя друг на друга.
  • Глобальные временные таблицы (global temporary tables). Создаются с использованием ключевого слова GLOBAL TEMPORARY (или ## в Microsoft SQL Server). Определение таблицы (её структура) является общим для всех сеансов, но данные, хранящиеся в ней, видны только тому сеансу, который их вставил. При завершении сеанса данные для этого сеанса удаляются, но сама таблица остаётся доступной для других сеансов.

По времени жизни данных

  • На время сеанса (session-level). Данные сохраняются на протяжении всего сеанса пользователя. При завершении сеанса все строки, вставленные в рамках этого сеанса, удаляются.
  • На время транзакции (transaction-level). Данные сохраняются только в рамках одной транзакции. При завершении транзакции (коммите или откате) все строки, вставленные в таблицу в этой транзакции, автоматически удаляются. Этот режим задаётся директивой ON COMMIT DELETE ROWS при создании таблицы.

Устройство и характеристики

Временные таблицы физически хранятся в специальной области базы данных — обычно в системном пространстве таблиц (например, tempdb в Microsoft SQL Server, pg_temp в PostgreSQL, временное табличное пространство в Oracle). Это позволяет быстро создавать и удалять таблицы, не затрагивая основное хранилище данных.

Основные характеристики временных таблиц:

  • Автоматическое удаление. После завершения сеанса или транзакции таблица и её данные удаляются без явной команды DROP TABLE.
  • Изоляция данных. Данные разных сеансов не пересекаются, даже если таблица имеет одинаковое имя.
  • Индексы и ограничения. На временные таблицы можно создавать индексы, первичные ключи, внешние ключи (часто только на другие временные таблицы) и другие ограничения целостности.
  • Производительность. Операции с временными таблицами обычно выполняются быстрее, чем с постоянными, так как они не требуют журналирования изменений (в некоторых СУБД) и не участвуют в механизмах восстановления после сбоев.
  • Ограничения. Временные таблицы не могут быть использованы в репликации, не поддерживают триггеры на уровне базы данных (но могут поддерживать триггеры на уровне сеанса), и их структура не может быть изменена после создания (в большинстве СУБД).

Применение

Временные таблицы находят широкое применение в различных сценариях работы с базами данных.

Промежуточные вычисления

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

Разделение больших задач

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

Изоляция данных пользователей

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

Тестирование и отладка

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

Загрузка данных

При загрузке больших объёмов данных из внешних источников временные таблицы часто используются как промежуточное хранилище. Данные сначала загружаются во временную таблицу, проходят проверку и преобразование, а затем переносятся в постоянные таблицы. Это минимизирует время блокировки постоянных таблиц.

Примеры

В разных СУБД синтаксис создания временных таблиц может незначительно отличаться.

PostgreSQL

```sql -- Локальная временная таблица на время сеанса CREATE TEMPORARY TABLE temp_orders ( order_id INT, customer_name TEXT, total_amount NUMERIC );

-- Глобальная временная таблица на время транзакции CREATE GLOBAL TEMPORARY TABLE global_temp_orders ( order_id INT, customer_name TEXT, total_amount NUMERIC ) ON COMMIT DELETE ROWS; ```

Microsoft SQL Server

```sql -- Локальная временная таблица (префикс #) CREATE TABLE #temp_orders ( order_id INT, customer_name NVARCHAR(100), total_amount DECIMAL(10,2) );

-- Глобальная временная таблица (префикс ##) CREATE TABLE ##global_temp_orders ( order_id INT, customer_name NVARCHAR(100), total_amount DECIMAL(10,2) ); ```

Oracle

```sql -- Глобальная временная таблица на время сеанса CREATE GLOBAL TEMPORARY TABLE temp_orders ( order_id NUMBER, customer_name VARCHAR2(100), total_amount NUMBER ) ON COMMIT PRESERVE ROWS;

-- Глобальная временная таблица на время транзакции CREATE GLOBAL TEMPORARY TABLE temp_orders_txn ( order_id NUMBER, customer_name VARCHAR2(100), total_amount NUMBER ) ON COMMIT DELETE ROWS; ```

Критика и ограничения

Несмотря на широкое применение, временные таблицы имеют ряд недостатков:

  • Перегрузка системного пространства. При интенсивном использовании временных таблиц может произойти переполнение временного табличного пространства (например, tempdb в SQL Server), что приведёт к сбоям в работе всей СУБД.
  • Отсутствие постоянного хранения. Данные временных таблиц не сохраняются при перезапуске сервера, что делает их непригодными для долгосрочного хранения.
  • Ограничения на индексы и связи. Не все СУБД позволяют создавать сложные индексы или внешние ключи на временные таблицы, что может ограничивать их функциональность.
  • Сложность отладки. Поскольку временные таблицы автоматически удаляются, их содержимое трудно отследить в процессе отладки без специальных инструментов.
  • Риск фрагментации. Частое создание и удаление временных таблиц может привести к фрагментации временного табличного пространства, что снижает производительность.

Альтернативы

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

  • Общие табличные выражения (CTE, Common Table Expressions). Позволяют создавать временные наборы данных в рамках одного запроса без создания физической таблицы.
  • Подзапросы. Вложенные запросы могут заменить временные таблицы в простых сценариях.
  • Табличные переменные (в SQL Server). Легковесные объекты, похожие на временные таблицы, но с меньшими накладными расходами на управление.
  • Материализованные представления. Постоянные объекты, которые могут периодически обновляться и использоваться для хранения промежуточных результатов.

Источники

  1. Международный стандарт SQL:1992 (ISO/IEC 9075:1992), раздел «Temporary tables».
  2. Документация PostgreSQL: «CREATE TABLE» (раздел «Temporary Tables»).
  3. Документация Microsoft SQL Server: «CREATE TABLE (Transact-SQL)» (раздел «Temporary Tables»).
  4. Документация Oracle Database: «CREATE TABLE» (раздел «Temporary Tables»).
  5. К. Дж. Дейт. «Введение в системы баз данных», 8-е издание (2004), глава 10 «Временные таблицы».
  6. Материалы конференций «HighLoad++» и «Saint HighLoad++» (Россия), доклады об оптимизации использования временных таблиц в PostgreSQL и MS SQL Server.

BFOmetr — база данных и аналитика по компаниям России.

На главную BFOmetr →

Похожие материалы