Oracle создать временную таблицу oracle
Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. From Oracle 8i onward, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.
Временные таблицы
Oracle позволяет создавать временные таблицы для хранения данных только на протяжении сеанса или даже одной транзакции. После окончания сеанса или транзакции таблица очищается (все строки автоматически удаляются). Временные таблицы удобны, когда вы имеете дело со сложными запросами или транзакциями, которые требуют хранения временной информации перед записью ее в постоянные таблицы.
Данные во временных таблицах не могут быть резервированы как данные постоянных таблиц. Временным таблицам или индексам до их создания никаких сегментов данных или индексов автоматически не выделяются, как это происходит с постоянными таблицами и индексами. Место для временных таблиц выделяется во временных сегментах только после первого применения команды INSERT с этими таблицами.
Временные таблицы повышают производительность транзакций, включающих сложные запросы. Одним из традиционных альтернатив сложным запросам является использование представлений для упрощения сложных запросов, но представление должно выполняться при каждом обращении к нему, что во многих случаях сводит на нет все преимущества. Временные таблицы — отличное решение для подобных случаев, поскольку они могут быть созданы как результат сложного оператора SELECT, использованного в конкретном сеансе или транзакции, и автоматически очищаются после сеанса.
Важно! Хотя Oracle не анализирует данные временных таблиц на предмет распределения данных, это не представляет проблемы для эффективной обработки запросов, потому что временные таблицы могут хранить постоянно доступные соединения и прочую информацию в одном удобном месте. Вместо многократного выполнения сложных запросов к постоянным таблицам можно многократно обращаться к этим таблицам.
Временные таблицы создаются во временном табличном пространстве пользователя и им выделяются временные сегменты только после первого оператора INSERT, обращенного к временной таблице. После завершения транзакции или окончания сеанса они освобождаются — в зависимости от того, как временная таблица была определена.
Ниже перечислены некоторые привлекательные свойства временных таблиц с точки зрения администратора баз данных Oracle.
- Временные таблицы существенно сокращают объем действий, связанных с журналами повторного выполнения, которые генерируются транзакциями. Журналы повторного выполнения заполняются не так быстро при использовании временных таблиц во время сложных транзакций.
- Временные таблицы могут быть проиндексированы для повышения производительности.
- Сеансы могут обновлять, вставлять и удалять данные во временных таблицах точно так же, как и в постоянных.
- Данные автоматически удаляются из временной таблицы по окончании сеанса или транзакции.
- На временных таблицах можно определять ограничения.
- Разные пользователи могут обращаться к одной и той же временной таблице, причем каждый из них видит данные только своего собственного сеанса.
- Временные таблицы обеспечивают эффективный доступ к данным, поскольку сложные запросы не приходится выполнять многократно.
- Минимальный объем блокировок временных таблицы означает более эффективную обработку запросов.
- Структура таблицы сохраняется после удаления данных, что помогает их использовать в будущем.
Private Temporary Tables (18c+)
A new variation of temporary tables has been introduced in Oracle 18c. A private temporary table is a memory-based temporary table that is dropped at the end of the session or transaction depending on the setup. You can read more about them here.
Простые таблицы Oracle Database, которые мы описывали ранее, удовлетворяют большинству потребностей приложений в данных, но это не единственный вид таблиц, которые Oracle позволяет создавать. Вдобавок можно создавать несколько видов специализированных таблиц, таких как временные таблицы, внешние таблицы и индекс-таблицы. В данной публикации дадим обзор именно временным таблицам базы данных Oracle .
Global Temporary Tables and Redo
If you've read the previous section, you will already know the relationship between global temporary tables and redo. The data in a GTT is written to the temporary tablespace, which is not directly protected by redo, so using a GTT improves performance by reducing redo generation. Unfortunately, prior to Oracle 12c, all undo associated with DML against a GTT is written to the normal undo tablespace, which is itself protected by redo. As a result, using a GTT reduces the amount of redo generation, but does not eliminate it. Another why of describing this is, using a GTT removes direct redo generation, but not indirect redo generation cause by undo.
The following code creates a conventional table, populates it and checks the amount of redo generated by the transaction.
We now repeat the previous test, but this time using a GTT.
We can see we have created an order of magnitude less redo when using the GTT, but we have not eliminated it.
Miscellaneous Features
- If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no affect on the data of other sessions.
- Data in temporary tables is stored in temp segments in the temp tablespace.
- Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
- Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.
- Views can be created against temporary tables and combinations of temporary and permanent tables.
- Temporary tables can have triggers associated with them.
- Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
- Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.
- There are a number of restrictions related to temporary tables but these are version specific.
Temporary Tables
Oracle support two types of temporary tables.
- Global Temporary Tables : Available since Oracle 8i and subject of this article.
- Private Temporary Tables : Available since Oracle 18c. Discussed here.
Создание временной таблицы транзакции
В отличие от временной таблицы сеанса, временные таблицы транзакций специфичны для отдельной транзакции. Как только транзакция фиксируется или откатывается, данные из этой таблицы удаляются. Как создать временную таблицу транзакции на языке SQL рассмотрим на примере:
Опция ON COMMIT DELETE ROWS ясно указывает на то, что данные в этой таблице должны оставаться только на протяжении транзакции, использующей эту временную таблицу.
This is the way I used to create temporary table but I got error, is there any other way to perform this task?
@tbone's answer shows what you should be doing, but to explain what you're seeing: You can't do this because at the time the procedure is compiled temp doesn't exist yet. The compiler doesn't attempt to parse the dynamic SQL, not least because it has no idea if it will work at runtime. The only way this approach would work is if the insert was turned into dynamic SQL too; but this is not how temporary tables work in Oracle so don't do it like this.
5 Answers 5
Just create it first (once, outside of your procedure), and then use it in your procedure. You don't want to (try to) create it on every call of the procedure.
But why? I found it strange. I.E. I need a "virtual" table. Can I create it inside the procedure itself?
@Gik25 whether you use an actual temp table or use other approaches depends on your specific situation. Maybe post a new question with your specifics, you'll most likely get some good responses.
I second @Revious I have a need to create, use & destroy temp tables with in a Proc, Oracle is not liking it, any ideas ?
I have edited this answer as it was wrong. I am a recent MSSQL convert and because of the way oracle implements global temp tables, if you really DO need to use temp tables, creating them once and leaving them there is the way to go. Unless you use dynamic sql in your procs exclusively (have fun debugging), you will not be able to successfully compile your package unless the tables referenced already exist. Oracle validates any objects referenced in methods that you attempt to compile, which is why you got the 942 error. I love the way Oracle manages scope with these global temp tables. That, alone, sold me on the idea.
This is generally a really bad practice in Oracle. Tables do not simply disappear willy-nilly. Why add extra (slow) code, force dynamic SQL everywhere, and lose all hope of configuration management? Temporary tables are almost never useful anyway. They can usually be replaced by regular tables, inline views, or a collection.
Nevermind my answer, I am a recent MSSQL convert. In the last week, I have come to a better understanding of Oracles implementation of temp tables. The more I use Oracle, the more I like it.
I'm working with an Oracle 10g database, and I want to extract a group of records from one table, and then use that for pulling records out of a bunch of related tables.
If this were T-SQL, I'd do it something like this:
However, all the helpful pages I look at make this look like a lot more work than it could possibly be, so I think I must be missing something obvious.
(BTW, instead of running this as one script, I'll probably open a session in Oracle SQL Developer, create the temp table, and then run each query off it, exporting them to CSV as I go along. Will that work?)
Why do you need the temp table step? You're just replacing one select with another. Is the initial select very expensive - Or is it in case a concurrent transaction changes things?
I was simplifying -- what I was actually trying to do was grab the last 100 patients (minus a bit of duplication). But talking it out below reminded me that I could just grab 100 patients starting at midnight this morning, and not have to worry about SYSDATE changing as I went along. :-)
There are nothing about date restrictions in question text. Can you please update question with details of selection criterious?
You might want to look at some of the other, non-default, transaction isolation levels available. I believe serializable and read only both show results as of the beginning of the transaction. Rows inserted after the transaction started would not appear.
Creation of Global Temporary Tables
The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction.
The ON COMMIT DELETE ROWS clause indicates the data should be deleted at the end of the transaction, or the end of the session.
In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should persist beyond the end of the transaction. They will only be removed at the end of the session.
Global Temporary Tables and Undo
Although the data in a GTT is written to the temporary tablespace, the associated undo is still written to the normal undo tablespace, which is itself protected by redo, so using a GTT does not reduce undo and the redo associated with protecting the undo tablespace.
The following code creates a conventional table, populates it and checks the amount of undo used by the transaction.
We now repeat the previous test, but this time using a GTT.
We can see, there is no significant difference in the undo used.
Oracle 12c introduced the concept of Temporary Undo, allowing the undo for a GTT to be written to the temporary tablespace, thereby reducing undo and redo.
3 Answers 3
Oracle has temporary tables, but they require explicit creation:
The data in a temporary table is private for the session that created it and can be session-specific or transaction-specific. If data is not to be deleted until the session ends, you need to use ON COMMIT PRESERVE ROWS at the end of the create statement. There's also no rollback or commit support for them.
I see no need for temp tables in the example you gave - it risks that updates made to the APPOINTMENTS table since the temp table was populating won't be reflected. Use IN/EXISTS/JOIN:
JOINing risks duplicates if there are more than one APPOINTMENT records associated to a single PERSON record, which is why I added the DISTINCT.
That was the way I was considering doing it at first, but I'm filtering it for apptDate < SYSDATE, so it could change between the first table I extract and the last one. I support I could just do it for apptDate is yesterday, as that would avoid the problem in question. thanks!
Oracle doesn't have the facility to casually create temporary tables in the same way as SQL Server. You have to create the table explicitly in the database schema ( create global tempory table ). This also means that you need permissions that allow you to create tables, and the script must explicitly be deployed as a database change. The table is also visible in a global name space.
This is a significant idiomatic difference between Oracle and SQL Server programming. Idiomatic T-SQL can make extensive use of tempory tables and genuine requirements to write procedural T-SQL code are quite rare, substantially because of this facility.
Idiomatic PL/SQL is much quicker to drop out to procedural code, and you would probably be better off doing this than trying to fake temporary tables. Note that PL/SQL has performance oriented constructs such as flow control for explicit parallel processing over cursors and nested result sets (cursor expressions); recent versions have a JIT compiler.
You have access to a range of tools to make procedural PL/SQL code run quickly, and this is arguably idiomatic PL/SQL programming. The underlying paradigm is somewhat different from T-SQL, and the approach to temporary tables is one of the major points where the system architecture and programming idioms differ.
В ременные таблицы впервые появились в 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$ таблице и поэтому информация в нём более верна.
Создание временной таблицы сеанса
Рассмотрим пример создания временной таблицы Oracle Database на SQL, которая существует на протяжении сеанса; для этого используется опция ON COMMIT DELETE ROWS:
Опция ON COMMIT PRESERVE ROWS в предыдущем примере указывает на то, что данные таблицы сохраняются на протяжении сеанса, а не на протяжении транзакции.
Читайте также: