Constraints are something that you will want to add to your schema as often as possible. Besides the obvious you " Get better data ", you get things like " Better plan and Better performance ". Consider
rajesh@10GR2> create table t1(num number,dt date);
Table created.
Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> insert into t1
2 select rownum,sysdate
3 from all_objects;
56359 rows created.
Elapsed: 00:00:01.53
rajesh@10GR2>
rajesh@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.03
rajesh@10GR2> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.35
rajesh@10GR2>
rajesh@10GR2> create table t2(num number,dt date);
Table created.
Elapsed: 00:00:00.06
rajesh@10GR2>
rajesh@10GR2> insert into t2
2 select -num,dt
3 from t1;
56359 rows created.
Elapsed: 00:00:00.07
rajesh@10GR2>
rajesh@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.07
rajesh@10GR2> exec dbms_stats.gather_table_stats(user,'T2');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.14
rajesh@10GR2>
Now, All I have done is created two tables T1 with non-negative numbers on column "num" and Table T2 with full of negative numbers and gathered statistics on T1 and T2.
rajesh@10GR2> create or replace view vw
2 as
3 select * from t1
4 union all
5 select * from t2;
View created.
Elapsed: 00:00:00.09
rajesh@10GR2>
Now the View "vw" pull data from each tables.
rajesh@10GR2> set autotrace traceonly explain;
rajesh@10GR2>
rajesh@10GR2> select * from vw where num > 0;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3977987467
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56361 | 770K| 69 (3)| 00:00:01 |
| 1 | VIEW | VW | 56361 | 770K| 69 (3)| 00:00:01 |
| 2 | UNION-ALL PARTITION| | | | | |
|* 3 | TABLE ACCESS FULL | T1 | 56359 | 660K| 36 (3)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T2 | 1 | 13 | 36 (3)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("NUM">0)
4 - filter("NUM">0)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.12 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 379 0.07 0.09 3 683 0 56571
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 381 0.07 0.21 3 683 0 56571
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100
Rows Row Source Operation
------- ---------------------------------------------------
56571 VIEW VW (cr=683 pr=3 pw=0 time=282918 us)
56571 UNION-ALL PARTITION (cr=683 pr=3 pw=0 time=169773 us)
56571 TABLE ACCESS FULL T1 (cr=530 pr=0 pw=0 time=56625 us)
0 TABLE ACCESS FULL T2 (cr=153 pr=3 pw=0 time=21057 us)
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select * from vw where num < 0;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3977987467
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56357 | 770K| 69 (3)| 00:00:01 |
| 1 | VIEW | VW | 56357 | 770K| 69 (3)| 00:00:01 |
| 2 | UNION-ALL PARTITION| | | | | |
|* 3 | TABLE ACCESS FULL | T1 | 1 | 12 | 36 (3)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T2 | 56359 | 715K| 36 (3)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("NUM"<0)
4 - filter("NUM"<0)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 379 0.06 0.07 1 683 0 56571
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 381 0.06 0.07 1 683 0 56571
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100
Rows Row Source Operation
------- ---------------------------------------------------
56571 VIEW VW (cr=683 pr=1 pw=0 time=294096 us)
56571 UNION-ALL PARTITION (cr=683 pr=1 pw=0 time=180952 us)
0 TABLE ACCESS FULL T1 (cr=153 pr=1 pw=0 time=11191 us)
56571 TABLE ACCESS FULL T2 (cr=530 pr=0 pw=0 time=31 us)
rajesh@10GR2>
rajesh@10GR2> set autotrace off;
As you see in plan CBO pulls datas from both Tables and produces results to client session. Now lets see what happens if constraints are in place.
rajesh@10GR2>
rajesh@10GR2> alter table t1 add constraint t1_chk check(num >0);
Table altered.
Elapsed: 00:00:00.12
rajesh@10GR2> alter table t2 add constraint t2_chk check(num < 0);
Table altered.
Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain;
rajesh@10GR2>
rajesh@10GR2> select * from vw where num > 0;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2831881304
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56361 | 770K| 69 (3)| 00:00:01 |
| 1 | VIEW | VW | 56361 | 770K| 69 (3)| 00:00:01 |
| 2 | UNION-ALL PARTITION| | | | | |
|* 3 | TABLE ACCESS FULL | T1 | 56359 | 660K| 36 (3)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| T2 | 1 | 13 | 36 (3)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("NUM">0)
4 - filter(NULL IS NOT NULL)
5 - filter("NUM">0)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 379 0.10 0.06 0 530 0 56571
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 381 0.12 0.07 0 530 0 56571
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100
Rows Row Source Operation
------- ---------------------------------------------------
56571 VIEW VW (cr=530 pr=0 pw=0 time=226340 us)
56571 UNION-ALL PARTITION (cr=530 pr=0 pw=0 time=169766 us)
56571 TABLE ACCESS FULL T1 (cr=530 pr=0 pw=0 time=45 us)
0 FILTER (cr=0 pr=0 pw=0 time=3 us)
0 TABLE ACCESS FULL T2 (cr=0 pr=0 pw=0 time=0 us)
rajesh@10GR2> select * from vw where num < 0;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 4179902146
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56357 | 770K| 69 (3)| 00:00:01 |
| 1 | VIEW | VW | 56357 | 770K| 69 (3)| 00:00:01 |
| 2 | UNION-ALL PARTITION| | | | | |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL| T1 | 1 | 12 | 36 (3)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T2 | 56359 | 715K| 36 (3)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NULL IS NOT NULL)
4 - filter("NUM"<0)
5 - filter("NUM"<0)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 379 0.10 0.05 0 530 0 56571
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 381 0.10 0.06 0 530 0 56571
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100
Rows Row Source Operation
------- ---------------------------------------------------
56571 VIEW VW (cr=530 pr=0 pw=0 time=282919 us)
56571 UNION-ALL PARTITION (cr=530 pr=0 pw=0 time=169773 us)
0 FILTER (cr=0 pr=0 pw=0 time=4 us)
0 TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=0 us)
56571 TABLE ACCESS FULL T2 (cr=530 pr=0 pw=0 time=47 us)
rajesh@10GR2>
rajesh@10GR2>
See the addition of new filter? NULL IS NOT NULL, the CBO basically removed either of table (T1 or T2) from consideration.
No comments:
Post a Comment