Hi Guys,
maybe a bit of a duplicate topic, but I'm not sure if it is really tackled (e.g. this Post). With TOAD 15 the Query for the Join-Suggestions has been changed considerably.
As per the resolved issues in this release the query has been changed according tho this Post. The now newly executed query takes up to a few minutes to complete, regardless if it is on our production or test-systems.
Also with renewed data-dictonary and fixed-object statistics the "old" way performs significantly better than the new one. To reproduce the problem I simply need to run the below queries, which are executed during the population of the pick-list for the join-suggestions.
Our current workaround is to deactivate the Join-Suggestions in the "Code Insight" Options. This remedies the situation immediatly but we have to do without the suggestions.
Does anybody have the same experience with these queries? Is there a way to improve the performance of this feature and not turning it off?
-- TOAD 15.0.97.1178 -- sql_id: amgj7m00rusm6
SELECT 'REF_INTEGRITY' AS TYPE,
rel.*,
cc1.column_name pk_column,
cc2.column_name fk_column,
cc1.position column_position
FROM (SELECT c1.owner AS parent_owner,
c1.table_name AS parent_table,
c2.r_constraint_name AS pk_constraint,
c2.owner AS child_owner,
c2.table_name AS child_table,
c2.constraint_name AS fk_constraint
FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2
WHERE ( (c1.table_name = :name AND c1.owner = :owner)
OR (c2.table_name = :name AND c2.owner = :owner))
AND c1.constraint_type IN ('P', 'U')
AND c2.constraint_type = 'R'
AND c2.r_constraint_name = c1.constraint_name
AND c2.r_owner = c1.owner) rel,
sys.DBA_CONS_COLUMNS cc1,
sys.DBA_CONS_COLUMNS cc2
WHERE cc1.owner = rel.parent_owner
AND cc1.constraint_name = rel.pk_constraint
AND cc1.table_name = rel.parent_table
AND cc2.owner = rel.child_owner
AND cc2.table_name = rel.child_table
AND cc2.constraint_name = rel.fk_constraint
AND cc1.position = cc2.position
ORDER BY 4, 10
-- TOAD 14.2.104.1069 sql_id: 5whrm028c22yq
Select 'REF_INTEGRITY' as type,
rel.parent_owner,
rel.parent_table,
rel.pk_constraint,
rel.child_owner,
rel.child_table,
rel.fk_constraint,
cc1.column_name pk_column,
cc2.column_name fk_column,
cc1.position column_position
FROM (Select c1.owner AS parent_owner,
c1.table_name AS parent_table,
c2.r_constraint_name AS pk_constraint,
c2.owner AS child_owner,
c2.table_name AS child_table,
c2.constraint_name AS fk_constraint
FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2
WHERE c1.table_name = :name
AND c1.owner = :owner
AND c1.constraint_type IN ('P', 'U')
AND c2.constraint_type = 'R'
AND c2.r_constraint_name = c1.constraint_name
AND c2.r_owner = c1.owner) rel,
sys.DBA_CONS_COLUMNS cc1,
sys.DBA_CONS_COLUMNS cc2
WHERE cc1.owner = rel.parent_owner
AND cc1.constraint_name = rel.pk_constraint
AND cc2.owner = rel.child_owner
AND cc2.constraint_name = rel.fk_constraint
AND cc1.position = cc2.position
UNION ALL
Select 'REF_INTEGRITY' as type,
rel.parent_owner,
rel.parent_table,
rel.pk_constraint,
rel.child_owner,
rel.child_table,
rel.fk_constraint,
cc1.column_name pk_column,
cc2.column_name fk_column,
cc1.position column_position
FROM (Select c1.owner AS parent_owner,
c1.table_name AS parent_table,
c2.r_constraint_name AS pk_constraint,
c2.owner AS child_owner,
c2.table_name AS child_table,
c2.constraint_name AS fk_constraint
FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2
WHERE c2.table_name = :name
AND c2.owner = :owner
AND c1.constraint_type IN ('P', 'U')
AND c2.constraint_type = 'R'
AND c2.r_constraint_name = c1.constraint_name
AND c2.r_owner = c1.owner) rel,
sys.DBA_CONS_COLUMNS cc1,
sys.DBA_CONS_COLUMNS cc2
WHERE cc1.owner = rel.parent_owner
AND cc1.constraint_name = rel.pk_constraint
AND cc2.owner = rel.child_owner
AND cc2.constraint_name = rel.fk_constraint
AND cc1.position = cc2.position
ORDER BY 4, 10
Thanks 