На таблицу установлено ограничение (констрейнт) внешнего ключа. Но по этому ключу таблицу не проиндексировали. Насколько важно наличие такого индекса?

• Как найти таблицы с неиндексированными внешними ключами?
• Какие проблемы могут возникнуть при наличии неиндексированных внешних ключей?
• А может не нужно суетиться по этому поводу?
Ответам на эти вопросы посвящена эта статья.

Когда возникают проблемы из-за отсутствия индекса по внешнему ключу? В основном это случается в двух случаях:

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

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

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

А теперь рассмотрим второй случай – более реальный. Достаточно часто приходится удалять записи в главной таблице. В этом случае подчиненная таблица полностью блокируется и любые изменения в ней становятся невозможными. А все, потому что пользователь захватил в монопольное владение слишком большой объем данных. И если другому пользователю нужны будут для изменения, захваченные первым пользователем строчки, возникнет зависание приложения. К тому же повышается вероятность взаимных блокировок. То есть ORACLE избыточно блокирует данные.

Как обнаружить в работающих приложениях неиндексированные внешние ключи? Рассмотрим скрипт, который предлагает Том Кайт:

create table dbst_p ( x int primary key );

create table dbst_c ( y references dbst_p );

после создания этих табличек выполним команду:

select table_name, constraint_name,
       cname1 || nvl2(cname2,','||cname2,null) ||
       nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
       nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
       nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null) 
            columns
  from ( select b.table_name, 
                b.constraint_name,
                max(decode( position, 1, column_name, null )) cname1,
                max(decode( position, 2, column_name, null )) cname2,
                max(decode( position, 3, column_name, null )) cname3,
                max(decode( position, 4, column_name, null )) cname4,
                max(decode( position, 5, column_name, null )) cname5,
                max(decode( position, 6, column_name, null )) cname6,
                max(decode( position, 7, column_name, null )) cname7,
                max(decode( position, 8, column_name, null )) cname8,
                count(*) col_cnt
           from (select substr(table_name,1,30) table_name, 
                        substr(constraint_name,1,30) constraint_name,
                        substr(column_name,1,30) column_name,
                        position
                   from user_cons_columns ) a, 
                user_constraints b
          where a.constraint_name = b.constraint_name
            and b.constraint_type = 'R'  --R referential integrity 
          group by b.table_name, b.constraint_name
       ) cons
 where col_cnt > ALL 
         ( select count(*)
             from user_ind_columns i
            where i.table_name = cons.table_name
              and i.column_name in (cname1, cname2, cname3, cname4, 
                                    cname5, cname6, cname7, cname8 )
              and i.column_position <= cons.col_cnt
            group by i.index_name
         )

что делает эта выборка?

Сделает разбор этого запроса. Предполагаем, что ограничение внешнего ключа включает не более 8 столбцов. Рассмотрим вложенный запрос

select b.table_name, 
                b.constraint_name,
                max(decode( position, 1, column_name, null )) cname1,
                max(decode( position, 2, column_name, null )) cname2,
                max(decode( position, 3, column_name, null )) cname3,
                max(decode( position, 4, column_name, null )) cname4,
                max(decode( position, 5, column_name, null )) cname5,
                max(decode( position, 6, column_name, null )) cname6,
                max(decode( position, 7, column_name, null )) cname7,
                max(decode( position, 8, column_name, null )) cname8,
                count(*) col_cnt
           from (select substr(table_name,1,30) table_name, 
                        substr(constraint_name,1,30) constraint_name,
                        substr(column_name,1,30) column_name,
                        position
                   from user_cons_columns ) a, 
                user_constraints b
          where a.constraint_name = b.constraint_name
            and b.constraint_type = 'R'
          group by b.table_name, b.constraint_name

Из представления user_cons_columns выбираем колонки, которые включены в ограничения. Затем для каждой таблицы, имеющей ограничения внешнего ключа, формируем строку с полями, содержащими названия колонок, входящих в ограничения. То есть для каждого нашего констрейнта есть одна запись, содержащая список столбцов, входящих в ограничения внешнего ключа. col_cnt –количество таких столбцов для конкретного констрейнта.

select count(*)
             from user_ind_columns i
            where i.table_name = cons.table_name
              and i.column_name in (cname1, cname2, cname3, cname4, 
                                    cname5, cname6, cname7, cname8 )
              and i.column_position <= cons.col_cnt
            group by i.index_name

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

Конструкция where col_cnt > ALL (…….) проверяет для каждого нашего ограничения: для каких выбранных ограничений количество столбцов превышает количество столбцов во всех индексах для конкретной таблицы. То есть мы получим список ограничений с перечнем столбцов, для которых не существует индексов, обеспечивающих ограничение внешнего ключа. В нашем случае мы получаем:

TABLE_NAME|CONSTRAINT_NAME|COLUMNS
DBST_C|SYS_C0052165|Y

Получив такой перечень, рекомендую провести индексацию внешний ключей, и ваша жизнь станет прекрасной.

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

• большая вероятность возникновения взаимных блокировок.
• при удалении из основной таблицы, если установлено ограничение on delete cascade, вызывается каскадное удаление из подчиненной таблицы. И если нет индекса на внешний ключ, то оракл вынужден делать полный просмотр подчиненной таблицы. А это не есть хорошо - требуются большие ресурсы и значительные расходы времени.
• для запроса из главной таблицы в подчиненную, при наличии в конструкции where условия на ключ, будет значительное замедление работы.

Можно не беспокоиться о наличии индекса на внешний ключ при следующих условиях:

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