Home » SQL & PL/SQL » SQL & PL/SQL » Foreign Keys without supporting index
| Foreign Keys without supporting index [message #634205] |
Thu, 05 March 2015 21:03  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
This was a recent "challenge" in an OraFAQ blog from MarcMartens. There are several answers to this on the WEB I think since it is hardly new. But I could not find a way to post files to the challenge thread so I decided to create a topic here and link back to it. This is one solution I created over the years, long winded but reasonably easy to following logically. Other solutions are more or less sophisticated. The trick is that somewhere in whatever solution you use you must do a set operation with the column list. LISTAGG or my distinct on a column matchup with follow-on group by, or even some variation of RELATIONAL ALGREBRA all work. I have also attached two helper scripts I use frequently that were obvious ways to validate results.
Maybe others would like to share their solutions too.
@showfknoindex.sql
21:10:01 SQL> @showfknoindex.sql
TABLE_OWNER TABLE_NAME CONSTRAINT_NAME COLUMN_COUNT
-------------------- ------------------------------ ------------------------------ ------------
BI_DATA_CURRENT EMP EMP_FK1 1
BI_DATA_CURRENT PROJECT PROJECT_FK1 1
DBSNMP BSLN_STATISTICS BSLN_STATISTICS_FK 1
EXFSYS EXF$EXPRSET REF_EXPRSET_ATTRSET 2
EXFSYS RLM$JOBQUEUE RLM$JOINQREF 2
EXFSYS RLM$RULESET RLM$RSET_STATUS_REF 1
EXFSYS RLM$SCHACTLIST RLM$SCHACTFKEY 2
HR COUNTRIES COUNTR_REG_FK 1
...
SCOTT EMP FK_DEPTNO 1
...
SYSTEM MVIEW$_ADV_FJG MVIEW$_ADV_FJG_FK 1
SYSTEM MVIEW$_ADV_GC MVIEW$_ADV_GC_FK 1
SYSTEM REPCAT$_REFRESH_TEMPLATES REPCAT$_REFRESH_TEMPLATES_FK1 1
SYSTEM REPCAT$_REFRESH_TEMPLATES REPCAT$_REFRESH_TEMPLATES_FK2 1
143 rows selected.
Elapsed: 00:00:00.71
21:19:23 SQL> @showconstraints scott emp
OWNER TABLE_NAME CONSTRAINT_NAME C COLUMN_NAME PARENT_CHILD_OWNER PARENT_CHILD_TABLE_NAME C INDEX_NAME
--------------- --------------- -------------------- - --------------- ----------------------- ------------------------------ - -----------
SCOTT EMP PK_EMP P EMPNO PK_EMP
SCOTT EMP FK_DEPTNO R DEPTNO SCOTT DEPT P
2 rows selected.
Elapsed: 00:00:03.36
21:19:34 SQL> @showindexes scott emp
INDEX_NAME COLUMN_NAME INDEX_TYPE UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------------------------- --------- ------------------------------
PK_EMP EMPNO NORMAL UNIQUE USERS
1 row selected.
Elapsed: 00:00:00.00
Kevin
I have also attached for your pleasure the promotional copy of my book on SQL Tuning, along with its associated scripts. If you enjoy it, you can get the book from Amazon at now 40% reduced price from its original list.
[Updated on: Thu, 05 March 2015 21:08] Report message to a moderator |
|
|
|
| Re: Foreign Keys without supporting index [message #634223 is a reply to message #634205] |
Thu, 05 March 2015 23:43  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I'm too lazy to formulate the solution, but it seems like a fun way to do it would be to create tuples of:
OWNER, TABLE_NAME, NESTED_TABLE(COLUMN_NAME, COLUMN_POSITION)
for each of indexes and FK constraints.
Then use SUBMULTISET to find the FKs that have a supporting index and discard them, leaving the ones that dont.
Ross Leishman
|
|
|
|
| Re: Foreign Keys without supporting index [message #634240 is a reply to message #634223] |
Fri, 06 March 2015 04:36 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
I'm lazy, I use T Kytes:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4530093713805#26568859366976
>>that should find them all (assuming reasonable key lengths of 8 or less)
tkyte@TKYTE816> select table_name, constraint_name,
2 cname1 || nvl2(cname2,','||cname2,null) ||
3 nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
4 nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
5 nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
6 columns
7 from ( select b.table_name,
8 b.constraint_name,
9 max(decode( position, 1, column_name, null )) cname1,
10 max(decode( position, 2, column_name, null )) cname2,
11 max(decode( position, 3, column_name, null )) cname3,
12 max(decode( position, 4, column_name, null )) cname4,
13 max(decode( position, 5, column_name, null )) cname5,
14 max(decode( position, 6, column_name, null )) cname6,
15 max(decode( position, 7, column_name, null )) cname7,
16 max(decode( position, 8, column_name, null )) cname8,
17 count(*) col_cnt
18 from (select substr(table_name,1,30) table_name,
19 substr(constraint_name,1,30) constraint_name,
20 substr(column_name,1,30) column_name,
21 position
22 from user_cons_columns ) a,
23 user_constraints b
24 where a.constraint_name = b.constraint_name
25 and b.constraint_type = 'R'
26 group by b.table_name, b.constraint_name
27 ) cons
28 where col_cnt > ALL
29 ( select count(*)
30 from user_ind_columns i
31 where i.table_name = cons.table_name
32 and i.column_name in (cname1, cname2, cname3, cname4,
33 cname5, cname6, cname7, cname8 )
34 and i.column_position <= cons.col_cnt
35 group by i.index_name
36 )
37 / |
|
|
|
| Re: Foreign Keys without supporting index [message #634241 is a reply to message #634205] |
Fri, 06 March 2015 05:49 |
Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Kevin,
I can't find my pre-LISTAGG script, but here is my LISTAGG based script. It is also taking into account ascending order and will consider FK index missing even if there is column-wise matching index with one or more columns in descending order. It also filters most of ORACLE supplied accounts which, btw, should be rewritten for 12C where it is so much simpler since Oracle added ORACLE_MAINTAINED column to data dictionary. Anyway:
-- List foreign keys without matching index
with t1 as (
select a.owner,
a.table_name,
a.constraint_name,
listagg(b.column_name,',') within group(order by b.position) column_list
from dba_constraints a,
dba_cons_columns b
where b.owner = a.owner
and b.table_name = a.table_name
and b.constraint_name = a.constraint_name
and a.constraint_type = 'R'
group by a.owner,
a.table_name,
a.constraint_name
),
t2 as (
select table_owner,
table_name,
listagg(column_name,',') within group(order by column_position) column_list
from dba_ind_columns
where descend = 'ASC'
group by table_owner,
table_name,
index_owner,
index_name
)
select owner,
table_name,
constraint_name
from t1
where not exists(
select 1
from t2
where t2.table_owner = t1.owner
and t2.table_name = t1.table_name
and t2.column_list = t1.column_list
)
and owner not in (
'CTXSYS',
'DBSNMP',
'EXFSYS',
'MDSYS',
'OLAPSYS',
'ORDDATA',
'SYS',
'SYSMAN',
'SYSTEM'
)
and owner not like 'APEX%'
order by owner,
table_name
/
-- Generate CREATE INDEX for foreign keys without matching index
with t1 as (
select a.owner,
a.table_name,
a.constraint_name,
listagg(b.column_name,',') within group(order by b.position) column_list
from dba_constraints a,
dba_cons_columns b
where b.owner = a.owner
and b.table_name = a.table_name
and b.constraint_name = a.constraint_name
and a.constraint_type = 'R'
group by a.owner,
a.table_name,
a.constraint_name
),
t2 as (
select table_owner,
table_name,
listagg(column_name,',') within group(order by column_position) column_list
from dba_ind_columns
where descend = 'ASC'
group by table_owner,
table_name,
index_owner,
index_name
),
t3 as (
select level lvl
from dual
connect by level <= 2
)
select case lvl
when 1 then 'CREATE INDEX ' || owner || '.' || constraint_name || ' ON ' ||
owner || '.' || table_name || '(' || column_list || ') NOLOGGING;'
else 'ALTER INDEX ' || owner || '.' || constraint_name || ' LOGGING;'
end create_index
from t1,
t3
where not exists(
select 1
from t2
where t2.table_owner = t1.owner
and t2.table_name = t1.table_name
and t2.column_list = t1.column_list
)
and owner not in (
'CTXSYS',
'DBSNMP',
'EXFSYS',
'MDSYS',
'OLAPSYS',
'ORDDATA',
'SYS',
'SYSMAN',
'SYSTEM'
)
and owner not like 'APEX%'
order by owner,
table_name,
lvl
/
SY.
|
|
|
|
|
|
|
|
| Re: Foreign Keys without supporting index [message #634330 is a reply to message #634326] |
Sat, 07 March 2015 14:58 |
Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
And if you want to consider a match if existing index is a superset (column-wise) of FK columns:
create or replace
type ColumnList
as
table of varchar2(4000)
/
with t1 as (
select a.owner,
a.table_name,
a.constraint_name,
cast(collect(b.column_name) as ColumnList) column_list
from dba_constraints a,
dba_cons_columns b
where b.owner = a.owner
and b.table_name = a.table_name
and b.constraint_name = a.constraint_name
and a.constraint_type = 'R'
group by a.owner,
a.table_name,
a.constraint_name
),
t2 as (
select table_owner,
table_name,
cast(collect(column_name) as ColumnList) column_list
from dba_ind_columns
where descend = 'ASC'
group by table_owner,
table_name,
index_owner,
index_name
)
select owner,
table_name,
constraint_name
from t1
where not exists(
select 1
from t2
where t2.table_owner = t1.owner
and t2.table_name = t1.table_name
and t1.column_list multiset except t2.column_list = ColumnList()
)
and owner not in (
'CTXSYS',
'DBSNMP',
'EXFSYS',
'MDSYS',
'OLAPSYS',
'ORDDATA',
'SYS',
'SYSMAN',
'SYSTEM'
)
and owner not like 'APEX%'
order by owner,
table_name
/
SY.
|
|
|
|
Goto Forum:
Current Time: Thu Aug 27 01:05:43 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
