Learnt something new in oracle its all about a very common scenario, using function calls on database columns in predicates.
rajesh@ORA11GR2> create table t
2 as
3 select * from all_objects;
Table created.
Elapsed: 00:00:14.83
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
2 dbms_stats.gather_table_stats
3 (user,'T',
4 method_opt=>'for all columns size 254');
5 end;
6 /
PL/SQL procedure successfully completed.
Well we started with a table T just a copy of all_objects and gathered histograms on all columns.
rajesh@ORA11GR2> select count(*) as total_cnt,
2 count(*) * 0.01 as per_cnt,
3 count( case when created >= to_date('30-oct-2011','dd-mon-yyyy')
4 and created < to_date('30-oct-2011','dd-mon-yyyy') +1
5 then 1 end ) per_day_cnt
6 from t ;
TOTAL_CNT PER_CNT PER_DAY_CNT
---------- ---------- -----------
72595 725.95 71874
1 row selected.
now, the total num of rows in table T is 72595 and one percent of it corresponds to 725 and nearly most of the records in table T belongs to 30-OCT-2011.
When we run queries to pull data for 30-OCT-2011 a very common implementation is trunc(created) ='30-oct-2011'. We get a completely incorrect plan ( estimated card differs from actuals)
rajesh@ORA11GR2> select * from t
2 where trunc(created) = to_date('30-oct-2011','dd-mon-yyyy');
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 726 | 70422 | 292 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 726 | 70422 | 292 (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC(INTERNAL_FUNCTION("CREATED"))=TO_DATE(' 2011-10-30
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Now, when you remove function calls, you get proper cardinality
rajesh@ORA11GR2> select * from t
2 where created >= to_date('30-oct-2011','dd-mon-yyyy')
3 and created < to_date('30-oct-2011','dd-mon-yyyy') +1 ;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71738 | 6795K| 290 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 71738 | 6795K| 290 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CREATED"<TO_DATE(' 2011-10-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "CREATED">=TO_DATE(' 2011-10-30 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
So what does this mean to us, when you join with another data sets using function calls in database columns may let to sub-optimal plans. In this case the plans differs like this.
rajesh@ORA11GR2> select *
2 from big_table b, t
3 where b.id = t.object_id
4 and trunc(t.created) = to_date('30-oct-2011','dd-mon-yyyy') ;
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 908975253
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 726 | 141K| 2470 (1)| 00:00:30 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 726 | 141K| 2470 (1)| 00:00:30 |
|* 3 | TABLE ACCESS FULL | T | 726 | 70422 | 292 (2)| 00:00:04 |
|* 4 | INDEX RANGE SCAN | BIG_IDX_01 | 1 | | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 1 | 103 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TRUNC(INTERNAL_FUNCTION("T"."CREATED"))=TO_DATE(' 2011-10-30
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
4 - access("B"."ID"="T"."OBJECT_ID")
rajesh@ORA11GR2> select *
2 from big_table b, t
3 where b.id = t.object_id
4 and t.created >= to_date('30-oct-2011','dd-mon-yyyy')
5 and t.created < to_date('30-oct-2011','dd-mon-yyyy') +1 ;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2514198030
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71738 | 13M| | 48918 (1)| 00:09:48 |
|* 1 | HASH JOIN | | 71738 | 13M| 7640K| 48918 (1)| 00:09:48 |
|* 2 | TABLE ACCESS FULL| T | 71738 | 6795K| | 290 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL| BIG_TABLE | 5000K| 491M| | 21046 (1)| 00:04:13 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."ID"="T"."OBJECT_ID")
2 - filter("T"."CREATED"<TO_DATE(' 2011-10-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "T"."CREATED">=TO_DATE(' 2011-10-30 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t
2 as
3 select * from all_objects;
Table created.
Elapsed: 00:00:14.83
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
2 dbms_stats.gather_table_stats
3 (user,'T',
4 method_opt=>'for all columns size 254');
5 end;
6 /
PL/SQL procedure successfully completed.
Well we started with a table T just a copy of all_objects and gathered histograms on all columns.
rajesh@ORA11GR2> select count(*) as total_cnt,
2 count(*) * 0.01 as per_cnt,
3 count( case when created >= to_date('30-oct-2011','dd-mon-yyyy')
4 and created < to_date('30-oct-2011','dd-mon-yyyy') +1
5 then 1 end ) per_day_cnt
6 from t ;
TOTAL_CNT PER_CNT PER_DAY_CNT
---------- ---------- -----------
72595 725.95 71874
1 row selected.
now, the total num of rows in table T is 72595 and one percent of it corresponds to 725 and nearly most of the records in table T belongs to 30-OCT-2011.
When we run queries to pull data for 30-OCT-2011 a very common implementation is trunc(created) ='30-oct-2011'. We get a completely incorrect plan ( estimated card differs from actuals)
rajesh@ORA11GR2> select * from t
2 where trunc(created) = to_date('30-oct-2011','dd-mon-yyyy');
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 726 | 70422 | 292 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 726 | 70422 | 292 (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC(INTERNAL_FUNCTION("CREATED"))=TO_DATE(' 2011-10-30
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Now, when you remove function calls, you get proper cardinality
rajesh@ORA11GR2> select * from t
2 where created >= to_date('30-oct-2011','dd-mon-yyyy')
3 and created < to_date('30-oct-2011','dd-mon-yyyy') +1 ;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71738 | 6795K| 290 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 71738 | 6795K| 290 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CREATED"<TO_DATE(' 2011-10-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "CREATED">=TO_DATE(' 2011-10-30 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
So what does this mean to us, when you join with another data sets using function calls in database columns may let to sub-optimal plans. In this case the plans differs like this.
rajesh@ORA11GR2> select *
2 from big_table b, t
3 where b.id = t.object_id
4 and trunc(t.created) = to_date('30-oct-2011','dd-mon-yyyy') ;
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 908975253
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 726 | 141K| 2470 (1)| 00:00:30 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 726 | 141K| 2470 (1)| 00:00:30 |
|* 3 | TABLE ACCESS FULL | T | 726 | 70422 | 292 (2)| 00:00:04 |
|* 4 | INDEX RANGE SCAN | BIG_IDX_01 | 1 | | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 1 | 103 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TRUNC(INTERNAL_FUNCTION("T"."CREATED"))=TO_DATE(' 2011-10-30
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
4 - access("B"."ID"="T"."OBJECT_ID")
rajesh@ORA11GR2> select *
2 from big_table b, t
3 where b.id = t.object_id
4 and t.created >= to_date('30-oct-2011','dd-mon-yyyy')
5 and t.created < to_date('30-oct-2011','dd-mon-yyyy') +1 ;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2514198030
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71738 | 13M| | 48918 (1)| 00:09:48 |
|* 1 | HASH JOIN | | 71738 | 13M| 7640K| 48918 (1)| 00:09:48 |
|* 2 | TABLE ACCESS FULL| T | 71738 | 6795K| | 290 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL| BIG_TABLE | 5000K| 491M| | 21046 (1)| 00:04:13 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."ID"="T"."OBJECT_ID")
2 - filter("T"."CREATED"<TO_DATE(' 2011-10-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "T"."CREATED">=TO_DATE(' 2011-10-30 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
rajesh@ORA11GR2>
