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 1Nothing 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.
...deleting from child table 2
...deleting from child table 3
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):
SELECTThat 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:
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
WHERE TABLE_NAME = 'MY_TABLE'
AND owner = 'SCHEMA_NAME'
AND constraint_type = 'P' )
WHERE b.table_owner = a.table_owner (+)
AND b.column_name = a.column_name (+)
AND b.table_name = a.table_name (+)
( CASENow I have my CREATE INDEX statements and all is well.
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 || ' )
END ) sql_text
I run the teardown script again and it finishes like it should.