| How To Disable or Enable All Constraints in a Table [message #450571] |
Thu, 08 April 2010 01:36  |
sivaora
Messages: 119
Registered: October 2009
Location: Hyderabad
|
Senior Member |
|
|
Hi All,
I knew, how to enable or disable a constraint on a table.
>alter table <table_name> disable constraint <constraint_name>;
But if we know all the data is correct, it's better to disable all constraints to improve the performance.
We are allowed to Disable or Enable only a single constraint.
can any one give me the solution to Enable or Disable All Constraints.
Thanks in Advance.
|
|
|
|
| Re: How To Disable or Enable All Constraints in a Table [message #450573 is a reply to message #450571] |
Thu, 08 April 2010 01:42  |
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
|
Member |
|
|
Disable all constraints and triggers --
begin
BEGIN
FOR rec IN ( SELECT trigger_name FROM user_triggers WHERE TRIGGER_NAME NOT LIKE 'BIN$%' )
LOOP
EXECUTE IMMEDIATE 'alter trigger '||rec.trigger_name||' disable';
END LOOP;
END;
DBMS_OUTPUT.PUT_LINE('TRIGGERS DISABLED');
BEGIN
FOR rec IN ( SELECT constraint_name, table_name FROM user_constraints WHERE constraint_type = 'R' )
LOOP
BEGIN
EXECUTE IMMEDIATE 'alter table '||rec.table_name||' disable constraint '||rec.constraint_name;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line( 'ERROR: alter table '||rec.table_name||' DISABLE constraint '||rec.constraint_name||';' );
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Constraints DISABLED');
END;
end;
/
Enable all constraints and triggers
begin
BEGIN
FOR rec IN ( SELECT constraint_name, table_name FROM user_constraints where status = 'DISABLED' )
LOOP
BEGIN
EXECUTE IMMEDIATE 'alter table '||rec.table_name||' enable NOVALIDATE constraint '||rec.constraint_name;
dbms_output.put_line('alter table '||rec.table_name||' enable NOVALIDATE constraint '||rec.constraint_name);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERROR: alter table '||rec.table_name||' enable NOVALIDATE constraint '||rec.constraint_name||' ;' );
END;
END LOOP;
END;
DBMS_OUTPUT.PUT_LINE('CONSTRAINTS ENABLED');
BEGIN
FOR rec IN ( SELECT trigger_name FROM user_triggers WHERE TRIGGER_NAME NOT LIKE 'BIN$%' )
LOOP
EXECUTE IMMEDIATE 'alter trigger '||rec.trigger_name||' enable';
END LOOP;
END;
DBMS_OUTPUT.PUT_LINE('TRIGGERS ENABLED');
END;
/
|
|
|
|
|
|
| Re: How To Disable or Enable All Constraints in a Table [message #450578 is a reply to message #450571] |
Thu, 08 April 2010 01:59 |
John Watson
Messages: 9000
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Here's a typical example of disabling a constraint causing a problem
SQL> set autotrace on exp
SQL> select count(*) from emp;
COUNT(*)
----------
14
Execution Plan
----------------------------------------------------------
Plan hash value: 2937609675
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------
SQL> alter table emp disable constraint pk_emp;
Table altered.
SQL> select count(*) from emp;
COUNT(*)
----------
14
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
SQL>
|
|
|
|
| Re: How To Disable or Enable All Constraints in a Table [message #450579 is a reply to message #450574] |
Thu, 08 April 2010 02:04 |
sivaora
Messages: 119
Registered: October 2009
Location: Hyderabad
|
Senior Member |
|
|
Thank you Adit for your quick reply.
Yours code performing right.But is there any way to do the same thing in sql other than writing the pl/sql code.
Hi John,
I am not sure about it, i think that if we are inserting the correct data then no need to check it through constraints, and it may take time to validate.
|
|
|
|
|
|
|
|
|
|
| Re: How To Disable or Enable All Constraints in a Table [message #450595 is a reply to message #450579] |
Thu, 08 April 2010 03:41 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
There's no 'may' about it - it WILL take time to validate. The question is, when do you want to spend that time.
Disabling the constraints will make the import process run faster, assuming that you're just doing straight forward inserts.
Re-anabling the constraints afterwards is going to be time consuming, as the constraints will be checked for all the previously existing rows, as well as the rows that you just added.
|
|
|
|
|
|
| Re: How To Disable or Enable All Constraints in a Table [message #450598 is a reply to message #450597] |
Thu, 08 April 2010 03:53 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
John Watson wrote on Thu, 08 April 2010 10:49My posted example is simple proof that the constraint improves the performance of the query.
To be more precise, your example proved that (at least) in certain cases constraints can influence the behaviour of the optimizer. This does in no way proof that having constraints could or would improve the performance of straight inserts.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
