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)
分享到:
相关推荐
The authors' pragmatic approach and illustrative use of examples will help application developers come quickly up to speed with this important object-modeling method--and will serve as a ready ...
The authors' pragmatic approach and illustrative use of examples will help application developers come quickly up to speed with this important object-modeling method--and will serve as a ready ...
Analysis of the constraint relation between ground and selfadaptive mobile mechanism of a transformable wheel-track robot.pdf
Because the trees are balanced, finding any record requires about the same amount of resources, and retrieval speed is consistent because the index has the same depth throughout. Clustered and ...
1.6.3 Distributed Query Processing . . . . . . . . . . . . . . . . . . . . . . . . . . 17 1.6.4 Distributed Concurrency Control . . . . . . . . . . . . . . . . . . . . . . . 18 1.6.5 Distributed ...
ppt about principles of constraint programming.
一篇对『The Object Constraint Language』这本书内容进行介绍的文章,浅显易懂,高屋建瓴,是入门的好材料。出现在CSCI3007 Component Based Development会议上
Constraint Processing.djvu
1. Introduction to the Use of Functions in the Management of Data Peter M.D. Gray, Peter J.H. King and Alexandra Poulovassilis.... 1 Introduction to Section I: Advances in Information Modelling ...
10 Working with the Constraint Layout & Creating Multiple Layouts
formed rules (using the Object Constraint Language and precise text) and precise text. The formalization of UML is still an open problem. Many works have been done to formalize parts of the language ...
Machine Learning projects our ultimate desire to understand the essence of human intelligence onto the space of technology. As such, while it cannot be fully understood in the restricted field of ...
3.2.8 Prior and posterior value of the penalty function . . . . . . 24 3.3 Discrete form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 4 Sequential data ...
Idealization of real world objects helps to simplify and speed up the simulation. One important idealization is a rigid body – a body that never changes its shape. The majority of objects in ...
There is a network I/O constraint. B. There is a storage I/O constraint. C. There is insufficient disk space assigned to the virtual machine. D. The application is not virtualization aware.
golabal constraint for round robin tounament scheduling.pdfgolabal constraint for round robin tounament scheduling.pdfgolabal constraint for round robin tounament scheduling.pdfgolabal constraint for ...
A deletion operation will _____ if the deletion leads to the violation of a referential integrity constraint. (a) fail (b) succeed with warning (c) succeed without warning (d) crash the ...
By the end of the book, the reader will be able to understand and write constraint programs that solve complex problems. Second, it provides a systematic introduction to the ECLiPSe system through ...
使用Data.Constraint.Dict和隐式参数解决Haskell记录问题 概述 字典类型输入是我用来解决Haskell中可扩展记录问题的一种编程技术。 它利用ConstraintKinds和ImplicitParams GHC扩展使用Data.Constraint.Dict将隐式...