# Loading constraints **URL:** \ **Category:** Toad for Oracle - Beta\ **Created:** [March 23, 2022, 2:24pm UTC](https://forums.toadworld.com/t/loading-constraints/54942 "2022-03-23T14:24:36Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![marc\_109](https://avatars.discourse-cdn.com/v4/letter/m/74df32/32.png) [@marc\_109](https://forums.toadworld.com/u/marc_109)\ **Post date:** [March 23, 2022, 2:24pm UTC](https://forums.toadworld.com/t/loading-constraints/54942/1 "2022-03-23T14:24:37Z")
I was trying out your new constraint item ![image](https://global.discourse-cdn.com/quest/original/2X/c/c53f9e0eb2c117e6536d6c4cd4532f7010e53c5e.png) but toad still locked up and I needed to kill it . The pop up was frozen for a few minutes. ![image](https://global.discourse-cdn.com/quest/original/2X/5/5dfda04cbe831b59504296dfc0cb5db5c6699a91.png) ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [March 23, 2022, 9:09pm UTC](https://forums.toadworld.com/t/loading-constraints/54942/2 "2022-03-23T21:09:18Z")
Hi Marc, If you turn on spool SQL, you should be seeing it running this: ```auto WITH ref_cons AS (Select c.owner, c.constraint_name, c.table_name, c.r_owner, c.r_constraint_name, cc.position, cc.column_name FROM sys.DBA_CONSTRAINTS c, sys.DBA_CONS_COLUMNS cc WHERE c.constraint_type = 'R' AND c.owner = cc.owner AND sys_context('USERENV', 'CURRENT_SCHEMA') in (c.r_owner, c.owner) AND c.constraint_name = cc.constraint_name), pk_cons AS (Select c.owner, c.constraint_name, c.table_name, cc.position, cc.column_name FROM sys.DBA_CONSTRAINTS c, sys.DBA_CONS_COLUMNS cc WHERE c.constraint_type IN ('P', 'U') AND c.owner = cc.owner AND c.owner = sys_context('USERENV', 'CURRENT_SCHEMA') AND c.constraint_name = cc.constraint_name) SELECT 'REF_INTEGRITY' AS type, ref_cons.owner, ref_cons.table_name, ref_cons.column_name, ref_cons.constraint_name, ref_cons.r_owner, pk_cons.table_name AS r_table_name, pk_cons.column_name AS r_column_name, pk_cons.constraint_name AS r_constraint_name FROM pk_cons, ref_cons WHERE pk_cons.owner in (ref_cons.owner, ref_cons.r_owner) AND ref_cons.r_constraint_name = pk_cons.constraint_name AND ref_cons.position = pk_cons.position ORDER BY ref_cons.owner, ref_cons.table_name, ref_cons.constraint_name, ref_cons.position; ``` Can you try that SQL in the editor and let me know how long it takes to run? Does it help to add this in each of the "WITH" subqueries? Seems like this ought to be there but for me it did not make a noticeable difference. ``` and c.table_name = cc.table_name ``` ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [March 23, 2022, 10:47pm UTC](https://forums.toadworld.com/t/loading-constraints/54942/3 "2022-03-23T22:47:45Z")
Is this faster? It seems to have a slightly better explain plan for me. ```auto with cons as (select c.owner, c.constraint_name, c.constraint_type, c.table_name, c.r_owner, c.r_constraint_name, cc.column_name, cc.position from dba_constraints c, dba_cons_columns cc where (Not ((c.constraint_name like 'BIN$%==$0') and length(c.constraint_name) = 30)) and ( c.constraint_type = 'R' and ((c.owner = sys_context('USERENV', 'CURRENT_SCHEMA')) or (c.r_owner = sys_context('USERENV', 'CURRENT_SCHEMA'))) and c.owner = cc.owner and c.constraint_name = cc.constraint_name ) or ( c.constraint_type in ('P', 'U') and c.owner = sys_context('USERENV', 'CURRENT_SCHEMA') and c.owner = cc.owner and c.constraint_name = cc.constraint_name)) select 'REF_INTEGRITY' AS type, ref_cons.owner, ref_cons.table_name, ref_cons.column_name, ref_cons.constraint_name, pk_cons.owner as r_owner, pk_cons.table_name AS r_table_name, pk_cons.column_name AS r_column_name, pk_cons.constraint_name AS r_constraint_name FROM cons ref_cons, cons pk_cons WHERE ref_cons.constraint_type = 'R' and pk_cons.constraint_type in ('P', 'U') and pk_cons.owner in (ref_cons.r_owner, ref_cons.owner) AND ref_cons.r_constraint_name = pk_cons.constraint_name AND ref_cons.position = pk_cons.position ORDER BY ref_cons.owner, ref_cons.table_name, ref_cons.constraint_name, ref_cons.position; ``` ---
**Author:** ![marc\_109](https://avatars.discourse-cdn.com/v4/letter/m/74df32/32.png) [@marc\_109](https://forums.toadworld.com/u/marc_109)\ **Post date:** [March 24, 2022, 11:56am UTC](https://forums.toadworld.com/t/loading-constraints/54942/4 "2022-03-24T11:56:06Z")
I attached both explains, 1st qry "with\_ref\_const", 2nd "with\_cons", with\_ref\_const took 7 seconds with\_cons took 1 second Is this the same qry that is used in the popup. The popup freeze was a non dba account. I changed both qrys to use the non dba schema(sys\_context('USERENV', 'CURRENT\_SCHEMA')) and the timing was the same, around 7 and 1 seconds [with\_cons.txt](https://forums.toadworld.com/uploads/short-url/bmZEPQnvn3AfuQlGySp9DkeiNqA.txt) (14.8 KB) [WITH\_ ref\_cons.txt](https://forums.toadworld.com/uploads/short-url/lv90nvl2c5xffSuNFOdoABFJHX2.txt) (52.1 KB) ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [March 24, 2022, 12:43pm UTC](https://forums.toadworld.com/t/loading-constraints/54942/5 "2022-03-24T12:43:52Z")
Thanks Marc. the ref\_cons version is what's in the popup now. The cons version is a rewrite that I did yesterday and may change the query to if it continues to test well. -John ---
**Author:** ![marc\_109](https://avatars.discourse-cdn.com/v4/letter/m/74df32/32.png) [@marc\_109](https://forums.toadworld.com/u/marc_109)\ **Post date:** [March 24, 2022, 12:57pm UTC](https://forums.toadworld.com/t/loading-constraints/54942/6 "2022-03-24T12:57:26Z")
Happy to run more queries. Does this fix the freeze? The query finished, the popup some times freeze toad. Also, not sure if it really is a freeze or not. Yesterday the popup was really frozen. Could not get it off any screen. Even showed on the desktop. But sometimes, the hour glass is going and I can eventually close it. ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [March 24, 2022, 1:08pm UTC](https://forums.toadworld.com/t/loading-constraints/54942/7 "2022-03-24T13:08:17Z")
Some users have reported a delay while the constraints query runs. If they hit ESC, it cancels just fine. Or if they let it finish, all is well (and then the constraint query is not run again since the data is cached) I haven't heard of the popup not going away before though, or Toad freezing completely even after the query is done. Maybe there is still something else going on in there. ---
**Author:** ![mstaszew](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/mstaszew/32/23_2.png) [@mstaszew](https://forums.toadworld.com/u/mstaszew)\ **Post date:** [March 24, 2022, 3:05pm UTC](https://forums.toadworld.com/t/loading-constraints/54942/8 "2022-03-24T15:05:54Z")
In another thread you mentioned using the 19c client with an 11g database. Is this still the case? If so that is a known issue and will lead to freezing. ---
**Author:** ![marc\_109](https://avatars.discourse-cdn.com/v4/letter/m/74df32/32.png) [@marc\_109](https://forums.toadworld.com/u/marc_109)\ **Post date:** [March 24, 2022, 3:59pm UTC](https://forums.toadworld.com/t/loading-constraints/54942/9 "2022-03-24T15:59:44Z")
i stopped using my 19c client, only 11g