Toad World® Forums

Index all FK in CodeTester Schema!


#1

Dear CodeTester team,

please make sure that all foreign keys in the CodeTester repository schema are indexed.
We encountered massive deadlocks when working concurrently on some test definitions. We finally concluded that this was due to the missing indexes.
Creating those indexes manually solved our problems.
A nice side effect was a massive performance boost.

Personally I classify this issue as critical…

Kind regards
Keyan


#2

Thanks, Keyen, this is very good to know. I will look into indexing all foreign keys. In the meantime, could you share with us which foreign keys/indexes fixed your problem more specifically?

SF


#3

Hello Steven,

we set our DBA on that task so I’m not sure what exactly they did. But from my weekend memory it was the table QU_INPUT that caused the blocking locks.

Kind regards
Keyan


#4

Thanks, Keyan.

If you could run this query while connected to the CodeTester repository, and send the results to me at steven.feuerstein@quest.com, Iwould appreciate it very much.

SELECT table_name
|| ’ (’
||index_name
|| ') ’
||column_position
|| ’ - ’
||column_name
FROM user_ind_columns
WHERE table_name LIKE ‘QU%’
ORDER BY table_name, index_name, column_position

Cheers, SF

[ NOTE: Please ignore garbled text below. I am working on getting rid of it. ]


#5

AskTom says: ALWAYS
The tables QU_SUITE_TREE and QU_SUITE_ACCESS in 2.0 are missing foreign key indexes in production release. Also, QU_SUITE_ACCESS has no primary key (!!). I hand-added UNIVERSAL_ID on that one as PK. Probably not important but complete.

here is SQL taken with faith from askTom (Thank you! )
to show foreign key index gaps in a schema:

select table_name, constraint_name,
cname1 || nvl2(cname2,’,’||cname2,null) ||
nvl2(cname3,’,’||cname3,null) || nvl2(cname4,’,’||cname4,null) ||
nvl2(cname5,’,’||cname5,null) || nvl2(cname6,’,’||cname6,null) ||
nvl2(cname7,’,’||cname7,null) || nvl2(cname8,’,’||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count() col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = ‘R’
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(
)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)


#6

We have missed those 2 tables and will be adding the foreign keys in the 2.0.1 patch. Thanks for bringing this to our attention.

Olga.