Unindexed Foreign Keys – What is Wrong with this Quote?

September 5, 2011

I often look for easier ways to accomplish time consuming tasks, and I suspect that is part of the reason why I buy so many computer books.  In Recipe 2-5 (page 60) of the “Oracle Database 11g Performance Tuning Recipes” book there is a SQL statement to determine which database columns (owned by the user) have foreign key constraints without indexes.  The same SQL statement is also found on page 243 of the book Oracle SQL Recipes: A Problem-Solution Approach.  What caught my attention is the length of the SQL statement – it is considerably shorter than the SQL statement I had been using, so that should certainly reduce the amount of unnecessary typing (and free up a couple of bytes in the library cache).  The SQL statement as it appears in these two books:

select
  a.constraint_name cons_name
 ,a.table_name tab_name
 ,b.column_name cons_column
 ,nvl(c.column_name,'***No Index***') ind_column
from user_constraints a
     join
     user_cons_columns b on a.constraint_name = b.constraint_name
     left outer join
     user_ind_columns c on b.column_name = c.column_name
                       and b.table_name = c.table_name
where constraint_type = 'R'
order by 2,1;

Using A, B, and C for table aliases… that must be what is wrong with the above SQL statement.

What, if anything, is wrong with the above quote from the book?

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

—

In case you are wondering, the following is the SQL statement that I had been using to locate unindexed foreign key columns (this SQL statement is based on a SQL statement that was found in the Expert Oracle Database Architecture book).

SELECT
  DECODE(B.TABLE_NAME, NULL, '*Check*', 'OK' ) STATUS,
  A.OWNER,
  A.TABLE_NAME,
  A.COLUMNS,
  B.COLUMNS INDEX_COLUMNS
FROM
  (SELECT
    A.OWNER,
    SUBSTR(A.TABLE_NAME,1,30) TABLE_NAME,
    SUBSTR(A.CONSTRAINT_NAME,1,30) CONSTRAINT_NAME,
    MAX(DECODE(POSITION, 1,     SUBSTR(COLUMN_NAME,1,30),NULL)) ||
    MAX(DECODE(POSITION, 2,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
    MAX(DECODE(POSITION, 3,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
    MAX(DECODE(POSITION, 4,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
    MAX(DECODE(POSITION, 5,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
    MAX(DECODE(POSITION, 6,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
    MAX(DECODE(POSITION, 7,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
    MAX(DECODE(POSITION, 8,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
    MAX(DECODE(POSITION, 9,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
    MAX(DECODE(POSITION,10,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
    MAX(DECODE(POSITION,11,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
    MAX(DECODE(POSITION,12,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
    MAX(DECODE(POSITION,13,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
    MAX(DECODE(POSITION,14,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
    MAX(DECODE(POSITION,15,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
    MAX(DECODE(POSITION,16,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) COLUMNS
  FROM
    DBA_CONS_COLUMNS A,
    DBA_CONSTRAINTS B
  WHERE
    A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
    AND A.OWNER=B.OWNER
    AND B.CONSTRAINT_TYPE = 'R'
  GROUP BY
    A.OWNER,
    SUBSTR(A.TABLE_NAME,1,30),
    SUBSTR(A.CONSTRAINT_NAME,1,30) ) A,
    (SELECT
      TABLE_OWNER,
      SUBSTR(TABLE_NAME,1,30) TABLE_NAME,
      SUBSTR(INDEX_NAME,1,30) INDEX_NAME,
      MAX(DECODE(COLUMN_POSITION, 1,
      SUBSTR(COLUMN_NAME,1,30),NULL)) ||
      MAX(DECODE(COLUMN_POSITION, 2,', '||
      SUBSTR(COLUMN_NAME,1,30),NULL)) ||
      MAX(DECODE(COLUMN_POSITION, 3,', '||
      SUBSTR(COLUMN_NAME,1,30),NULL)) ||
      MAX(DECODE(COLUMN_POSITION, 4,', '||
      SUBSTR(COLUMN_NAME,1,30),NULL)) ||
      MAX(DECODE(COLUMN_POSITION, 5,', '||
      SUBSTR(COLUMN_NAME,1,30),NULL)) ||
      MAX(DECODE(COLUMN_POSITION, 6,', '||
      SUBSTR(COLUMN_NAME,1,30),NULL)) ||
      MAX(DECODE(COLUMN_POSITION, 7,', '||
      SUBSTR(COLUMN_NAME,1,30),NULL)) ||
      MAX(DECODE(COLUMN_POSITION, 8,', '||
      SUBSTR(COLUMN_NAME,1,30),NULL)) ||
      MAX(DECODE(COLUMN_POSITION, 9,', '||
      SUBSTR(COLUMN_NAME,1,30),NULL)) ||
      MAX(DECODE(COLUMN_POSITION,10,', '||
      SUBSTR(COLUMN_NAME,1,30),NULL)) ||
      MAX(DECODE(COLUMN_POSITION,11,', '||
      SUBSTR(COLUMN_NAME,1,30),NULL)) ||
      MAX(DECODE(COLUMN_POSITION,12,', '||
      SUBSTR(COLUMN_NAME,1,30),NULL)) ||
      MAX(DECODE(COLUMN_POSITION,13,', '||
      SUBSTR(COLUMN_NAME,1,30),NULL)) ||
      MAX(DECODE(COLUMN_POSITION,14,', '||
      SUBSTR(COLUMN_NAME,1,30),NULL)) ||
      MAX(DECODE(COLUMN_POSITION,15,', '||
      SUBSTR(COLUMN_NAME,1,30),NULL)) ||
      MAX(DECODE(COLUMN_POSITION,16,', '||
      SUBSTR(COLUMN_NAME,1,30),NULL)) COLUMNS
    FROM
      DBA_IND_COLUMNS
    GROUP BY
      TABLE_OWNER,
      SUBSTR(TABLE_NAME,1,30),
      SUBSTR(INDEX_NAME,1,30)) B
    WHERE
      A.TABLE_NAME = B.TABLE_NAME (+)
      AND A.OWNER=B.TABLE_OWNER(+)
      AND B.COLUMNS (+) LIKE A.COLUMNS || '%'
ORDER BY
  A.OWNER,
  A.TABLE_NAME;

—

Are you feeling that it might be an ANSI problem with the book’s SQL statement?  I certainly would prefer to use the shorter version of the SQL statement – I have an aversion to unnecessary typing.

—

While on the subject of creating indexes on foreign key columns, are those indexes created just to reduce the chance of deadlocks, or is it done primarily for SELECT query performance as described in the book (Google Books view)?  think about this question a bit.