Tuesday, December 2, 2008

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.

1 comment:

Tom said...

What you would do, is look at what your session was waiting on. You would probably see some kind of enqueue wait.