a@10G> create table t (x number constraint t_chk check (x >0));
a@10G> create table t1(x number check (x > 0));
a@10G> SELECT table_name, constraint_name, constraint_type, search_condition
2 FROM all_constraints
3 WHERE owner ='A'
4 AND table_name IN ('T','T1');
TABLE_NAME CONSTRAINT_NAME CONST SEARCH_CON
---------- -------------------- ----- ----------
T1 SYS_C0011225 C x > 0
T T_CHK C x >0
Now we have a Named Check constraint bounded to table T and System Generated Constraint name bounded to table T1. Now let us see the Impact of having System generated constraint name.
D:\>exp userid=a/a tables=(T,T1) file=d:\a_10g.dmp
Export: Release 10.2.0.3.0 - Production on Thu Mar 4 18:55:49 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T 0 rows exported
. . exporting table T1 0 rows exported
Export terminated successfully without warnings.
D:\>imp userid=a/a file=d:\a_10g.dmp ignore=y
Import: Release 10.2.0.3.0 - Production on Thu Mar 4 18:56:21 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing A's objects into A
. importing A's objects into A
. . importing table "T" 0 rows imported
IMP-00017: following statement failed with ORACLE error 2264:
"ALTER TABLE "T" ADD CONSTRAINT "T_CHK" CHECK (x >0) ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2264 encountered
ORA-02264: name already used by an existing constraint
. . importing table "T1" 0 rows imported
About to enable constraints...
Import terminated successfully with warnings.
Now we can see that Named Constraints failed, because Constraint T_CHK is already bounded to table 'T'. but that doesn't happen with System Generated Constraint name
a@10G> SELECT table_name, constraint_name, constraint_type, search_condition
2 FROM all_constraints
3 WHERE owner ='A'
4 AND table_name IN ('T','T1');
TABLE_NAME CONSTRAINT_NAME CONST SEARCH_CON
---------- -------------------- ----- ----------
T1 SYS_C0011225 C x > 0
T1 SYS_C0011226 C x > 0
T T_CHK C x >0
Then importing multiple times , we have redundant constraint added to T1 but not in T& Over the constraints they accumulated hundreds of check constraint on table T1.
a@10G> SELECT table_name, constraint_name, constraint_type, search_condition
2 FROM all_constraints
3 WHERE owner ='A'
4 AND table_name IN ('T','T1');
TABLE_NAME CONSTRAINT_NAME CONST SEARCH_CON
---------- -------------------- ----- ----------
T1 SYS_C0011225 C x > 0
T1 SYS_C0011226 C x > 0
T T_CHK C x >0
T1 SYS_C0011227 C x > 0
T1 SYS_C0011228 C x > 0
T1 SYS_C0011229 C x > 0
T1 SYS_C0011230 C x > 0
T1 SYS_C0011231 C x > 0
T1 SYS_C0011232 C x > 0
T1 SYS_C0011233 C x > 0
T1 SYS_C0011234 C x > 0
T1 SYS_C0011235 C x > 0
T1 SYS_C0011236 C x > 0
T1 SYS_C0011237 C x > 0
T1 SYS_C0011238 C x > 0
T1 SYS_C0011239 C x > 0
T1 SYS_C0011240 C x > 0
T1 SYS_C0011241 C x > 0
T1 SYS_C0011242 C x > 0
T1 SYS_C0011243 C x > 0
T1 SYS_C0011244 C x > 0
T1 SYS_C0011245 C x > 0
T1 SYS_C0011246 C x > 0
T1 SYS_C0011247 C x > 0
T1 SYS_C0011248 C x > 0
T1 SYS_C0011249 C x > 0
T1 SYS_C0011250 C x > 0
T1 SYS_C0011251 C x > 0
T1 SYS_C0011252 C x > 0
29 rows selected.
Now lets us check the effect of having redundant check constraints with impact to performance.
a@10G> insert into T(x)
2 select level
3 from dual
4 connect by level <= 1000000;
1000000 rows created.
Elapsed: 00:00:02.70
a@10G> insert into T1(x)
2 select level
3 from dual
4 connect by level <= 1000000;
1000000 rows created.
Elapsed: 00:00:07.13
Yet another good reason to name your constraints!
No comments:
Post a Comment