таблицы и результат сравнивается с полученным INFORMATION SCHEMA.KEY COLUMN USAGE.COLUMN NAME для той же таблицы. Если COLUMN NAME есть в KEY COLUMN USAGE, но не возвращено в результате выполнения SHOW INDEX, делаем вывод, что столбец непроиндексирован.

SQL Server

Делаем запрос к SYS.TABLES, SYS.FOREIGN KEYS, SYS.COLUMNS, SYS.INDEXES и SYS.INDEX COLUMNS:

1 2 3 4

9 10

11 12 13 14 15 1b 17 18 19 20 21 22 23 24 25 2b 27 28 29

30 31

34 35

select fkeys.table name,

fkeys.constraint name, fkeys.column name, ind cols.index name from (

select

a.ob]ect id, d.column id, a.name table name, b.name constraint name, d.name column name from sys.tables a join

sys.foreign keys b on ( a.name = EMP

and a.object id = b.parent object id

join

sys.foreign key columns c on ( b.object id = c.constraint object id )

join

sys.columns d on ( c.constraint column id = d.column id and a.object id = d.object id

) fkeys left join (

select a.name index name, b.object id, b.column id from sys.indexes a,

sys.index columns b where a.index id = b.index id ) ind cols

on ( fkeys.object id = ind cols.object id and fkeys.column id = ind cols.column id ) where ind cols.index name is null

Обсуждение

Каждый производитель использует собственные механизмы блокировки при изменении строк. В случаях, когда отношение родитель-потомок организовано посредством внешнего ключа, индексация дочер-него(их) столбца(ов) может сократить время или количество блокировок (подробнее смотрите в специальной документации своей СУБД). В остальных случаях дочерняя таблица обычно объединяется с родительской по столбцу внешнего ключа, так что индекс может способствовать повышению производительности и в этом сценарии.

Использование SQL для генерирования SQL

Задача

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

Решение

Основная идея состоит в использовании строки таблицы для построения выражений SQL. При этом значения, которыми должны быть заполнены выражения (например, имя объекта, к которому обращена команда), берутся из данных таблицы. Помните, запросы только генерируют выражения, которые вы затем должны выполнять посредством сценария, вручную или любым другим способом. Ниже приведены примеры запросов, которые работают в системе Oracle. Для других СУБД техника точно такая же, отличие будет лишь в именах таблиц словаря данных и в форматировании данных. Показанный результат выполнения приведенных ниже запросов является фрагментом строк, которые были возвращены экземпляром Oracle на моем компьютере. Вы, конечно же, получите другие результирующие множества.

/* генерируем SQL для подсчета всех строк всех таблиц */

select select count(*) from table name; cnts from user tables;

/* деактивируем внешние ключи всех таблиц */

select alter table table name

disable constraint constraint name; cons from user constraints where constraint type = R;

CONS

alter table ANT disable constraint ANT FK; alter table BONUS disable constraint BONUS FK; alter table DEMO1 disable constraint DEMO1 FK; alter table DEMO2 disable constraint DEMO2 FK; alter table DEPT disable constraint DEPT FK; alter table DUMMY disable constraint DUMMY FK; alter table EMP disable constraint EMP FK; alter table EMP SALES disable constraint EMP SALES FK; alter table EMP SCORE disable constraint EMP SCORE FK; alter table PROFESSOR disable constraint PROFESSOR FK;

/* генерируем сценарий вставки из некоторых столбцов таблицы EMP */

select insert into emp(empno,ename,hiredate) chr(10) values( empno,ename ,to date(hiredate) ); inserts from emp where deptno = 10;

INSERTS

insert into emp(empno,ename,hiredate)

values( 7782,CLARK,to date(09-JUN-1981 00:00:00) );

insert into emp(empno,ename,hiredate)

values( 7839,KING,to date(17-NOV-1981 00:00:00) );

insert into emp(empno,ename,hiredate)

values( 7934,MILLER,to date(23-JAN-1982 00:00:00) );