Источник: журнал Oracle Magazine, March-April 2006
(http://www.oracle.com/technology/oramag/oracle/06-mar/o26asktom.html).
Том Кайт удаляет дубликаты, задает порядок срабатывания триггеров и объясняет, почему объектная привилегия SELECT разрешает блокирование.
Вопрос. Пожалуйста, помогите мне написать SQL-запрос для избавления от дубликатов, которые ниже помечены как XXX. Дублирующие записи для меня – записи, которые имеют одинаковые значения в столбцах SA и SB, независимо от их позиции. (Первичный ключ – объединенные столбцы SA и SB).
SA SB
--- ---
A B
A C
A D
B A XXX (DUPLICATE )
B C
C A XXX (DUPLICATE)
C B XXX (DUPLICATE)
C D
Ответ. Есть несколько способов сделать это, я продемонстрирую свой подход, но на будущее я предлагаю вам добавить ограничение целостности, которое в дальнейшем будет запрещать ввод дубликатов.
А пока для удаления из таблицы дубликатов я могу использовать некоторые встроенные функции и аналитику.
Используя аналитику, я буду разделять, разбивать, данные с помощью функций LEAST(SA,SB) и GREATEST(SA,SB). То есть, сначала я найду меньшее значение столбцов SA и SB, а затем большее значение, чтобы строки со значениями A, B находились в одном и том же разделе, что и строки со значениями B, A. После разбиения данных с помощью этих функций я сортирую строки по значениям столбцов SA и SB и применяю к каждой строке функцию row_number (). Все, что я должен сделать затем, – найти любые строки, у которых номер строки row_number () <> 1 (не первая строка в разделе – в каждом разделе мне нужна только одна строка), и удалить их.
Этот SQL-код похож на показанный в листинге 1.
SQL> delete from t
2 where rowid in
3 ( select rid
4 from (
5 select rowid rid, sa, sb,
6 row_number() over
7 (partition by least(sa,sb),
8 greatest(sa,sb) order by sa, sb) rn
9 from t
10 )
11 where rn <> 1
12 )
13 /
3 rows deleted.
Листинг 1. Удаление дубликатов из таблицы t.
На будущее я рекомендую вам изначально предотвращать в системе ввод дубликатов. Один из способов добиться этого – определить на уровне таблицы проверочное ограничение целостности:
SQL> alter table t
2 add constraint sa_less_than_equal_sb
3 check (sa <= sb);
Table altered.
Затем создайте составной первичный ключ по столбцам SA и SB. Таким образом вы никогда не будете иметь A, B и B, A; поскольку B, A изначально не будет вставлено. Если вы все же хотите иметь в первичном ключе значение B, A; вы можете использовать индекс по ключу-функции:
SQL> create unique index t_idx
2 on t(least(sa,sb),
3 greatest(sa,sb));
Index created.
Это – уникальный индекс с отсортированными значениями столбцов таблицы – в первом столбце индекса сначала помещается наименьшее значение столбцов SA и SB, а во втором столбце индекса – наибольшее значение столбцов SA и SB.
Эта тема вылилась в довольно интересное интерактивное обсуждение, когда кто-то спросил: "А что насчет трех столбцов или N столбцов"? Вы можете прочитать исходное обсуждение на сайте asktom.oracle.com, там же рассматривались альтернативные операторы DELETE, которые также позволяют удалять дубликаты.
Порядок срабатывания триггеров
Вопрос. У меня есть таблица и два или больше триггеров AFTER INSERT/AFTER DELETE. У меня есть два-три триггера AFTER INSERT для одной и той же таблицы. Можете ли вы рассказать о порядке срабатывания триггеров AFTER INSERT? (Я пытался определить это, используя отметки времени (time stamps), но все они указывают одинаковое время).
Ответ. О порядке срабатывания триггеров я могу сказать только следующее:
- будут срабатывать все триггеры BEFORE в каком-то порядке;
- будут срабатывать все триггеры BEFORE FOR EACH ROW в каком-то порядке;
- будут срабатывать все триггеры AFTER FOR EACH ROW в каком-то порядке;
- а затем будут срабатывать все триггеры AFTER в каком-то порядке.
В пределах одного типа триггеров вы не должны рассчитывать на порядок срабатывания этих триггеров. Даже если вы обнаружили, что три триггера BEFORE запускаются в таком порядке: TRIGGER_A, TRIGGER_B, TRIGGER_C; вы не можете полагаться на это. В документации Oracle (Сервер Oracle Database. Руководство разработчика приложений. Основы) ясно написано: "Если для таблицы существует несколько триггеров одного типа, СУБД Oracle выполняет их в произвольном порядке".
Если ваши триггеры зависят от порядка срабатывания, вы должны объединить их в один триггер. На самом деле я рекомендую, чтобы во всех триггерах вызывались хранимые процедуры, так что большинство ваших триггеров будут состоять из одной строки – вызова процедуры. Таким образом вы сможете взять два-три триггера AFTER INSERT, разместить их код в хороших модульных процедурах пакета, а затем вызывать их в правильном порядке в одном триггере.
Побочное примечание: во время интерактивного обсуждения возник вопрос: "Почему в СУБД Oracle вообще разрешается использование множественных триггеров одного и того же типа"? Причина восходит к первоначальной реализации в сервере Oracle Database механизма тиражирования. В журналах материализованных представлений (materialized view logs) – раньше в версии 7.0 они назывались просто журналами моментальных копий (snapshot logs) – для регистрации операций языка манипулирования данными (DML), которые выполнялись на данной таблице, использовались триггеры базы данных. До поддержки в сервере базы данных множественных триггеров одного и того же типа это накладывало на разработчиков ограничение: либо не использовать сам этот тип триггеров (который уже использовался сервером Oracle), либо не использовать журналы материализованных представлений. Ни один из тех вариантов не был привлекательным, поэтому в сервер базы данных была добавлена возможность использования множественных триггеров одного и того же типа.
Важно, чтобы при использовании множественных триггеров одного и того же типа вы гарантировали, что между ним нет никакой зависимости – особенно относительно порядка их срабатывания, поскольку вы никогда не сможете им управлять.
Объектная привилегия SELECT разрешает блокирование
Вопрос. Нашим разработчикам (по крайней мере, нашим разработчикам группы технической поддержки) необходим доступ только для чтения (READ ONLY) к нашим промышленным базам данных, поэтому в каждой промышленной базе данных у нас есть роль с объектными привилегиями SELECT на каждую таблицу приложения и эта роль предоставляется разработчикам.
Несколько недель назад один из наших новых разработчиков заблокировал главную таблицу промышленной базы данных и приостановил всю пакетную обработку. Все администраторы базы данных клялись, что нельзя установить блокировку, имея только объектную привилегию SELECT, и мы потеряли много времени выясняя, как разработчик заблокировал таблицу. Мы открыли в Oracle запрос на техническую поддержку (TAR) и получили ответ: "Да, вы можете заблокировать строки, имея только объектную привилегию SELECT. Фактически, вы можете заблокировать всю таблицу оператором LOCK TABLE". Я хочу знать, сколько администраторов баз данных не знают об этом, и понять причину разрешения блокирования, когда нельзя модифицировать данные.
Ответ. Такое блокирование "известно" и допускается, я согласен, что немногие знают об этой особенности (в этом заключается основная причина моего решения о публикации этого вопроса – повысить уровень знаний). В описании оператора LOCK TABLE (Сервер Oracle Database. Справочное руководство по языку SQL), например, сказано: "Необходимые условия: таблица или представление должны принадлежать вашей собственной схеме либо вы должны иметь системную привилегию LOCK ANY TABLE или любую объектную привилегию на таблицу или представление. (выделено мною)
Заметим, эти необходимые условия не обязательно означают возможность модификации данных, но и возможность чтения данных указывает на возможность блокирования этих данных (обеспечивать их устойчивое состояние, "замораживать", запрещать модификацию). Так работает оператор SELECT ... FOR UPDATE. Итак, почему это правильно?
Следующий сценарий показывает, почему это правильно. Предположим, я имею доступ по чтению к таблице конференц-залов и доступ по чтению-записи к таблице расписания заседаний. Мне нужно зарезервировать конференц-зал X. Для того чтобы сделать это надежно (чтобы избежать наложений) и никто другой не мог зарезервировать конференц-зал X, я должен заблокировать этот конференц-зал (в таблице конференц-залов). Транзакция, в которой резервируются залы, – хранимая процедура, как это должно и быть – все попытки модификации таблицы расписания заседаний выполняются с помощью этой процедуры. Хранимая процедура блокирует этот конференц-зал (в таблице конференц-залов) оператором SELECT ... FOR UPDATE, чтобы никто другой не мог зарезервировать его.
Другой сценарий, который показывает, почему чтение может заблокировать таблицу: я хочу удалить строку из таблицы P, которая является родительской таблицей таблицы C. У меня есть доступ по чтению-записи к таблице P и доступ только по чтению к таблице С. Я пытаюсь удалить строку из таблицы P, но перед этим я должен заблокировать часть таблицы C (если существуют индексированные внешние ключи) или всю эту таблицу (если нет индекса по внешнему ключу).
Сложные ограничения целостности
Вопрос. Я написал на языке PL/SQL хранимую процедуру, но думаю, она не будет работать правильно с одновременным доступом к ней нескольких пользователей. Эта процедура проверяет, что строка с определенными значениями не существует и инициирует ошибку, если она существует. Если строка не существует, процедура выполняет некоторые вычисления, а затем вставляет строку. Я думаю, если другой пользователь вставил строку в эту же таблицу и зафиксировал эту вставку после того, как моя хранимая процедура выполнила проверку, но до того, как она зафиксировала вставку, то в таблицу могут быть вставлены неверные данные. Эта проблема не может быть устранена при помощи уникального ключа, поскольку поля не всегда должны быть различными – только иногда. Единственное решение, которое я смог придумать, состоит в том, чтобы проверять условие в начале хранимой процедуры, а затем снова проверять в конце, и в случае любой неудачной проверки выполнять откат. Есть ли лучший способ сделать это?
Таблица имеет три столбца: A, B и С; комбинация значений столбцов A и B всегда должна быть уникальной, если только значение столбца C не равно 'W'. Если в столбце C содержится значение 'W', то значения столбцов A и B могут совпадать со значениями в других строках. Кроме того, столбец B может иметь неопределенное значение (null). Можно ли создать ограничение для этого?
Ответ. Вы правы, при работе в многопользовательской среде могут быть проблемы. Фактически невозможно определять кросс-стороковые (это ваш случай) или кросс-табличные (типа внешних ключей) ограничения целостности. Почти всегда я вижу попытки реализовать это в кодах приложений или с помощью триггеров, что неправильно для многопользовательской среды. Как вы заметили, если два пользователя одновременно делают одно и тоже, они не видят работу друг друга. Чтобы достичь цели, нужно фактически сериализовать модификации этой таблицы, используя оператор LOCK TABLE.
К счастью, есть действительно хороший способ сделать это, используя индексы по ключу-функции. Здесь вы можете использовать два факта:
- ключи, которые содержат только значения NULL, не включаются в индексы типа "B-дерево". Если вы создаете индекс на T (C1, C2) и существует строка, в которой оба столбца C1 и C2 имеют значения NULL, то такая строка не помещается в индекс. Итак, вы можете в сервере Oracle селективно индексировать строки, если вы будете возвращать NULL-ключ (полностью NULL-ключ);
- вы может индексировать сложные функции, включая пользовательские и встроенные.
Итак, я создаю вашу таблицу:
SQL> create table t
2 ( a int,
3 b int,
4 c varchar2(1) not null
5 );
Table created.
И уникальный индекс по столбцам A и B, когда столбец C <> 'W':
SQL> create unique index t_idx
2 on t
3 ( case when c <> 'W'
4 then a
5 end,
6 case when c <> 'W'
7 then b
8 end
9 );
Index created.
Я получил именно то, что вам нужно:
SQL> set feedback off
SQL> insert into t values (1,1,'W');
SQL> insert into t values (1,1,'W');
SQL> insert into t values (1,1,'x');
SQL> insert into t values (1,1,'y');
insert into t values (1,1,'y')
*
ERROR at line 1:
ORA-00001: unique constraint
(T_IDX) violated
Значения NULL, NULL получаются тогда, когда столбец C = 'W', а значения столбцов A, B, когда столбец C <> 'W', то есть я уникально индексирую столбцы A,B только тогда, когда столбец C <> 'W' – в точности то, что вам нужно. Это как бы похоже на индексирование предложения WHERE.
С чего начинать
Вопрос. В течение прошедших четырех лет я был администратором базы данных SQL Server 2000, теперь я очень хочу изучить СУБД Oracle. С чего мне начинать?
Ответ. Я часто получаю этот вопрос, поэтому я придумал план начального изучения документации Oracle.
Я рекомендую всем начинать с Основных концепций. Затем я ввожу две ветви: для разработчиков и администраторов баз данных, как это показано на рис. 1.
Рис. 1. План изучения документации сервера Oracle Database 10g
Я также предлагаю загрузить и инсталлировать бесплатную СУБД Oracle Database 10g Express Edition. Объем загружаемой информации относительно небольшой (приблизительно 150 МБ), а для последующей инсталляции и запуска в ОС Windows или Linux нужно около пяти минут – великолепный способ начать изучать СУБД Oracle.
И наконец, участвуйте в форумах, начинайте задавать вопросы. Некоторые предлагаемые форумы:
Usenet newsgroups (система телеконференций Интернета). С этого я начинал в октябре 1994 г., когда отправил свое первое сообщение. Что удивительно, масса людей, которые участвовали в этих телеконференциях тогда, все еще там и сегодня. Сейчас я не отправляю столько же сообщений, сколько раньше, но время от времени по-прежнему просматриваю телеконференции, в которых активно участвует много людей.
Oracle-l. (Модератор – Стив Адамс. – Прим. пер.) Здесь участвуют такие известные специалисты, как Волфганг Брайтлинг (Wolfgang Breitling), Лекс де Хаан (Lex de Haan), Пол Дрэйк (Paul Drake), Младен Гогала (Mladen Gogala), Тим Горман (Tim Gorman), Джонатан Льюис (Jonathan Lewis,), Найалл Личфилд (Niall Litchfield), Коннор Макдоналд (Connor McDonald), Кэри Миллсап (Cary Millsap), Танэль Подер (Tanel Poder), Марк Пауэлл (Mark Powell), Пит Шарман (Pete Sharman) и Джаред Стилл (Jared Still). (И этот список далеко не исчерпывающий.) Кроме того, здесь очень высокий дух содружества. В каждой конференции или событии любого масштаба обычно участвуют практически все. У многих участников сложились хорошие дружеские отношения.
The Oracle Users' Co-Operative FAQ (совместные "ЧАВО" пользователей продуктов Oracle). Спонсируется и поддерживается Джонатаном Льюисом. Не забывайте, здесь надо делиться. Если вы обнаружили часто задаваемый вопрос там, где вы работаете, то, вероятно, он будет таким же во всем FAQ-мире, так что вы тоже можете внести и свой вклад.
OTN discussion forums (дискуссионные форумы на сайте OTN). Здесь вы можете даже получить ответ от человека, который написал программное обеспечение, о котором вы спрашиваете.
OracleMetaLink. Я должен упомянуть этот сайт. На нем также проводятся интерактивные дискуссионные форумы.
(Из русскоязычных дискуссионных форумов стоит отметить форум на сайте sql.ru, как имеющий самый большой трафик. Однако культура общения многих его участников заметно уступает зарубежной. Вместе с тем, участвует также и много высококлассных специалистов. – Прим. пер.)
Independent Oracle Users Group. (IOUG, независимая группа пользователей Oracle) Я рекомендую этот сайт не столько из-за дискуссионных форумов (у них небольшой трафик), а как организацию в целом. Я – большой сторонник IOUG и пытаюсь сделать все, что могу, для ее поддержки. Я настоятельно призываю всех вас, кто связал свою карьеру с Oracle, вступить в нее. Само по себе сетевое общение - это одно. Встречайтесь с людьми, которые работают в разных компаниях в вашей предметной области, отдельно от специалистов в других областях. Послушайте об их опыте работы; поделитесь своим. Проводятся большие конференции – я участвовал в американской IOUG-A, британской UKOUG (и планирую в этом году быть там снова), ирландской Irish OUG, не считая других событий OUG, которых слишком много, чтобы даже начать их перечислять. Это – большая организация с большим техническим содержанием и сильным лидерством.
Некоторые "сладости" в сервере Oracle Database 10g Release 2
К настоящему времени очень много написано о новых функциональных возможностях сервера Oracle Database 10g Release 2 (например см. oracle.com/ru/oramag/feb2006/dev_asktom55.html), таких, как прозрачное шифрование данных, новое DML-предложение LOG ERRORS, условная компиляция в языке PL/SQL и так далее. Теперь я хотел бы рассмотреть некоторые другие новые функциональные возможности, обсуждения которых прошли на сайте asktom.oracle.com.
Начнем с улучшенной поддержки больших объектов (LOB) в распределенных системах. В среде PL/SQL максимальный размер больших объектов, к которым возможен доступ через связи баз данных, вырос до 32 КБ (максимальный размер строки в языке PL/SQL) – в интерфейсе уровня вызовов СУБД Oracle (OCI) и среде Java/JDBC отсутствуют такие ограничения, поэтому в них можно иметь доступ через связи баз данных к большим объектам любого размера.
В диспетчере восстановления Recovery Manager (RMAN) появилась новая функциональная возможность – шифрование резервных копий. Оно может выполняться прозрачно с использованием электронных бумажников (Oracle Wallet), что очень похоже на прозрачное шифрование данных, или же во время резервирования вы можете указать пароль, который вам хочется. Вы также можете использовать комбинацию обоих этих способов, например, это будет полезно, когда электронный бумажник не доступен во время восстановления. Конечно, вы должны заботиться о гарантиях доступности бумажника или пароля, иначе зашифрованные данные нельзя будет восстановить.
Другая новая функциональная возможность утилиты RMAN – возможность пропускать во время резервирования базы данных любые неиспользованные блоки. В прошлом утилита RMAN "сжимала" наборы резервных копий, пропуская (не резервируя) любые блоки базы данных, которые никогда не использовалась, но после того, как в блок хотя бы один раз вставлялись данные, он всегда будет резервироваться. То есть, если бы вы создали пустое табличное пространство размером 100 ГБ и не помещали в него ничего, то во время резервирования утилита RMAN пропустила бы в этом табличном пространстве все блоки (поскольку они никогда не содержали никаких данных). Если бы вы впоследствии заполнили все это табличное пространство данными (использовали бы все блоки), а затем удалили бы таблицу с этими данными, то в некоторой момент времени утилита RMAN создала бы резервную копию всех этих блоков, поскольку они содержали данные (были сформатированы), даже при том, что они в настоящее время пусты. Начиная с сервера Oracle Database 10g Release 2, утилита RMAN имеет возможность не резервировать такие блоки, не содержащие никакой информации, которую действительно нужно резервировать.
По соображениям безопасности, серьезно уменьшен набор привилегий роли CONNECT. В прошлом роль CONNECT позволяла не только создавать сеансы, но также и кластерные сегменты базы данных, связи базы данных, последовательности, синонимы, таблицы, представления и изменять параметры сеансов. (Она была больше похожа на роль разработчика, а не на роль для простого подключения к базе данных.) Ей были предоставлены очень серьезные права. Начиная с сервера Oracle Database 10g Release 2, роль CONNECT имеет только привилегию создания сеансов CREATE SESSION.
И наконец, долгожданная функциональная возможность сервера Oracle Database 10g Release 2 – возможность удаления из табличного пространства файла данных, который не содержит никаких данных (в нем не выделено никаких экстентов). Это позволяет администратору базы данных удалить файл, который был случайно добавлен в неправильные место или табличное пространство. В прошлом такой файл навсегда закреплялся за табличным пространством.
Это – только короткий список из многих новых функциональных возможностей сервера Oracle Database 10g Release 2. Если вам интересно узнать больше об этом новом выпуске, на сайте oracle.com/pls/db102/portal.portal_db?selected=1 ознакомьтесь с руководством New Features Guide (новые возможности). Кроме того, не забывайте просматривать начальные разделы почти каждой книги комплекта документации Oracle, посвященные описанию новых возможностей рассмотренных в конкретном документе.
Ведущий данной колонки Том Кайт (Tom Kyte) работает в корпорации Oracle с 1993 года. Кайт – вице-президент Oracle, возглавляющий группу Oracle Public Sector, он автор книг "Expert Oracle: 9i and 10g Programming Techniques and Solutions" (Apress, 2005), "Effective Oracle by Design" (Oracle Press, 2003), а также ряда других.
🔥 VPS до 5.7 ГГц под любые задачи с AntiDDoS в 7 локациях
Виртуальные VPS серверы в РФ и ЕС
