The constraint also helps speed up the query.

As we know the constraint is an object to keep data integrity in oracle database.

But it is also help accelerate the query.So we should not disable the contraint simply just because our data is cleansed in our database warehouse. Let us look the following test on this. 

 

$ sqlplus / 

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Nov 28 23:51:53 2010 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved. 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP

and Data Mining options

 

SQL> create table t as select * from all_objects;

 

Table created.

 

SQL> create index t_idx on t(object_type);

 

Index created.

 

SQL> exec dbms_stats.gather_table_stats(user,'T');

 

PL/SQL procedure successfully completed.

 

SQL> select count(*) from t;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1842905362

 

-------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |   233   (3)| 00:00:02 |

|   1 |  SORT AGGREGATE    |      |     1 |            |          |

|   2 |   TABLE ACCESS FULL| T    | 52862 |   233   (3)| 00:00:02 |

-------------------------------------------------------------------

 

SQL> alter table t modify object_type not null;

 

Table altered.

 

SQL> select count(*) from t;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1799443504

 

-----------------------------------------------------------------------

| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |

-----------------------------------------------------------------------

|   0 | SELECT STATEMENT      |       |     1 |    49   (7)| 00:00:01 |

|   1 |  SORT AGGREGATE       |       |     1 |            |          |

|   2 |   INDEX FAST FULL SCAN| T_IDX | 52862 |    49   (7)| 00:00:01 |

-----------------------------------------------------------------------

 

SQL> alter table t modify object_type null;

 

Table altered.

 

SQL> select * from t where object_type is null;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2153619298

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    94 |   236   (4)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |    94 |   236   (4)| 00:00:02 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("OBJECT_TYPE" IS NULL)

 

SQL> drop index t_idx;

 

Index dropped.

 

SQL> create index t_idx on t(object_type,0);

 

Index created.

 

SQL> select * from t where object_type is null;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1020776977

-------------------------------------------------------------------------------------

 

| Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time   |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |     1 |    94 |     1   (0)| 00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| T   |   1 |    94 |     1   (0)| 00:00:01 |

 

|*  2 |   INDEX RANGE SCAN      | T_IDX |     1 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_TYPE" IS NULL)