Отправляет email-рассылки с помощью сервиса Sendsay

Открыто о СУБД Oracle на русском

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "СУБД Oracle "с нуля"" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

←   Сентябрь 2002   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
12.09.2002
14:53:12
15:23:14
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Автор
Сергей

Статистика

10.773 подписчиков
-8 за неделю
←   Все выпуски   →

Открыто о СУБД Oracle на русском : Первичный ключ и индексы


Информационный Канал Subscribe.Ru

Выпуск 14

Первичный ключ и индексы

Уважаемые подписчики! Этот выпуск посвящен использованию индексов для реализации ограничения первичного ключа в СУБД Oracle. Том Кайт вернулся к этому вопросу последний раз 17 сентября 2002 года. Вашему вниманию предлагается сокращенный перевод соответствующего обсуждения.

Вопрос: почему создание первичного ключа не отражается в представлении DBA_INDEXES

Привет, Том!

Мы хотели бы больше узнать о представлении 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

Здесь, из-за того, что ограничение допускает отложенную проверку, оказывается, что сервер создал неуникальный индекс.

Комментарий читателя от 14 марта 2002 года

Привет, Том! Очень полезный ответ. Но, с точки зрения администрирования/сопровождения, не было бы лучше (да, это не так, но все же...) автоматически создавать уникальный индекс при добавлении ограничения первичного ключа? Вместо "похищения" (мне понравилось твое определение) существующего индекса (особенно составного) и проверки уникальности по нему?

Ответ Тома Кайта

Нет, лучше не было бы.

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

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

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

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

Ограничение всегда проверяется сервером процедурно - индекс используется для ускорения доступа к проверяемым данным. Индекс обеспечивает производительность при проверке ограничений, не более того. Уникальный он или нет, - это лишь вопрос производительности.

Как определить, какой индекс используется для обеспечения ограничения? Вопрос читателя от 17 сентября 2002 года

Есть ли в версии 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.

С наилучшими пожеланиями,

  В.К.

http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное