Уважаемые подписчики! Этот выпуск посвящен использованию индексов для реализации ограничения первичного ключа в СУБД Oracle. Том Кайт вернулся к этому вопросу последний раз 17 сентября 2002 года. Вашему вниманию предлагается сокращенный перевод соответствующего обсуждения.
Привет, Том!
Мы хотели бы больше узнать о представлении DBA_INDEXES и его внутреннем устройстве.
Проблема в том, что для таблицы создан первичный ключ, соответствующая информация о котором в представлении dba_indexes не отражена.
SQL> SELECT * FROM dba_constraints WHERE table_name = 'STD_SEM_HIST' and constraint_type in ('P','U');
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ -
------------------------------
SEARCH_CONDITION
--------------------------------------------------------------------------------
R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS
DEFERRABLE
------------------------------ ------------------------------ ---------
-------- --------------
DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE
--------- ------------- -------------- --- ---- -----------
IISDBM US_SMH_PK_AYSMMN P STD_SEM_HIST
ENABLED
NOT DEFERRABLE
IMMEDIATE VALIDATED USER NAME 28-JUN-1999
IISDBM UI_SMH_UK_AYSMMNCC U STD_SEM_HIST
ENABLED
NOT DEFERRABLE
IMMEDIATE VALIDATED USER NAME 28-JUN-1999
SQL> select * from dba_indexes
where index_name in (SELECT constraint_name FROM dba_constraints WHERE
table_name = 'STD_SEM_HIST' and constraint_type in ('P','U'));
no rows selected
Мы попытались вставить в таблицу дублирующуюся запись, и получили следующее сообщение об ошибке (свидетельствующее, что огрнаничение первичного ключа срабатывает):
SQL> insert into std_sem_hist
2 (CRSE_C,SEM_ACAD_Y,SEM_C,SEM_DEG_C,SEM_FAC_C,SEM_SEX_C,SEM_STS_C,STD_MATRIC_N)
3 values
4 ('BDG4', '1998/1999', '1', '101', '37', 'F', 'L', '954460M10')
5 ;
insert into std_sem_hist
*
ERROR at line 1:
ORA-00001: unique constraint (IISDBM.US_SMH_PK_AYSMMN) violated
Можете ли вы объяснить, почему так происходит?
PS: мы обратились в службу технической поддержки Oracle, но, к сожалению, они не смогли дать нам исчерпывающего объяснения. Они рассказали нам то, что и так было известно...
Вы исходите из ошибочного предположения, что при добавлении ограничения первичного ключа ВСЕГДА создается индекс, - это неверно.
Для обеспечения выполнения ограничения первичного ключа будет использоваться существующий индекс (уникальный или не уникальный).
Рассмотрим следующий пример:
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t; Table dropped. ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t (a int, b int, c int); Table created. ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(a,b); Index created. ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(a); Table altered. ops$tkyte@ORA817DEV.US.ORACLE.COM> select constraint_name, constraint_type from user_constraints; CONSTRAINT_NAME C ------------------------------ - T_PK P ops$tkyte@ORA817DEV.US.ORACLE.COM> select index_name, uniqueness from user_indexes; INDEX_NAME UNIQUENES ------------------------------ --------- T_IDX NONUNIQUE
Имеется ограничение первичного ключа и единственный неуникальный индекс (причем составной, не только по столбцу первичного ключа!!). В данном случае, первичный ключ "похитил" существующий индекс и использовал его для обеспечения уникальности.
Рассмотрим теперь другой пример:
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t; Table dropped. ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t (a int, b int, c int); Table created. ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(a); Table altered. ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(a,b); Index created. ops$tkyte@ORA817DEV.US.ORACLE.COM> select constraint_name, constraint_type from user_constraints; CONSTRAINT_NAME C ------------------------------ - T_PK P ops$tkyte@ORA817DEV.US.ORACLE.COM> select index_name, uniqueness from user_indexes; INDEX_NAME UNIQUENES ------------------------------ --------- T_IDX NONUNIQUE T_PK UNIQUE ops$tkyte@ORA817DEV.US.ORACLE.COM
В данном случае, действительно создается уникальный индекс. Действие add constriant было выполнено до создания индекса, поэтому подходящего индекса не было, и при добавлении первичного ключа он был создан автоматически. Учтите, что при этом был создан уникальный индекс, но так бывает не всегда, что демонстрирует следующий пример:
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t; Table dropped. ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( a int, b int, c int ); Table created. ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(a) deferrable; Table altered. ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(a,b); Index created. ops$tkyte@ORA817DEV.US.ORACLE.COM> select constraint_name, constraint_type from user_constraints; CONSTRAINT_NAME C ------------------------------ - T_PK P ops$tkyte@ORA817DEV.US.ORACLE.COM> select index_name, uniqueness from user_indexes; INDEX_NAME UNIQUENES ------------------------------ --------- T_IDX NONUNIQUE T_PK NONUNIQUE
Здесь, из-за того, что ограничение допускает отложенную проверку, оказывается, что сервер создал неуникальный индекс.
Нет, лучше не было бы.
Во-первых, индекс не обязательно должен быть уникальным, - сервер ведь поддерживает отложенную проверку ограничений.
Во-вторых, есть случаи, когда неуникальность индекса дает существенные преимущества (например, в хранилище данных, где иногда приходится отключать и включать проверку ограничений. При использовании неуникального индекса можно отключить ограничение, не удаляя индекс, и включить его снова, не пересоздавая индекс заново).
Ничего не обязывает создавать уникальные индексы при добавлении ограничения первичного ключа. Если существует индекс, в начальную часть которого в любом порядке входят столбцы первичного ключа, этот индекс вполне подходит.
Я выбросил часть обсуждения, посвященную загрузке данных в хранилища, как не имеющую прямого отношения к обсуждаемой теме... В качестве компенсации, вот вам еще цитата. На меня она произвела впечатление. В.К.
Ограничение всегда проверяется сервером процедурно - индекс используется для ускорения доступа к проверяемым данным. Индекс обеспечивает производительность при проверке ограничений, не более того. Уникальный он или нет, - это лишь вопрос производительности.
Есть ли в версии 8.06 представление или метод, чтобы понять, какой индекс используется для обеспечения уникальности первичного ключа?
Я часто использую создание неуникальных индексов для поддержки ограничений. Это действительно удобно, если доступ к данным выполняется по первичному ключу, но необходимы и другие индексы (в частности, при использовании таблиц, организованных по индексу). Было бы удобно быстро разобраться, использован ли этот метод в тех случаях, когда необходимо изменить соответствующий индекс.
В версии 9i, но не раньше, есть соответствующий столбец в представлении user_constraints.
О том, как это можно определить в прежних версиях, можно прочитать здесь.
Для тех, кому лень читать обсуждение по ссылке на английском, оно представлено ниже
Оригинал обсуждения этого вопроса можно найти здесь.
В версиях до 9i индекс, используемый для поддержки ограничения, непосредственно нигде не указан. Но, почти всегда, его можно определить.
Если имя индекса, поддерживающего ограничение первичного/уникального ключа, сгенерировано системой - можно определить с полной уверенностью.
Если имя индекса, поддерживающего ограничение, совпадает с именем ограничения, - почти наверняка индекс был сгенерирован при добавлении ограничения. Почти наверняка (можно создать индекс I1 по столбцам первичного ключа, а затем создать первичный ключ I1 - индекс в этом случае не создается автоматически).
Если имя индекса не совпадает с именем ограничения, то индекс навреняка не создан автоматически.
Вот представление, которое можно использовать для решения этой проблемы (его надо создавать от имени пользователя SYS):
sys@TKYTE816> create or replace view pk_indexes 2 as 3 select t.name table_name 4 , u.name owner 5 , c.name constraint_name 6 , i.name index_name 7 , decode(bitand(i.flags, 4), 4, 'Yes', decode(i.name, c.name, 'Probably', 'No')) 8 generated 9 from sys.cdef$ cd 10 , sys.con$ c 11 , sys.obj$ t 12 , sys.obj$ i 13 , sys.user$ u 14 where cd.type# between 2 and 3 15 and cd.con# = c.con# 16 and cd.obj# = t.obj# 17 and cd.enabled = i.obj# 18 and c.owner# = u.user# 19 and c.owner# = uid 20 / View created. sys@TKYTE816> create public synonym pk_indexes for pk_indexes; sys@TKYTE816> grant select on pk_indexes to public;
Протестируем его:
tkyte@TKYTE816> create table t (x int primary key, 2 y int, 3 z int, 4 constraint y_unique unique(y)); Table created. tkyte@TKYTE816> create index z_idx on t(z); Index created. tkyte@TKYTE816> alter table t add constraint z_unique unique(z); Table altered. tkyte@TKYTE816> select * from pk_indexes 2 / TABLE_NAME OWNER CONSTRAINT_ INDEX_NAME GENERATE ---------- ----- ----------- ----------- -------- T TKYTE SYS_C001260 SYS_C001260 Yes T TKYTE Y_UNIQUE Y_UNIQUE Probably T TKYTE Z_UNIQUE Z_IDX No
Сменим заявленную тему. В следующем выпуске, скорее всего, - перевод замечательной статьи Джонатана Льюиса, посвященной битовым индексам. Выпуск выйдет в конце следующей недели. Следите за новостями на сайте проекта Open Oracle.
С наилучшими пожеланиями,
В.К.
|
|