Index those Foreign Keys

I've been reading about this phenomenon for years over on asktom, but I had never actually encountered the problem, until today that is.

I'm helping out doing a small piece on another project. Trying to get back into the habit of "good" unit-testing, I have created some test data. One build script that runs the whole thing, a few user scripts and finally a teardown script that deletes all the data I have created.

Naturally, I run it via SQL*Plus; turning feedback and echo off and using a liberal number of PROMPT directives (very similar to how the ApEx install goes coincidentally). This is what my teardown script reports:

...deleting from child table 1
...deleting from child table 2
...deleting from child table 3
...etc, etc

Nothing fancy. Then I realized on the final DELETE it was hanging up. Any locked objects? Nope. Maybe it's the trigger? I checked, only saw ON INSERT OR UPDATE, confirmed that with another developer. He suggested unindexed foreign keys. Huh? Really? That's impossible you say.

It wasn't impossible. It was true. So I ran my copy of the Tom's "find unindexed foreign keys."

Quite a few "****" which is not good.

So I went searching for them myself and came up with this little query (for my specifc table):

SELECT 
  b.table_owner,
  b.table_name,
  b.constraint_name,
  b.column_name,
  a.index_name
FROM
  dba_ind_columns a,
  (
    SELECT 
      a.owner table_owner,
      a.table_name, 
      a.constraint_name, 
      b.column_name
    FROM 
      dba_constraints a,
      dba_cons_columns b
    WHERE a.constraint_name = b.constraint_name
      AND a.owner = b.owner
      AND a.table_name = b.table_name
      AND a.r_constraint_name = ( SELECT constraint_name
                                FROM DBA_CONSTRAINTS
                                WHERE TABLE_NAME = 'MY_TABLE'
                                  AND owner = 'SCHEMA_NAME'
                                  AND constraint_type = 'P' )
  ) b
WHERE b.table_owner = a.table_owner (+)
  AND b.column_name = a.column_name (+)
  AND b.table_name = a.table_name (+)
ORDER BY 
  b.table_name, 
  b.constraint_name

That gave me a list of all the columns referencing the primary key (some 37 different tables). If index_name was NULL, then I knew I had to add one. Since I have this obsession lately with generating code, I just used this query and added the following:

  ( CASE
      WHEN a.index_name IS NULL THEN
        'CREATE INDEX schema_name.' || b.table_name || '_' || b.column_name || '_idx
        ON ' || b.table_owner || '.' || b.table_name || '( ' || b.column_name || ' )
        TABLESPACE my_tablespace;' 
    END ) sql_text

Now I have my CREATE INDEX statements and all is well.

I run the teardown script again and it finishes like it should.