Глава 7

ops$tkyte@ORA8I.WORLD> insert into t values (NULL, 1) ; 1 row created.

ops$tkyte@ORA8I.WORLD> insert into t values (NULL, NULL); 1 row created.

ops$tkyte@ORA8I.WORLD> analyze index t idx validate structure; Index analyzed.

ops$tkyte@ORA8I.WORLD> select name, lf rows from index stats; NAME LF ROWS

T IDX 3

В таблице - четыре строки, а в индексе - только три. Первые три строки, в кото-р1х хотя бы один из ключей индекса - не Null, входят в индекс. Последняя строка со значениями (NULL, NULL) в индекс не входит. Один из случаев, вызывающих непонимание, когда индекс - уникальный, как в примере выше. Рассмотрим результаты выполнения трех следующих операторов INSERT:

ops$tkyte@ORA8I.WORLD> insert into t values (NULL, NULL);

1 row created.

ops$tkyte@ORA8I.WORLD> insert into t values (NOLL, 1) ; insert into t values ( NULL, 1 )

ERROR at line 1:

ORA-00001: unique constraint (OPS$TKYTE.T IDX) violated

ops$tkyte@ORA8I.WORLD> insert into t values (1, NULL); insert into t values ( 1, NULL )

ERROR at line 1:

ORA-00001: unique constraint (OPS$TKYTE.T IDX) violated

Новая строка (NULL, NULL) не считается совпадающей со старой строкой со значениями (NULL, NULL):

ops$tkyte@ORA8I.WORLD> select x, у, count(*)

2 from t

3 group by x,y

4 having count(*) > 1;

X Y COUNT (*)

Это кажется невероятным: уникальный ключ оказывается не уникальным, если все столбцы имеют пустые значения. Факт в том, что в СУБД Oracle (NULL, NULL) <> (NULL, NULL). Эти два ключа не совпадают при сравнении, но совпадают при группировке (при использовании конструкции GROUP BY). Учтите следующее: каждое требование уникальности должно включать хотя бы один непустой столбец, чтобы обеспечивать действительную уникальность.

Индексы

Еще один часто задаваемый вопрос: Почему запрос не использует индекс? связан с индексами и пустыми значениями. При этом речь идет о запросе вида:

select * from T where x is null;

Этот запрос не может использовать созданный ранее индекс - строка (NULL, NULL) просто не входит в индекс, поэтому при использовании индекса был бы получен непра-вильн1й ответ. Запрос сможет использовать индекс, только если хотя бы для одного из столбцов задано требование NOT NULL. Например, можно показать, что сервер Oracle будет использовать индекс по столбцу при поиске по условию X IS NULL, если X - начальный столбец индекса, и хотя бы один из остальных столбцов, входящих в индекс, имеет требование NOT NULL:

ops$tkyte@ORA8I.WORLD> create table t (x int, у int NOT NULL); Table created.

ops$tkyte@ORA8I.WORLD> create unique index t idx on t(x,y); Index created.

ops$tkyte@ORA8I.WORLD> insert into t values (1, 1) ; 1 row created.

ops$tkyte@ORA8I.WORLD> insert into t values (NULL, 1) ; 1 row created.

ops$tkyte@ORA8I.WORLD> analyze table t compute statistics; Table analyzed.

ops$tkyte@ORA8I.WORLD> set autotrace on

ops$tkyte@ORA8I.WORLD> select * from t where x is null; X Y

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=l Card=l Bytes=8)

1 0 INDEX (RANGE SCAN) OF T IDX (UNIQUE) (Cost=l Card=l Bytes = 8)

Я уже говорил, что можно использовать тот факт, что полностью пустые записи в индексе на основе В*-дерева не хранятся. Вот как это делать. Предположим у нас есть таблица со столбцом, имеющим всего два значения. Значения используются крайне неравномерно: допустим, 90 процентов строк содержат одно значение, а 10 процентов - другое. Можно эффективно проиндексировать этот столбец, чтобы получить доступ к строкам, составляющим меньшинство. Это пригодится, когда необходимо проиндексировать меньшую часть строк, но оставить возможность полного просмотра большинства строк, сэкономив при этом место. Решение состоит в том, чтобы использовать значение Null в большинстве строк и любое другое значение - в остальных строках.

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

378 Глава 7

ющее большинство строк в этой таблице находится в состоянии обработано , и лишь немногие еще не обработаны. Можно создать таблицу следующего вида:

create table t ( другие столбцы .. ., timestamp DATE default SYSDATE);

create index t idx on t(timestamp);

Теперь, при вставке новой строки, она будет помечена текущим временем. Процесс будет запрашивать данные с помощью такого запроса, использующего очень давнюю дату для получения всех текущих записей:

select * from T where timestamp > to date(01010001,ddmmyyyyy) order by timestamp;

И по мере обработки этих записей он будет изменять столбец timestamp, устанавливая в нем значение NULL, удаляя ее тем самым из индекса. Поэтому индекс по этой таблице остается небольшим по размеру, независимо от количества строк в таблице. Если есть вероятность, что некоторые записи долго не будут обработаны, т.е. в индексе мог быть долго живущие записи, можно потребовать физически освободить пространство и сжать индекс. Это можно сделать с помощью оператора ALTER INDEX ... COALESCE. В противном случае, индекс со временем будет излишне разрастаться (становиться менее плотным). Если строки всегда обрабатываются и удаляются из индекса, этот шаг не нужен.

Теперь, когда известно, как обрабатываются пустые значения в индексе на основе В*-дерева, можно использовать это с выгодой для приложения и учесть особенности ограничения уникальности по нескольким столбцам, все из которых могут быть пустыми (будьте готовы к тому, что в этом случае может быть несколько строк с пустыми значениями).

Индексы по внешним ключам

Часто задают вопрос, надо ли индексировать внешние ключи. Мы уже касались этой темы в главе 3, при обсуждении взаимных блокировок. Там я подчеркивал, что не проиндексированные внешние ключи являются, как правило, наиболее частой причиной возникновения взаимных блокировок, поскольку изменение в главной таблице или удаление записи из главной таблицы приводит в этом случае к блокированию всей подчиненной таблицы (никакие изменения в таблице внешнего ключа будут невозможны, пока транзакция не завершится). При этом блокируется намного больше строк, чем нужно, и снижается параллелизм. Я часто видел, как это происходит, когда используются средства, автоматически генерирующие SQL-операторы для изменения таблицы. Генерируется оператор UPDATE, изменяющий все столбцы таблицы, независимо от того, изменено значение в столбце или нет. При этом изменяется первичный ключ (хотя на самом деле его значение не изменяется никогда). Например, Oracle Forms будет делать это по умолчанию, если не потребовать явно передавать в базу данных только измененные столбцы. Помимо блокирования таблицы, не проиндексированный внешний ключ плох еще и в следующих случаях: