Foreign key indexes come back to bite in Oracle 11g

When I started out doing database design, in Oracle 7, I was told that a foreign key always needed an associated index to help with locking issues. I never really questioned it as I was the new guy and did what I was told by the experienced people. Ever since it has been the standard to do this and things were going along quite well until we got to 11g.
While I was still doing database design and including FK indexes as I went (most of the time) other people doing design were a little less careful and some foreign keys were being created without indexes or were created with indexes that had their columns in a different order to the FK (which means the index is not used).
Oracle 11g seems to be a bit more rigid than previous versions and soon we had production locking problems that were locking up parts of the system in a nasty way.

So I had to find all the foreign key indexes that have columns in a different sequence to the columns in the actual foreign key. Fortunately the naming standards mean that all the foreign keys have the name in the format that ends with FK and all the related indexes have the same name but with '_I' appended to the end.
For example a foreign key might be called DEPT_EMP_FK and the index will be called DEPT_EMP_FK_I.

So to find sequence issues I can use

SELECT dbac.table_name,
dbacc.constraint_name,
dbaic.index_name,
dbacc.column_name,
dbacc.position fk_pos,
dbaic.column_position index_pos
FROM dba_constraints dbac,
dba_cons_columns dbacc,
dba_indexes dbai,
dba_ind_columns dbaic
WHERE dbac.owner = 'SCHEMA_OWNER' AND
dbac.constraint_type = 'R' AND
dbac.constraint_name LIKE '%FK' AND
dbacc.constraint_name = dbac.constraint_name AND
dbai.owner = dbac.owner AND
dbai.index_name = dbac.constraint_name || '_I' AND
dbaic.index_name = dbai.index_name AND
dbaic.column_name = dbacc.column_name AND
dbaic.column_position != dbacc.position
ORDER BY 1,2,3,4,5,6


and to find foreign keys that don't have any indexes at all I can use

SELECT dbac.table_name,
dbac.constraint_name
FROM dba_constraints dbac
WHERE dbac.owner = 'SIS_OWNER' AND
dbac.constraint_type = 'R' AND
dbac.constraint_name LIKE '%FK' AND
NOT EXISTS(
SELECT 'x'
FROM dba_indexes dbai
WHERE dbai.owner = dbac.owner AND
dbai.index_name = dbac.constraint_name || '_I')
ORDER BY 1,2


But this will bring back some instances that don't need to have a foreign key index. This is because if all the columns in the foreign key are in the primary key and are the first columns in the primary key (and in the correct order) then Oracle will use the primary key index. So any foreign key index will not be used.
So we can refine the query above to cope with finding only those that are not already in the primary key in the correct order.

SELECT dbac.table_name,
dbac.constraint_name
FROM dba_constraints dbac
WHERE dbac.owner = 'SCHEMA_OWNER' AND
dbac.constraint_type = 'R' AND
dbac.constraint_name LIKE '%FK' AND
NOT EXISTS(
SELECT 'x'
FROM dba_indexes dbai
WHERE dbai.owner = dbac.owner AND
dbai.index_name = dbac.constraint_name || '_I') AND
EXISTS(
SELECT 'x'
FROM dba_constraints dbac1,
dba_cons_columns dbacc1,
dba_constraints dbac2,
dba_cons_columns dbacc2
WHERE dbac1.owner = dbac.owner AND
dbac1.table_name = dbac.table_name AND
dbac1.constraint_name = dbac.constraint_name AND
dbacc1.constraint_name = dbac1.constraint_name AND
dbacc1.owner = dbac1.owner AND
dbac2.owner = dbac1.owner AND
dbac2.table_name = dbac1.table_name AND
dbac2.constraint_type = 'P' AND
dbacc2.constraint_name = dbac2.constraint_name AND
dbacc2.owner = dbac2.owner AND
dbacc2.column_name = dbacc1.column_name AND
dbacc2.position != dbacc1.position)
ORDER BY 1,2

Finding all the child tables of a table in Oracle

When doing schema changes there are often times when I want to know all the child tables of the current table. Also when doing a delete and I want to find child tables to make sure the data is cleaned up correctly.

The SQL to do this is pretty easy.

select table_name, constraint_name 

from all_constraints

where constraint_type = 'R'

and r_constraint_name in

    (select constraint_name

    from all_constraints

    where upper(table_name) = upper(:OBJECT_NAME)

    and constraint_type IN ('P', 'U'))

    order by table_name

It would be nice if some of the IDEs for Oracle development had this as standard information about a table.

I created an XML extension for Oracle SQL Developer to give this information as a new tab. Originally posted here.

It is reproduced below as that link needs a login to get to.

Following on from Sue Harper's blog entry about adding your own tabs to SQL Developer this example adds a tab to display all child tables that have a foreign key pointing to the Primary Key or a Unique Key in the context table.

Following the directions on Sue's blog about how to create your own tab http://sueharper.blogspot.com/2007/04/how-to-add-tabs-to-sql-developer.html you can create your own tabs as required.

I wanted a tab that would show me all the child tables of the current table.

The structure of the XML file needed for this is as below

<items>

<item type="editor" node="TableNode" vertical="true">

<title><![CDATA[Child Tables]]></title>

<query>

<sql>

<![CDATA[

select  table_name,

constraint_name 

from all_constraints

where  constraint_type = 'R'

and r_constraint_name in

(select constraint_name

from all_constraints

where upper(table_name) = upper(:OBJECT_NAME)

and constraint_type IN ('P', 'U'))

order by table_name]]>

</sql>

</query>

</item>

</items>

Might be an enhancement I can submit to Quest for SQL Navigator.

Oracle PL/Scope could be useful

Background

PL/Scope is functionality in PL/SQL that collects details of identifiers like variables, parameters, function calls, etc from compiled PL/SQL packages and then stores the details in the database. These details can then be viewed using the ALL_IDENTIFIERS view.


For further information on PL/Scope see this article by Steven Feuerstein and the Oracle Documentation. It can be enabled for a session by executing the following:

ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL' /

To disable PL/Scope use:

ALTER SESSION SET  plscope_settings='IDENTIFIERS:NONE' /

The data in the view can then be used for various analysis and quality checks.

Sample usage - Variable declared but never used

To find if any variables have been created in PL/SQL code but are never used. For some large packages it is easy to lose track of variables that may have been removed from functionality but their declarations remain. The following SQL example will identify any occurrences in the package MY_BIG_PACKAGE where a variable has been declared but not used on any other line in the package.

select *
from all_identifiers ai
where ai.owner = 'SCHEMA_OWNER' and
ai.type = 'VARIABLE' and
ai.object_name = 'MY_BIG_PACKAGE' and
not exists
(select 'x'
from all_identifiers ai2
where ai2.signature = ai.signature and
ai2.line != ai.line)

Sample usage - Conforming to naming standards

This example will find any variables in the package MY_BIG_PACKAGE that do not start with either of my standard prefixes v_ for standard variables or gv_ for global variables.

Note that the even though the code has the value in lower-case the value will be stored as upper-case when selecting ALL_IDENTIFIERS

select *
from all_identifiers ai
where ai.owner = 'SCHEMA_OWNER' and
ai.object_name = 'MY_BIG_PACKAGE' and;
ai.type = 'VARIABLE' and
ai.usage = 'DECLARATION' and
(ai.name not like 'V/_%' ESCAPE '/' and
ai.name not like 'GV/_%' ESCAPE '/')

This example can then be extended to look at many other standards like constants, parameters, cursors and even the procedure names themselves within the same package.

SELECT *
FROM all_identifiers ai
WHERE ai.owner = 'SCHEMA_OWNER' AND
ai.object_name = 'MY_BIG_PACKAGE' AND
((ai.type = 'VARIABLE' AND
ai.usage = 'DECLARATION' AND
-- variables start with v_ or gv_
(ai.name NOT LIKE 'V/_%' ESCAPE '/' AND 
ai.name NOT LIKE 'GV/_%' ESCAPE '/')) OR
-- cursor names start with c_
(ai.type = 'CURSOR' AND
ai.usage = 'DECLARATION' AND
ai.name NOT LIKE 'C/_%' ESCAPE '/') OR
(ai.type = 'FORMAL IN' AND -- parameters
ai.usage = 'DECLARATION' AND
-- parameters start with p_
(ai.name NOT LIKE 'P/_%' ESCAPE '/' AND
-- cursor parameters start with cp_
ai.name NOT LIKE 'CP/_%' ESCAPE '/' ))OR
-- constants start with cst_
(ai.type = 'CONSTANT' AND
ai.usage = 'DECLARATION' AND
ai.name NOT LIKE 'CST/_%' ESCAPE '/') OR
-- procedure names start with 3 letters then p_
(ai.type = 'PROCEDURE' AND
ai.usage = 'DECLARATION' AND
ai.name NOT LIKE '___P/_%' ESCAPE '/'))


Of course these are my standards and would need to be customised for your own application.