Problem
You want to list tables that have foreign key columns that are not indexed. For example, you want to determine if the foreign keys on table EMP are indexed.
Solution
DB2
Query SYSCAT.TABCONST, SYSCAT.KEYCOLUSE, SYSCAT.INDEXES, and SYSCAT.INDEXCOLUSE:
1 select fkeys.tabname,
2 fkeys.constname,
3 fkeys.colname,
4 ind_cols.indname
5 from (
6 select a.tabschema, a.tabname, a.constname, b.colname
7 from syscat.tabconst a,
8 syscat.keycoluse b
9 where a.tabname = 'EMP'
10 and a.tabschema = 'SMEAGOL'
11 and a.type = 'F'
12 and a.tabname = b.tabname
13 and a.tabschema = b.tabschema
14 ) fkeys
15 left join
16 (
17 select a.tabschema,
18 a.tabname,
19 a.indname,
20 b.colname
21 from syscat.indexes a,
22 syscat.indexcoluse b
23 where a.indschema = b.indschema
24 and a.indname = b.indname
25 ) ind_cols
26 on ( fkeys.tabschema = ind_cols.tabschema
27 and fkeys.tabname = ind_cols.tabname
28 and fkeys.colname = ind_cols.colname )
29 where ind_cols.indname is null
Oracle
Query SYS.ALL_CONS_COLUMNS, SYS.ALL_CONSTRAINTS, and SYS.ALL_ IND_COLUMNS:
1 select a.table_name,
2 a.constraint_name,
3 a.column_name,
4 c.index_name
5 from all_cons_columns a,
6 all_constraints b,
7 all_ind_columns c
8 where a.table_name = 'EMP'
9 and a.owner = 'SMEAGOL'
10 and b.constraint_type = 'R'
11 and a.owner = b.owner
12 and a.table_name = b.table_name
13 and a.constraint_name = b.constraint_name
14 and a.owner = c.table_owner (+)
15 and a.table_name = c.table_name (+)
16 and a.column_name = c.column_name (+)
17 and c.index_name is null
PostgreSQL
Query INFORMATION_SCHEMA.KEY_COLUMN_USAGE, INFORMATION_ SCHEMA.REFERENTIAL_CONSTRAINTS, INFORMATION_SCHEMA.COL-UMNS, and PG_CATALOG.PG_INDEXES:
1 select fkeys.table_name,
2 fkeys.constraint_name,
3 fkeys.column_name,
4 ind_cols.indexname
5 from (
6 select a.constraint_schema,
7 a.table_name,
8 a.constraint_name,
9 a.column_name
10 from information_schema.key_column_usage a,
11 information_schema.referential_constraints b
12 where a.constraint_name = b.constraint_name
13 and a.constraint_schema = b.constraint_schema
14 and a.constraint_schema = 'SMEAGOL'
15 and a.table_name = 'EMP'
16 ) fkeys
17 left join
18 (
19 select a.schemaname, a.tablename, a.indexname, b.column_name
20 from pg_catalog.pg_indexes a,
21 information_schema.columns b
22 where a.tablename = b.table_name
23 and a.schemaname = b.table_schema
24 ) ind_cols
25 on ( fkeys.constraint_schema = ind_cols.schemaname
26 and fkeys.table_name = ind_cols.tablename
27 and fkeys.column_name = ind_cols.column_name )
28 where ind_cols.indexname is null
MySQL
You can use the SHOW INDEX command to retrieve index information such as index name, columns in the index, and ordinal position of the columns in the index. Additionally, you can query INFORMATION_SCHEMA.KEY_COLUMN_USAGE to list the foreign keys for a given table. In MySQL 5, foreign keys are said to be indexed automatically, but can in fact be dropped. To determine whether a foreign key column's index has been dropped you can execute SHOW INDEX for a particular table and compare the output with that of INFORMATION_SCHEMA.KEY_ COLUMN_USAGE.COLUMN_NAME for the same table. If the COLUMN_NAME is listed in KEY_COLUMN_USAGE but is not returned by SHOW INDEX, you know that column is not indexed.
SQL Server
Query SYS.TABLES, SYS.FOREIGN_KEYS, SYS.COLUMNS, SYS.INDEXES, and SYS.INDEX_COLUMNS:
1 select fkeys.table_name,
2 fkeys.constraint_name,
3 fkeys.column_name,
4 ind_cols.index_name
5 from (
6 select a.object_id,
7 d.column_id,
8 a.name table_name,
9 b.name constraint_name,
10 d.name column_name
11 from sys.tables a
12 join
13 sys.foreign_keys b
14 on ( a.name = 'EMP'
15 and a.object_id = b.parent_object_id
16 )
17 join
18 sys.foreign_key_columns c
19 on ( b.object_id = c.constraint_object_id )
20 join
21 sys.columns d
22 on ( c.constraint_column_id = d.column_id
23 and a.object_id = d.object_id
24 )
25 ) fkeys
26 left join
27 (
28 select a.name index_name,
29 b.object_id,
30 b.column_id
31 from sys.indexes a,
32 sys.index_columns b
33 where a.index_id = b.index_id
34 ) ind_cols
35 on ( fkeys.object_id = ind_cols.object_id
36 and fkeys.column_id = ind_cols.column_id )
37 where ind_cols.index_name is null
Discussion
Each vendor uses its own locking mechanism when modifying rows. In cases where there is a parent-child relationship enforced via foreign key, having indexes on the child column(s) can reducing locking (see your specific RDBMS documentation for details). In other cases, it is common that a child table is joined to a parent table on the foreign key column, so an index may help improve performance in that scenario as well.
** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table
0 Comments:
Post a Comment