SQL-Ex blog
В этом руководстве изучаются временные таблицы PostgreSQL и их использование. Рассматривается базовый синтаксис и приводится несколько примеров, поясняющих их функциональность.
Определение
Временная таблица PostgreSQL, как подразумевает ее название, является таблицей, которая существует для данной сессии и автоматически удаляется, когда эта сессия закрывается. Временная таблица связана с данной сессией, и к ней нет доступа из другой сессии. Запрос из другой сессии вернет ошибку, если она попытается получить доступ к временной таблице, созданной не в этой сессии.
- Временная таблица относится к данной сессии, и она недоступна для других сессий.
- Временная таблица автоматически удаляется при закрытии сессии.
- Временная таблица не принадлежит ни к какой схеме.
- PostgreSQL допускает совпадение имени временной таблицы с именем постоянной таблицы в той же базе данных.
- При совпадении имен временной и постоянной таблиц, доступа к постоянной таблице не будет, пока временная таблица не будет удалена из базы или сессии.
Базовый синтаксис
CREATE TEMPORARY TABLE имя_таблицы(
список_столбцов
);
CREATE TEMP TABLE имя_таблицы(
список_столбцов
);
PostgreSQL поддерживает оба ключевых слова Temporary и Temp, которые являются взаимозаменяемыми при создании временной таблицы.
- После ключевых слов CREATE TEMPORARY TABLE сначала указывается имя таблицы.
- Затем следует список столбцов таблицы.
Пример 1 — Открываем сессию, создаем новую временную таблицу в базе данных по умолчанию «postgres». Параллельно открываем другую сессию и пытаемся получить доступ к временной таблице, созданной в предыдущей сессии. Выясним, что доступа к таблице нет.
CREATE TEMP TABLE mytable(
name varchar,
age integer
);
Пример 2 — Временная таблица автоматически удаляется при закрытии сессии.
Сначала мы создадим новую таблицу в базе данных по умолчанию «postgres», затем закроем сессию и, наконец, попытаемся снова получить к ней доступ после повторного открытия новой сессии.
Открывает новую сессию и обнаруживает, что таблица не существует. Это доказывает тот факт, что таблица не существует вне сессии, в которой была создана.
Пример 3 — Создаем две таблицы, одну временную и одну постоянную с тем же именем. После создания мы пытаемся получить доступ к таблицам, но доступной является только временная таблица.
Создаем таблицу с именем «students» и выполняем к ней запрос. Пока поведение ожидаемое.
CREATE TABLE students(
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL
);
Затем создаем временную таблицу с тем же именем «students» и снова пытаемся выполнить к ней запрос. В этом сочетании видно, что временная таблица имеет приоритет, и возвращается она, а не постоянная таблица. Тут еще раз важно подчеркнуть, что временная таблица привязана к сессии и теряется при закрытии сессии, в отличие от постоянных таблиц.
CREATE TEMP TABLE students(
student_id INT
);
Пример 4 — Временная не принадлежит какой-либо схеме, даже схеме «public». Временная таблица «students» принадлежит схеме с именем ‘pg_temp_4’, которое является алиасом временной схемы текущей сессии в отличие от постоянной таблицы, которая принадлежит схеме по умолчанию «public», что показано в предыдущем примере.
Удаление временной таблицы
Для удаления временной таблицы используется та же самая команда, что и для постоянной таблицы, в неизменном синтаксисе. Давайте удалим таблицу, созданную в предыдущем примере. Мы сначала выполним команду DROP TABLE с последующим выводом списка всех таблиц при помощи команды «\dt», чтобы убедиться в том, что временная таблица пропала, а постоянная по-прежнему возвращается.
Drop table students;
Заключение
В этой статье рассматривалось понятие временной таблицы в PostgreSQL и ее использование. Так как временные таблицы могут бы очень полезны в ряде случаев для хранения временных данных, они часто используются в исследовательских и аналитических проектах.
Подзапросы и временные таблицы
Во всех рассмотренных ранее примерах значения столбцов сравниваются с выражением, константой или набором констант. Кроме таких возможностей сравнения язык Transact-SQL позволяет сравнивать значения столбца с результатом другой инструкции SELECT. Такая конструкция, где предложение WHERE инструкции SELECT содержит одну или больше вложенных инструкций SELECT, называется . Первая инструкция SELECT подзапроса называется внешним запросом (outer query), а внутренняя инструкция (или инструкции) SELECT, используемая в сравнении, называется вложенным запросом (inner query). Первым выполняется вложенный запрос, а его результат передается внешнему запросу. Вложенные запросы также могут содержать инструкции INSERT, UPDATE и DELETE.
Существует два типа подзапросов: независимые и связанные. В независимых подзапросах вложенный запрос логически выполняется ровно один раз. Связанный запрос отличается от независимого тем, что его значение зависит от переменной, получаемой от внешнего запроса. Таким образом, вложенный запрос связанного подзапроса выполняется каждый раз, когда система получает новую строку от внешнего запроса. В этом разделе приводится несколько примеров независимых подзапросов. Связанные подзапросы рассматриваются далее в следующей статье совместно с оператором соединения JOIN.
Независимый подзапрос может применяться со следующими операторами:
- операторами сравнения;
- оператором IN;
- операторами ANY и ALL.
Подзапросы и операторы сравнения
Использование оператора равенства (=) в независимом подзапросе показано в примере ниже:
USE SampleDb; SELECT FirstName, LastName FROM Employee WHERE DepartamentNumber = (SELECT Number FROM Department WHERE DepartmentName = 'Исследования');В этом примере происходит выборка имен и фамилий сотрудников отдела ‘Исследования’. Результат выполнения этого запроса:
В примере выше сначала выполняется вложенный запрос, возвращая номер отдела разработки (d1). После выполнения внутреннего запроса подзапрос в примере можно представить следующим эквивалентным запросом:
USE SampleDb; SELECT FirstName, LastName FROM Employee WHERE DepartamentNumber = 'd1';В подзапросах можно также использовать любые другие операторы сравнения, при условии, что вложенный запрос возвращает в результате одну строку. Это очевидно, поскольку невозможно сравнить конкретные значения столбца, возвращаемые внешним запросом, с набором значений, возвращаемым вложенным запросом. В последующем разделе рассматривается, как можно решить проблему, когда результат вложенного запроса содержит набор значений.
Подзапросы и оператор IN
Оператор IN позволяет определить набор выражений (или констант), которые затем можно использовать в поисковом запросе. Этот оператор можно использовать в подзапросах при таких же обстоятельствах, т.е. когда вложенный запрос возвращает набор значений. Использование оператора IN в подзапросе показано в примере ниже:
USE SampleDb; SELECT FirstName, LastName FROM Employee WHERE DepartamentNumber IN (SELECT Number FROM Department WHERE DepartmentName = 'Исследования')Этот запрос аналогичен предыдущему. Каждый вложенный запрос может содержать свои вложенные запросы. Подзапросы такого типа называются подзапросами с многоуровневым вложением. Максимальная глубина вложения (т.е. количество вложенных запросов) зависит от объема памяти, которым компонент Database Engine располагает для каждой инструкции SELECT. В случае подзапросов с многоуровневым вложением система сначала выполняет самый глубокий вложенный запрос и возвращает полученный результат запросу следующего высшего уровня, который в свою очередь возвращает свой результат запросу следующего уровня над ним и т.д. Конечный результат выдается запросом самого высшего уровня.
Запрос с несколькими уровнями вложенности показан в примере ниже:
USE SampleDb; SELECT FirstName, LastName FROM Employee WHERE ID IN (SELECT EmpId FROM Works_on WHERE ProjectNumber IN (SELECT Number FROM Project WHERE ProjectName = 'Apollo') )В этом примере происходит выборка фамилий всех сотрудников, работающих над проектом Apollo. Самый глубокий вложенный запрос выбирает из таблицы ProjectNumber значение p1. Этот результат передается следующему вышестоящему запросу, который обрабатывает столбец ProjectNumber в таблице Works_on. Результатом этого запроса является набор табельных номеров сотрудников: (10102, 29346, 9031, 28559). Наконец, самый внешний запрос выводит фамилии сотрудников, чьи номера были выбраны предыдущим запросом.
Подзапросы и операторы ANY и ALL
Операторы ANY и ALL всегда используются в комбинации с одним из операторов сравнения. Оба оператора имеют одинаковый синтаксис:
Параметр operator обозначает оператор сравнения, а параметр query — вложенный запрос. Оператор ANY возвращает значение true (истина), если результат соответствующего вложенного запроса содержит хотя бы одну строку, удовлетворяющую условию сравнения. Ключевое слово SOME является синонимом ANY. Использование оператора ANY показано в примере ниже:
USE SampleDb; SELECT DISTINCT EmpId, ProjectNumber, Job FROM Works_on WHERE EnterDate > ANY (SELECT EnterDate FROM Works_on);В этом примере происходит выборка табельного номера, номера проекта и названия должности для сотрудников, которые не затратили большую часть своего времени при работе над одним из проектов. Каждое значение столбца EnterDate сравнивается со всеми другими значениями этого же столбца. Для всех дат этого столбца, за исключением самой ранней, сравнение возвращает значение true (истина), по крайней мере, один раз. Строка с самой ранней датой не попадает в результирующий набор, поскольку сравнение ее даты со всеми другими датами никогда не возвращает значение true (истина). Иными словами, выражение «EnterDate > ANY (SELECT EnterDate FROM Works_on)» возвращает значение true, если в таблице Works_on имеется любое количество строк (одна или больше), для которых значение столбца EnterDate меньше, чем значение EnterDate текущей строки. Этому условию удовлетворяют все значения столбца EnterDate, за исключением наиболее раннего.
Оператор ALL возвращает значение true, если вложенный запрос возвращает все значения, обрабатываемого им столбца.
Настоятельно рекомендуется избегать использования операторов ANY и ALL. Любой запрос с применением этих операторов можно сформулировать лучшим образом посредством функции EXISTS, которая рассматривается далее в следующей статье. Кроме этого, семантическое значение оператора ANY можно легко принять за семантическое значение оператора ALL и наоборот.
Временные таблицы
— это объект базы данных, который хранится и управляется системой базы данных на временной основе. Временные таблицы могут быть локальными или глобальными. Локальные временные таблицы представлены физически, т.е. они хранятся в системной базе данных tempdb. Имена временных таблиц начинаются с префикса #, например #table_name.
Временная таблица принадлежит создавшему ее сеансу, и видима только этому сеансу. Временная таблица удаляется по завершению создавшего ее сеанса. (Также локальная временная таблица, определенная в хранимой процедуре, удаляется по завершению выполнения этой процедуры.)
Глобальные временные таблицы видимы любому пользователю и любому соединению и удаляются после отключения от сервера базы данных всех обращающихся к ним пользователей. В отличие от локальных временных таблиц имена глобальных временных таблиц начинаются с префикса ##. В примере ниже показано создание временной таблицы, называющейся project_temp, используя две разные инструкции языка Transact-SQL:
USE SampleDb; CREATE TABLE #project_temp ( Number NCHAR(4) NOT NULL, Name NCHAR(25) NOT NULL ); -- Аналог предыдущей инструкции со вставкой -- данных во временную таблицу из существующей -- таблицы Project SELECT Number, ProjectName INTO #project_temp FROM Project;Два этих подхода похожи в том, что в обоих создается локальная временная таблица #project_temp. При этом таблица, созданная инструкцией CREATE TABLE, остается пустой, а созданная инструкцией SELECT заполняется данными из таблицы Project.
1.2.9. Создание временных таблиц
Возможности SQL не безграничны, и тем более не безгранично воображение программистов. Иногда очень сложно соорудить запрос, который выполнял бы в базе данных необходимые действия за один проход, намного проще выполнить одно действие и на основании полученного результата выполнить следующее действие. Для хранения результата можно использовать временные таблицы, которые автоматически будут уничтожаться после выполнения запроса.
В MS SQL Server вы можете создавать локальные и глобальные временные таблицы. Локальные временные таблицы будут видны только вашей сессии, а глобальные таблицы видны всем сессиям. При этом и те, и другие таблицы, уничтожаются после завершения сессии.
Чтобы таблица стала временной локально видимой, перед именем нужно поставить символ #, например, в следующем примере создается временная таблица #TestTable с одним только полем идентификатором, который является первичным ключом:
CREATE TABLE #TestTable ( id INT PRIMARY KEY )
Таблица будет существовать только во время выполнения одной сессии, и работать с ней сможете только вы. Например, вы написали большой сценарий, выполняющий определенные действия (например, процедуру, хранящуюся на сервере). Если в этом сценарии создать временную таблицу, то она будет существовать, только пока выполняется сценарий. После этого она автоматически уничтожается.
Попробуйте создать таблицу. Теперь попробуйте получить все данные из таблицы с помощью запроса:
SELECT * FROM #TestTable
В результате должна появиться пустая таблица (мы только создали таблицу, но не наполняли ее) из одной колонки с именем id.
Закройте соединение с базой данных или просто перезапустите программу, которую вы используете для отладки запросов. Снова выполните запрос выборки данных из временной таблицы. В ответ должна появиться ошибка: «Invalid object name ‘#TestTable'» (неправильное имя объекта #TestTable).
Давайте снова создадим временную таблицу. Таблица будет создана, а если бы такой объект уже существовал, то мы увидели бы ошибку. Проверьте существование временной таблицы с помощью запроса выборки данных. Ошибок не должно быть. Откройте новое соединение или еще одну копию программы отладки запросов и выполните выборку данных из временной таблицы в этой копии программы. В ответ должна снова появиться ошибка, о не существовании объекта, потому что в другой программе создается новая сессия, и вы не можете видеть, что локальные таблицы других пользователей.
Глобально видимые таблицы работают также, но в момент существования видны всем подключенным к серверу клиентам. Имена таких таблиц начинаются с двух символов ##. Следующий пример создает глобально временную таблицу:
CREATE TABLE ##TestTable ( id INT PRIMARY KEY )
Такая таблица будет существовать, пока существует сеанс с базой данных, создавшей таблицу, и все это время любой пользователь может работать с глобально временной таблицей.
Во временных таблицах нельзя использовать внешние ключи. Нет, ошибки не будет, и при попытке создать таблицу, она будет создана корректно, но попытки создать внешний ключ будут игнорироваться. Видимо, это связано с тем, что из-за внешнего ключа усложняется удаление таблиц и нужно следовать определенной последовательности. Сервер SQL от Microsoft не может себе позволить такую роскошь, да наверно и другие тоже.
Не смотря на то, что мы рассматриваем тему временных таблиц, я настоятельно рекомендую вам не использовать их в своих проектах. Если вы думаете, что в определенном месте они помогут вам решить проблему, то попробуйте найти другое решение. Если новое решение не приходит на ум, нужно продолжать искать более простой способ. Очень часто программисты используют временные таблицы только из-за того, что не могут объединить некоторые действия в один запрос. Данная книга направлена на то, чтобы научить вас не просто использовать SQL, а делать это эффективно. Временные таблицы, на мой взгляд, являются самым не эффективным решением проблемы.
Единственный случай, когда я использую временные таблицы, когда выполняются следующие условия в условии задачи:
- таблица интенсивно пополняется или обновляется;
- отчет создается продолжительное время и все это время, обрабатываемые данные должны быть статичными.
Чтобы в течение создания отчета данные были неизменными, их можно выбрать во временную таблицу и потом уже над этими данными выполнять формирование выходных данных. Есть еще один способ решение задачи – блокировка. Можно заблокировать таблицу для изменений, но это не очень хорошее решение, когда происходит интенсивная работа изменения и добавления данных. После блокировки таблицы, пользователи уже не смогут производить модификаций, что не всегда приемлемо. В такой задаче лучше подумать о возможном использовании временных таблиц.
Возможно, есть и другие задачи, когда временные таблицы могут помочь в решении задачи, но я в своей практике с ними не встречался.
Временные таблицы выделенного пула SQL в Azure Synapse Analytics
В этой статье содержатся важные рекомендации по использованию временных таблиц и приводятся основные концепции временных таблиц уровня сеанса.
На основе сведений, содержащихся в этой статье, вы сможете разбить код на модули, чтобы улучшить его повторное использование и повысить удобство управления.
Что собой представляют временные таблицы
Временные таблицы удобны при обработке данных — особенно во время преобразования, где промежуточные результаты являются временными. В выделенном пуле SQL временные таблицы существуют на уровне сеанса.
Их можно просмотреть только в сеансе, в котором они были созданы. После закрытия сеанса они автоматически удаляются.
Временные таблицы позволяют оптимизировать производительность, так как их результаты записываются в локальное, а не удаленное хранилище.
Временные таблицы в выделенном пуле SQL
Временные таблицы в ресурсе выделенного пула SQL позволяют оптимизировать производительность, так как их результаты записываются в локальное, а не удаленное хранилище.
Создание временной таблицы
Для создания временной таблицы к имени таблицы добавляется префикс # . Пример:
CREATE TABLE #stats_ddl ( [schema_name] NVARCHAR(128) NOT NULL , [table_name] NVARCHAR(128) NOT NULL , [stats_name] NVARCHAR(128) NOT NULL , [stats_is_filtered] BIT NOT NULL , [seq_nmbr] BIGINT NOT NULL , [two_part_name] NVARCHAR(260) NOT NULL , [three_part_name] NVARCHAR(400) NOT NULL ) WITH ( DISTRIBUTION = HASH([seq_nmbr]) , HEAP ) Временные таблицы можно также создать с помощью CTAS точно таким же образом:
CREATE TABLE #stats_ddl WITH ( DISTRIBUTION = HASH([seq_nmbr]) , HEAP ) AS ( SELECT sm.[name] AS [schema_name] , tb.[name] AS [table_name] , st.[name] AS [stats_name] , st.[has_filter] AS [stats_is_filtered] , ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [seq_nmbr] , QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name]) AS [two_part_name] , QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name]) AS [three_part_name] FROM sys.objects AS ob JOIN sys.stats AS st ON ob.[object_id] = st.[object_id] JOIN sys.stats_columns AS sc ON st.[stats_id] = sc.[stats_id] AND st.[object_id] = sc.[object_id] JOIN sys.columns AS co ON sc.[column_id] = co.[column_id] AND sc.[object_id] = co.[object_id] JOIN sys.tables AS tb ON co.[object_id] = tb.[object_id] JOIN sys.schemas AS sm ON tb.[schema_id] = sm.[schema_id] WHERE 1=1 AND st.[user_created] = 1 GROUP BY sm.[name] , tb.[name] , st.[name] , st.[filter_definition] , st.[has_filter] ) ; CTAS — мощная команда, которая особенно эффективна при использовании пространства журнала транзакций.
Удаление временных таблиц
При создании сеанса не должно быть ни одной временной таблицы.
Если вы вызываете одну и ту же хранимую процедуру, в которой создается временная таблица с тем же именем, чтобы обеспечить успешное выполнение инструкций CREATE TABLE , можно использовать простую проверку на наличие с помощью DROP , как показано в следующем примере.
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL BEGIN DROP TABLE #stats_ddl END Для согласованности кода целесообразно использовать этот шаблон как для обычных, так и для временных таблиц. Рекомендуется также удалить временные таблицы с помощью DROP TABLE , когда вы закончите работу с ними в коде.
При разработке хранимой процедуры команды удаления обычно помещаются вместе в конце процедуры, чтобы гарантировать удаление объектов.
DROP TABLE #stats_ddl Разбиение кода на модули
Так как временные таблицы можно просмотреть где угодно в сеансе пользователя, эту возможность можно использовать для модульной организации кода приложения.
Например, в представленной ниже хранимой процедуре создается DDL для обновления всей статистики в базе данных по имени статистического показателя.
CREATE PROCEDURE [dbo].[prc_sqldw_update_stats] ( @update_type tinyint -- 1 default 2 fullscan 3 sample 4 resample ,@sample_pct tinyint ) AS IF @update_type NOT IN (1,2,3,4) BEGIN; THROW 151000,'Invalid value for @update_type parameter. Valid range 1 (default), 2 (fullscan), 3 (sample) or 4 (resample).',1; END; IF @sample_pct IS NULL BEGIN; SET @sample_pct = 20; END; IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL BEGIN DROP TABLE #stats_ddl END CREATE TABLE #stats_ddl WITH ( DISTRIBUTION = HASH([seq_nmbr]) ) AS ( SELECT sm.[name] AS [schema_name] , tb.[name] AS [table_name] , st.[name] AS [stats_name] , st.[has_filter] AS [stats_is_filtered] , ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [seq_nmbr] , QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name]) AS [two_part_name] , QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name]) AS [three_part_name] FROM sys.objects AS ob JOIN sys.stats AS st ON ob.[object_id] = st.[object_id] JOIN sys.stats_columns AS sc ON st.[stats_id] = sc.[stats_id] AND st.[object_id] = sc.[object_id] JOIN sys.columns AS co ON sc.[column_id] = co.[column_id] AND sc.[object_id] = co.[object_id] JOIN sys.tables AS tb ON co.[object_id] = tb.[object_id] JOIN sys.schemas AS sm ON tb.[schema_id] = sm.[schema_id] WHERE 1=1 AND st.[user_created] = 1 GROUP BY sm.[name] , tb.[name] , st.[name] , st.[filter_definition] , st.[has_filter] ) SELECT CASE @update_type WHEN 1 THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+');' WHEN 2 THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH FULLSCAN;' WHEN 3 THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH SAMPLE '+CAST(@sample_pct AS VARCHAR(20))+' PERCENT;' WHEN 4 THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH RESAMPLE;' END AS [update_stats_ddl] , [seq_nmbr] FROM #stats_ddl ; GO На этом этапе единственное выполненное действие заключается в создании хранимой процедуры, в которой создается временная таблица #stats_ddl с использованием инструкций DDL.
В этой хранимой процедуре удаляется существующая таблица #stats_ddl . Это обеспечивает бесперебойную работу таблицы в случае ее повторного запуска на протяжении сеанса.
Но так как в конце хранимой процедуры нет команды DROP TABLE , после выполнения этой процедуры созданная таблица сохранится и ее можно будет читать вне хранимой процедуры.
В отличие от других баз данных SQL Server, в выделенном пуле SQL временную таблицу можно использовать вне процедуры, в которой она была создана. Временные таблицы выделенного пула SQL можно использовать в любом месте внутри сеанса. Это может улучшить модульность и управляемость кода, как показано в следующем примере.
EXEC [dbo].[prc_sqldw_update_stats] @update_type = 1, @sample_pct = NULL; DECLARE @i INT = 1 , @t INT = (SELECT COUNT(*) FROM #stats_ddl) , @s NVARCHAR(4000) = N'' WHILE @i Ограничения временной таблицы
В выделенном пуле SQL есть несколько ограничений, касающихся реализации временных таблиц. В настоящее время поддерживаются временные таблицы, которые можно просмотреть только в сеансе. Глобальные временные таблицы не поддерживаются.
Также для временных таблиц нельзя создавать представления. Временные таблицы можно создавать только с помощью хэша или циклического распределения. Распределение реплицированных временных таблиц не поддерживается.