Август 2004
Профессионалу разработчику
Том Кайт
Том Кайт: об ограничениях, CASE-операторах и курсорах
(On Constraints, CASE, and Cursors, By Tom Kyte)
Источник: журнал Oracle Magazine, November-December 2003
(http://otn.oracle.com/oramag/oracle/03-nov/o63asktom.html).
Наш эксперт рассказывает об ограничениях целостности,
фантазиях со временем, размещении файлов и курсоров.
После просмотра руководства Oracle9i Database Concepts
(имеется русский перевод: Сервер Oracle9i. Основные концепции. –
http://www.rdtex.ru/win/store.doc_set?setid=107 – прим. пер. ) и вашего сайта я так и не понял следующую концепцию:
"Для ограничения, которое определяется как откладываемое (deferrable), может устанавливаться одно из следующих состояний:
- Первоначально немедленное (initially immediate).
- Первоначально отложенное (initially deferred)."
Думаю, что понимаю, что такое отложенные ограничения, но не "откладываемые первоначально немедленные" и "откладываемые первоначально отложенные" ограничения. Объясните, пожалуйста, различия. Кроме того, для чего используются эти ограничения?
Путаница с этими понятиями – общее явление. Я надеюсь, следующие примеры прояснят ситуацию. Компоненты "initially immediate/deferred" в состоянии ограничения указывают, как по умолчанию будет проверяться ограничение:
- первоначально немедленное (initially immediate) – проверка ограничения в конце выполнения оператора;
- первоначально отложенное (initially deferred) – ожидание проверки ограничения до конца выполнения транзакции (или до явного изменения состояния ограничения оператором SET CONSTRAINT … IMMEDIATE).
Рассмотрим следующий пример:
SQL> create table t
2 ( x int constraint
check_x check ( x > 0 )
deferrable
initially immediate,
3 y int constraint
check_y check ( y > 0 )
deferrable
initially deferred
4 )
5 /
Table created.
SQL> insert into t values ( 1,1 );
1 row created.
SQL> commit;
Commit complete.
Итак, когда оба ограничения удовлетворяются, строки вставляются без ошибки. Однако если я попытаюсь вставить строку, которая нарушает ограничение CHECK_X, первоначально немедленное ограничение, оно немедленно проверится, и я обнаружу следующее:
SQL> insert into t values ( -1,1);
insert into t values ( -1,1)
*
ERROR at line 1:
ORA-02290: check constraint (проверочное ограничение)
(OPS$TKYTE.CHECK_X) violated (нарушено)
Ограничение CHECK_X – откладываемое, но первоначально немедленное, поэтому строка сразу же отвергается. Ограничение CHECK_Y, однако, ведет себя по-другому. Оно не только откладываемое, но и первоначально отложенное, а это означает, что ограничение не будет проверяться до выполнения оператора COMMIT или изменения состояния ограничения на немедленное.
SQL> insert into t values ( 1,-1);
1 row created.
Видите, вставка выполнена успешно (во всяком случае, в настоящий момент). Я отложил проверку ограничения до завершения транзакции:
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back (выполнен откат транзакции)
ORA-02290: check constraint (проверочное ограничение)
(OPS$TKYTE.CHECK_Y) violated (нарушено)
В этот момент сервер базы данных выполнил откат моей транзакции, так как произошел сбой оператора COMMIT из-за нарушения ограничения. Итак, эта последовательность операторов демонстрирует различия между первоначально немедленными и первоначально отложенными ограничениями. Компонент "initially" в состоянии ограничения указывает, когда сервер Oracle будет по умолчанию проверять ограничение: либо в конце выполнения оператора (immediate), либо в конце выполнения транзакции (deferred). Мне также нужно выяснить, что откладываемые ограничения позволяют мне делать. Я выполню оператор, делающий все ограничения откладываемыми (все, которые могут быть отложенными). Заметим, вы можете также выполнять этот оператор для отдельных ограничений, если это требуется; вы не должны обязательно откладывать все ограничения, которые могут быть отложенными:
SQL> set constraints all deferred;
Constraint set.
SQL> insert into t values ( -1,1);
1 row created.
Когда первоначально немедленное ограничение переводится в отложенный режим, кажется, что операция вставки выполняется успешно; но посмотрим, что произойдет при попытке фиксации транзакции:
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint
(OPS$TKYTE.CHECK_X) violated
Происходит сбой транзакции и выполняется ее откат, поскольку проверочное ограничение CHECK_X проверяется во время выполнения оператора COMMIT. И наоборот, я могу заставить первоначально отложенные ограничения действовать как "немедленные" ограничения:
SQL> set constraints all immediate;
Constraint set.
SQL> insert into t values ( 1,-1);
insert into t values ( 1,-1)
*
ERROR at line 1:
ORA-02290: check constraint
(OPS$TKYTE.CHECK_Y) violated
Теперь оператор, который работал до момента фиксации транзакции, сбивается сразу же. Я вручную изменил режим проверки ограничений по умолчанию.
Для чего нужны откладываемые ограничения в реальном окружении? Примеров их использования можно привести много. Прежде всего, они предназначены для работы с материализованными представлениями (materialized views), называемыми также моментальными копиями (snapshots). Эти представления могут использовать откладываемые ограничения для обновления представлений. Во время обновления материализованного представления целостность может нарушаться, и ограничения не могут быть проверенными при каждом выполнении операторов. Но к моменту фиксации транзакции целостность будет восстановлена и ограничения будут удовлетворены. Без откладываемых ограничений ограничения в материализованных представлениях не позволят успешно выполнять процесс обновления этих представлений.
Другая популярная причина использования откладываемых ограничений – обеспечение каскадного обновления, когда вы предвидите необходимость обновления первичного ключа в связи "главный-подчиненный". Если вы делаете внешний ключ откладываемым, но первоначально немедленным, вы можете:
- перевести все ограничения в отложенный режим;
- обновить первичный ключ – на данный момент дочерние ограничения целостности проверяться не будут;
- обновить дочерние внешние ключи;
- выполнить оператор COMMIT (он будет выполнен успешно, если все дочерние записи, затронутые обновлением, указывают на существующую родительскую запись).
Без откладываемых ограничений процесс обновления будет чрезмерно трудным. Пример кода, требуемого для выполнения каскадного обновления без этой возможности, см. на сайте asktom.oracle.com/~tkyte/update_cascade/index.html!
Кроме того, откладываемые ограничения вы можете использовать в различных многооператорных транзакциях, в процессе выполнения которых требуется временно нарушать целостность, но в конце транзакций все "устаканивается ".
Фантазии со временем
Как вы вычисляете время, которое выдается в столбце AGE (возраст) на первой странице сайта asktom.oracle.com? Я спрашиваю это, потому что вижу его в разных форматах, таких, как 3 hrs old (3 часа назад), 9 months old (9 месяцев назад); 2.3 years old (2.3 лет назад) и т. д. Я – новичок в среде Oracle и интересуюсь, какого вида вычисления времени вы могли бы использовать.
Я просто использую старый добрый CASE-оператор, введенный в СУБД Oracle8i Release 2 версии 8.1.6 (перевод выдаваемого текста дается в виде комментариев в коде оператора – прим. пер.):
Select
case
when sysdate-timestamp < 1/24
then round(24*60*(sysdate-timestamp))
|| ' minutes old ' -- минут назад
when sysdate-timestamp < 1
then round(24*(sysdate-timestamp))
|| ' hours old ' -- часов назад
when sysdate-timestamp < 14
then trunc(sysdate-timestamp)
|| ' days old ' -- дней назад
when sysdate-timestamp < 60
then trunc((sysdate-timestamp)/7)
|| ' weeks old ' -- недель назад
when sysdate-timestamp < 365
then round(months_between
(sysdate,timestamp))
|| ' months old ' -- месяцев назад
else round(months_between
(sysdate,timestamp)/12,1)
|| ' years old ' -- лет назад
end age, ...
Теперь, если вы попытаетесь использовать CASE-оператор в PL/SQL-коде в СУБД Oracle8i, вы получите сообщение об ошибке, поскольку синтаксический разборщик PL/SQL не распознает CASE-оператор. (Заметим, таких проблем в СУБД Oracle9i нет.) Для того чтобы обойти это ограничение СУБД Oracle8i вы можете:
- скрыть CASE-оператор в представлении и в PL/SQL-коде выполнять запрос к этому представлению;
- вместо CASE-оператора использовать вложенную функцию DECODE.
Я лично предпочитаю использовать представление, а читатель Мартин Бербридж (Martin Burbridge) прислал на веб-сайт asktom.oracle.com следующий пример использования функции DECODE:
decode(sign(sysdate-timestamp-1/24),-1,
round(24*60*(sysdate-timestamp))
|| ' minutes old ',
decode(sign(sysdate-timestamp - 1), -1,
round(24*(sysdate-timestamp))
|| ' hours old ',
decode(sign(sysdate-timestamp-14),-1,
trunc(sysdate-timestamp)
|| ' days old ',
decode(sign(sysdate-timestamp-60),-1,
trunc((sysdate-timestamp)/7)
|| ' weeks old ',
decode(sign(sysdate-timestamp-365),-1,
round(months_between
(sysdate,timestamp))
|| ' months old ',
round(months_between
(sysdate,timestamp)/12,1)
|| ' years old '
))))) age
Она делает в точности то же самое, что и CASE-оператор, но не так наглядно.
Где размещать файлы?
Я рассматриваю варианты проекта приложения, в котором пользователи будут загружать и сохранять документы, доступные также для выгрузки другими пользователями. Документы будут в формате Microsoft Word со средним размером, равным 150K. Планируется первоначальное перемещение от 18,000 до 20,000 документов (с компакт-дисков), а при эксплуатации приложения объем хранилища возрастет приблизительно до 25,000 документов. Для загрузки и выгрузки браузер на клиентской машине будет использовать страницы PL/SQL-картриджа. Первоначально к приложению будут обращаться 400 - 500 человек (почти) одновременно, и в течение двух недель к нему будут обращаться 300 человек в день (с рассеянным доступом). Сами документы должны будут храниться в базе данных как данные типа BLOB.
Как вы думаете, это хороший вариант, принимая во внимание эксплуатацию? Будет ли это "деформировать" системную глобальную область (SGA, system global area)? Учитывая требования приложения и браузер на клиентской машине, есть ли другие варианты, не использующие данные типа BLOB, такие, как файловая система?
Я все храню в базе данных. Точка. Если данные что-нибудь значат для вас, имеют какое-либо значение вообще, то вы в действительности поместите их в базу данных, где они будут профессионально администрироваться, будут создаваться их резервные копии, данные будут восстанавливаемыми и защищенными от несанкционированного доступа. В дополнение к этим явным выгодам, вы также получите возможность индексирования и поиска ваших документов. (Правда, это может быть сделано также и в файловой системе, но тогда не будет никакой целостности между индексом и самим документом.) В базе данных вы получаете возможность преобразования формата документа (например, загрузить DOC-файл и представить его в HTML-формате.) Ваши данные полностью интегрированы, защищены, резервированы и всегда доступны вам.
В корпорации Oracle мы имеем для внутреннего пользования многотерабайтовую базу данных, используемую как единый файловый сервер для всей компании. Все документы компании находятся там – зарезервированные, пригодные для поиска, проиндексированные и полностью доступные – в одном-единственном месте. Управление тысячами тысяч документов было бы невозможным, если бы они находились в обычной файловой системе (даже если бы файловая система смогла разместить их всех).
Что касается проблем с SGA, то все в ваших руках. Данные типа BLOB могут не кешироваться (опция NOCACHE), если вы не хотите иметь их в кеше буферов, так что вам не нужно беспокоиться о "деформации" SGA.
Где размещать курсоры?
Вы можете объяснить мне, где лучшее место для размещения курсоров при программировании на языке PL/SQL? Должны ли мы помещать их в спецификацию пакета или в его тело? Причина, по которой я это спрашиваю, следующая: я работаю с разработчиком, который настаивает на том, чтобы помещать все курсоры в спецификацию пакета. Он говорит мне, что это является правильным способом размещения. Конечно, мы должны поместить курсоры в спецификации пакета, но только если они используются в пакете не один раз. Все курсоры в данном пакете используются только по одному разу, так что я полагаю, что они должны быть помещены в раздел объявления процедуры или функции, из которой они вызываются. Прав ли я? Каковы преимущества и недостатки помещения всех курсоров в спецификацию пакета? Влияет ли размещение курсоров на производительность?
Мое личное предпочтение было, есть и всегда будет заключаться в том, что в большинстве случаев необходимо использовать неявные курсоры, то есть явно не определять курсор вообще! Например, следующим образом:
is
...
begin
....
for x in ( select * from emp )
loop
Этот способ работает исключительно хорошо для результирующих наборов, скажем, от 50 до 100 строк. Я предпочитаю данный способ использованию явных курсоров по следующим причинам:
- он чуть более эффективен с точки зрения потребления ресурсов центрального процессора;
- я могу взглянуть на код и легко увидеть, с какими данными имею дело. Запрос бросается мне в глаза;
- он заставляет меня использовать представления, когда запросы становятся большими. Я по-прежнему вижу, какие данные я запрашиваю из представления, но сложность представления скрыта. Вместо того чтобы скрывать это в курсоре, я скрываю это в представлении.
Иногда вы должны использовать явно определенный курсор, самый общий случай – вам необходимо обрабатывать большие результирующие наборы и для повышения производительности во время фазы FETCH (выборка) требуется использовать режим BULK COLLECT (массовая выборка). Когда я должен использовать явно определенный курсор, я предпочитаю локальный курсор, определяемый непосредственно в процедуре (даже не в теле пакета, а прямо в самой процедуре в теле пакета). Почему?
- По сравнению с глобальным курсором, который определяется в спецификации пакета, он чуть более эффективен с точки зрения потребления ресурсов центрального процессора;
- я по-прежнему могу взглянуть на код и легко увидеть, с какими данными имею дело;
- он заставляет меня использовать представления, поскольку запрос находится в моей процедуре и я не хочу, чтобы он затенял остальную часть кода;
- всегда понятно, кому курсор принадлежит.
Вы видите здесь пример для подражания? Кроме того, курсор с локальной областью действия (в стеке процедур) сам очищается после вызова, и "хлама" типа курсорная_переменная%isopen, "засоряющего" мой код, нет. (Ни в одном моем коде никогда не использовалась такая "особенность", как isopen.) Я не должен беспокоиться, что "ах, вы знаете, процедура P1 использует курсор cursor_x, и я использую курсор cursor_x, а так как это один и тот же курсор, мы можем "затоптать" друг друга". Так что я не имею тех проблем, которые всегда имеются с глобальными переменными. Я рассматриваю курсоры в спецификации пакета или в его теле (не определенным в процедуре, а имеющим глобальную область действия) с тем же недоверием, с которым я рассматриваю глобальные переменные; возможность побочных эффектов от множественных процедур, обращающихся к этим глобальным переменным, слишком высока. Я использую глобальные переменные только тогда, когда никакого другого выбора не существует.
Приведем краткое заключение, в порядке предпочтений:
- Никаких курсоров (select into, for x in ( select..... ).
- Локально объявляемые курсоры, когда по какой-либо причине возникает такая необходимость, например:
a. Массовые связывания, в которых требуется ограничивать количество строк (предложение LIMIT).
b. Тип данных REF CURSOR.
Я рекомендую избегать глобально объявляемых курсоров (в спецификациях пакетов) по следующим причинам:
- вы теряете прекрасные возможности инкапсуляции. Курсоры доступны глобально и уязвимы для всех, кто может иметь доступ к пакету;
- у вас незначительно снижается производительность (я подчеркиваю "незначительно" – это не есть главная причина);
- вы уменьшаете удобочитаемость тела пакета;
- они глобальные, а глобальные объекты, в общем, – плохая практика программирования, которую следует по мере возможности избегать.
Переключение табличных пространств отката (UNDO Tablespaces) в сервере Oracle9i Database
Насколько я понимаю, если я изменяю одно табличное пространство отката на другое, используя оператор ALTER SYSTEM, то сервер Oracle фактически переключится на другое табличное пространство только после того, когда все активные транзакции, которые используют первое пространство, будут зафиксированы или будет выполнен их откат. Правильно ли это? Кроме того, если я хочу узнать, какие транзакции активны в первом табличном пространстве отката, что я должен сделать? Любое представление или запрос, которые вы имеете, будут полезны.
Вы понимаете неправильно. Сервер Oracle начинает использовать другое табличное пространство отката сразу же. В следующем примере это не только демонстрируется, но и дается запрос, который вы хотели получить для определения того, кто использует сегменты отката в табличных пространствах отката.
Я начну выполнять в некотором сеансе транзакцию и не буду фиксировать ее. Я выполню запрос, чтобы узнать, какие сеансы используют сегменты отката в табличных пространствах отката. Затем я выполню оператор ALTER SYSTEM для переключения табличных пространств отката, начну выполнять в моем сеансе другую транзакцию и повторно выполню запрос, чтобы узнать, кто и какое табличное пространство отката использует. В это время я увижу мою старую транзакцию в старом табличном пространстве отката, а также мою новую транзакцию в новом табличном пространстве отката. Прежде всего, я проверю, кто что использует. В этом запросе соединяются представления V$SESSION (для получения информации о сеансах), V$TRANSACTION (для выдачи данных только о сеансах с активными транзакциями) и DBA_ROLLBACK_SEGS (для вывода информации о сегментах отката, такой, как имена и табличные пространства):
select a.username,
b.segment_name,
b.tablespace_name tspace
from v$session a,
dba_rollback_segs b,
v$transaction c
where a.taddr = c.addr
and b.segment_id = c.xidusn
/
USERNAME SEGMENT_NAME TSPACE
-------- ------------ -------
OPS$TKYTE _SYSSMU11$ UNDO
Здесь показано, что активна одна транзакция, использующая табличное пространство отката с именем UNDO. Теперь я переключу табличные пространства отката:
alter system
set undo_tablespace = undo2;
Теперь я начну другую транзакцию в этом сеансе:
update dept set deptno = deptno;
2 rows updated.
select a.username,
b.segment_name,
b.tablespace_name tspace
from v$session a,
dba_rollback_segs b,
v$transaction c
where a.taddr = c.addr
and b.segment_id = c.xidusn
/
USERNAME SEGMENT_NAME TSPACE
-------- ------------ -------
OPS$TKYTE _SYSSMU11$ UNDO
OPS$TKYTE _SYSSMU16$ UNDO2
В этот момент я могу увидеть, что используются оба табличных пространства отката. Я все еще не могу удалить табличное пространство UNDO, поскольку оно имеет активные транзакции, но оно не будет использоваться для каких-либо новых транзакций.
И теперь вы также имеете запрос, который вы хотели.
Случайные числа
Как мне с помощью одного SQL-оператора получить, скажем, шесть уникальных случайных чисел в диапазоне от 1 до 49?
Иногда, думая "процедурно" о работе со множествами, вы можете очень сильно отдалиться от языка SQL. SQL считается непроцедурным языком, но я временами обнаруживаю, что планирование процедурных потребностей помогает мне формулировать запрос.
Для того чтобы решить данную проблему, мне нужно:
- сгенерировать набор чисел в диапазоне от 1 до 49. Это будет набор, из которого я буду извлекать шесть случайных чисел.
- упорядочить эти 49 чисел по случайной схеме. Эта сортировка похожа на присваивание случайного значения каждому из этих 49 чисел с последующим упорядочиванием по этим случайным числам;
- взять из этого результирующего набора 6 первых чисел.
Для генерации набора из 49 чисел мне нужно просто взять любую таблицу, содержащую не менее 49 строк. Я полагаю, что для подобных целей очень подходит таблица ALL_OBJECTS. В ней всегда содержится не менее 1,000 строк, и все во всех системах имеют доступ к ней.
Для начала мне нужен набор из 49 чисел. Такой SQL-запрос очень прост:
select rownum r
From all_objects
where rownum < 50
Он будет генерировать числа 1, 2, 3, ... 49. Затем мне нужно взять этот набор и упорядочить его по случайной схеме. Для этого я буду использовать вложенное представление (inline view). В следующем запросе замените слово ЗАПРОС на приведенный выше запрос:
select r
from ( ЗАПРОС )
order by dbms_random.value
Теперь, если вы в утилите SQL*Plus будете неоднократно выполнять этот запрос с предложением order by dbms_ random.value, вы обнаружите, что вы всегда получаете 49 чисел и при каждом выполнении запроса числа возвращаются в разном порядке.
Теперь все, что мне нужно сделать, – это взять первые 6 чисел. Я буду использовать еще одно вложенное представление, которое ограничит выдачу первыми 6 строками. В полном виде запрос выглядит так:
select r
from
( select r
from
( select rownum r
from all_objects
where rownum < 50 )
order by dbms_random.value )
where rownum <= 6
/
R
-----
8
20
32
12
44
26
6 rows selected.
И если я выполню его снова, я получу шесть других чисел.
Ведущий данной колонки Том Кайт (Tom Kyte, thomas.kyte@oracle.com) работает в Oracle с 1993 года. Кайт – вице-президент группы Oracle Government, Education, and Healthcare, он автор книг "Effective Oracle by Design" (издательство Oracle Press) – "Проектирование эффективных приложений Oracle" и "Expert One on One: Oracle" (издательство Apress) ( Прим. пер. Имеется русский перевод: Oracle для профессионалов. Книга 1. Архитектура и основные особенности. Книга 2. Расширение возможностей и защита. – ДиаСофт, 2003 г.).
|
