4 select * from scott.emp where 1=0

Table created.

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

tkyte@TKYTE816> insert into temp table seseion select * from scott.emp; 14 rows created.

tkyte@TKYTE816> insert into temp table transaction select * from scott.emp; 14 rows created.

Мы только что поместили 14 строк в каждую из временных таблиц, и, как показывает следующий оператор, мы их видим :

tkyte@TKYTE816> select session cnt, transaction cnt

2 from (select count(*) session cnt from temp table session),

3 (select count(*) transaction cnt from temp table transaction);

SESSION CNT TRANSACTION CNT

14 14

tkyte@TKYTE816> commit;

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

tkyte@TKYTE816> select session cnt, transaction cnt

2 from (select count(*) session cnt from terop table session),

3 (select count(*) txansaction cnt from temp table transaction);

SESSION CNT TRANSACTION CNT

14 0

tkyte@TKYTE816> disconnect

Disconnected from 0racle8i Enterprise Edition Release 8.1.6.0.0 - Production

With the Partitioning option

JServer Release 8.1.6.0.0 - Production

tkyte@TKYTE816> connect tkyte/tkyte

Connected.

Поскольку мы создали новый сеанс, ни в одной из таблиц данных не будет:

tkyte@TKYTE816> select session cnt, transaction cnt

2 from (select count(*) session cnt from temp table session),

3 (select count(*) transaction cnt from temp table transaction);

SESSION CNT TRANSACTION CNT о о

Если у вас есть опыт работы с временными таблицами в СУБД SQL Server и/или Sybase, главное - учесть, что вместо выполнения оператора select x, у, г into #temp некая таблица для динамического создания и наполнения данными временной таблицы, необходимо следующее.

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

В процедуре выполнить оператор insert into temp (x, у, z) select x, у, z fiom некая таблица.

Подчеркну еще раз - цель не в том, чтобы создавать таблицы в хранимых процедурах по ходу выполнения. Этот способ для СУБД Oracle не подходит. Выполнение оператора ЯОД - действие дорогостоящее, и при работе процедур его надо избегать. Временные таблицы для приложения должны создаваться при его установке, а не по ходу работы.

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

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

быть целев1ми для внешнего ключа и для них нельзя задавать требование внешнего ключа;

нельзя использовать столбцы типа VARRAY и NESTED TABLE;

нельзя организовывать временные таблицы по индексу;

нельзя размещать временные таблицы в индексном или хеш-кластере;

нельзя фрагментировать временные таблицы;

для них нельзя сгенерировать статистическую информацию с помощью операто-

ра ANALYZE.

Один из недостатков временных таблиц в базе данных состоит в том, что оптимизатор не имеет по ним реальной статистической информации. При использовании оптимизатора основанного на стоимости (Cost-Based Optimizer - СВО), актуальная статистическая информация принципиально важна для успешной оптимизации. При отсутствии статистической информации оптимизатор будет делать предположения о распределении и объеме данных, а также избирательности индекса. Если эти предположения окажутся неверными, планы выполнения, сгенерированные для запросов, интенсивно использующих временные таблицы, окажутся далекими от оптимальности. Во многих случаях, правильное решение состоит в том, чтобы вообще не использовать временную таблицу, а использовать вместо нее подставляемое представление (inline view). Пример использования подставляемого представления можно найти в последнем из показанных выше операторов SELECT (их там используется два). При этом серверу Oracle доступна вся

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

Часто приходится видеть, как разработчики используют временные таблицы, усвоив при работе с другими СУБД, что соединение слишком большого количества таблиц в одном запросе - это плохо . От этой привычки при работе с СУБД Oracle надо избавляться. Лучше не пытаться превзойти оптимизатор, разбивая один запрос на три или четыре запроса, хранящих промежуточные результаты во временных таблицах, и затем соединяя эти временные таблицы, а просто использовать один запрос, извлекающий необходимую информацию. Ссылаться на множество таблиц в одном запросе - вполне допустимо; обходные пути с использованием временных таблиц для таких запросов в СУБД Oracle не нужны.

В других случаях, однако, использование временной таблицы бывает вполне оправдано. Например, я недавно написал приложение для наладонника Palm, синхронизирующее дневник Palm Pilot с календарной информацией, хранящейся в базе данных Oracle. Palm выдает мне список всех записей, изменившихся с момента последней синхронизации. Я должен б1л получить эти записи и сравнить с текущими данными в базе, изменить записи базы данных и затем сгенерировать список изменений, которые надо применить к информации в Palm. Это прекрасный пример того, когда временная таблица особенно полезна. Я использовал временную таблицу для хранения в базе данных изменений информации, выполненных в Palm. Затем выполнил хранимую процедуру, сравнивающую сгенерированные наладонником изменения с постоянно изменяющимися (и очень большими) постоянными таблицами, чтобы определить, какие изменения необходимо выполнить в данных СУБД Oracle, а какие - скопировать с СУБД Oracle в Palm. По этим данным приходится выполнять несколько проходов. Сначала я нахожу все записи, которые были изменены только на Palm и делаю соответствующие изменения в базе данных Oracle. Затем нахожу все записи, измененные с момента последней синхронизации как на Palm, так и в базе данных, и разбираюсь с ними. После этого я нахожу все записи, измененные только в базе данных, и вношу соответствующие изменения во временную таблицу. Наконец, программа синхронизации Palm выбирает изменения из временной таблицы и применяет их к информации наладонника. После отключения временные данные исчезают.

Я, однако, столкнулся с проблемой: поскольку постоянные таблицы были проанализированы, использовался оптимизатор, основанный на стоимости. Статистической информации по временной таблице не было (проанализировать временную таблицу можно, но статистическая информация при этом не собирается), и оптимизатор, основанный на стоимости делал о ней множество предположений . Я как разработчик знал среднее количество строк в этой таблице, распределение данных, избирательность индексов и т.п. Мне нужен б1л способ сообщить оптимизатору эти, более точные, сведения. Это легко сделать с помощью пакета DBMS STATS.

Поскольку оператор ANALYZE не собирает статистическую информацию о временной таблице, необходимо вручную заполнить словарь данных репрезентативной информацией о временных таблицах. Например, если в среднем в таблице будет 500 строк, со средним размером 100 байт и таблица будет занимать примерно 7 блоков, можно использовать следующий вызов: