One case in which CBO will make wrong estimation based on accurate and fresh statistics

The DB version is 11.1.

 

Sometimes we design tables like this

 

OPT_CAL_MASTR_DIM(CAL_MASTR_SKID NUMBER not null primary key,

                                MTH_SKID           number not null

 

                              );

In this table, CAL_MASTR_SKID is a subset of MTH_SKID, which is very similar to employee_id and manager_id design.

 

And in reality, we usually need to issue SQL looks like; 

 

 

select 

*from OPT_CAL_MASTR_DIM WHERE CAL_MASTR_SKID = MTH_SKID

 

 

 

When we look at its execution plan, we will get something interesting.

 

3 ---------------------------------------------------------------------------------------
4 | Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
5 ---------------------------------------------------------------------------------------
6 |   0 | SELECT STATEMENT  |                   |     1 |  1066 |   748   (3)| 00:00:03 |
7 |*  1 |  TABLE ACCESS FULL| OPT_CAL_MASTR_DIM |     1 |  1066 |   748   (3)| 00:00:03 |
8 ---------------------------------------------------------------------------------------

 

 

But actually,

select

count(*) from OPT_CAL_MASTR_DIM CAL WHERE CAL_MASTR_SKID = MTH_SKID

 

returns 1795 rows.

 

 

I will check whether this still exists in future release.

 The reason I guess is CAL_MASTR_SKID is primary key and CBO think it must return one row based on the assumption that 'PK = one_value'.