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.
Showing posts with label Constraints for "Better plans". Show all posts
Showing posts with label Constraints for "Better plans". Show all posts
Thursday, January 6, 2011
Subscribe to:
Posts (Atom)