Skip to Main Content

Oracle Database Discussions

better cardinality for predicate having is null

spur230Nov 2 2015 — edited Nov 5 2015

I  am using Oracle 11.2.0.3.   I  have a query similar to the one given below. It's estimated cardinality is  3 times off from actual.  I tried to create extended statistics but it is not helping. 

Can't extended statistics be used  on columns  handling is null?

Is there any way to improve cardinality for this cases.

I have created random data in tmp.

col1 can have values  1 and 2.

col 2 can have values 1 and 2.

col3 is date and it is null mostly when  col1=1 and col2=1

I want to get good estimate for query (select * from tmp where col1=1 and col2 =1 and col3 is null)

drop table tmp;

create table tmp ( col1 number, col2 number, col3 date);

insert  into tmp

select 1 ,1 ,sysdate from dual

union all

select 1, 2, sysdate  from dual

union all

select 1 ,1 ,NUll  from dual

union all

select 1, 1, NULL  from dual

union all

select 1, 1, sysdate  from dual

union all

select 2, 2, sysdate  from dual

union all

select 1, 1, NULL  from dual

exec DBMS_STATS.GATHER_TABLE_STATS( user, 'TMP' , method_opt => 'FOR ALL COLUMNS ');

select  count(*) from tmp where col1=1 and col2 =1 and col3 is null ;

-- gives 3 estimate is only 1

Plan hash value: 3231217655

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

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

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

|   0 | SELECT STATEMENT   |      |        |       |     4 (100)|          |

|   1 |  SORT AGGREGATE    |      |      1 |    11 |            |          |

|*  2 |   TABLE ACCESS FULL| TMP  |      1 |    11 |     4   (0)| 00:00:01 |

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

select dbms_stats.CREATE_EXTENDED_STATS ( user, 'TMP','(col1,col2,col3)') from dual;

exec DBMS_STATS.GATHER_TABLE_STATS(user, 'TMP', method_opt => 'for columns (col1,col2,col3) ' , degree=> 16 , estimate_percent => null);

select  count(*) from tmp where col1=1 and col2 =1 and col3 is null;

-- gives 3 estimate is only 1

This post has been answered by Jonathan Lewis on Nov 5 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 3 2015
Added on Nov 2 2015
9 comments
3,533 views