Cardinality Decay

There was a forums question on Cardinality decay when a desired  value is beyond the MAX value in a column.

Here is a quick demo in 11.2.0.1:

SQL> drop table test_cardinality purge;

Table dropped.

SQL> create table test_cardinality as select mod(rownum,100) as ID, dbms_random.string('X',25) as col_2
  2  from dual  connect by level < 10000;

Table created.

SQL> exec dbms_stats.gather_table_stats('HEMANT','TEST_CARDINALITY',method_opt=>'FOR ALL  COLUMNS SIZE 1',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> select min(id), max(id), count(distinct(id)) from test_cardinality;

   MIN(ID)    MAX(ID) COUNT(DISTINCT(ID))
---------- ---------- -------------------
         0         99                 100

SQL> 
SQL> explain plan for select * from test_cardinality where id=50;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1710481294

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   100 |  2900 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_CARDINALITY |   100 |  2900 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("ID"=50)

13 rows selected.

SQL> 

SQL> explain plan for select * from test_cardinality where id=100;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1710481294

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |    99 |  2871 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_CARDINALITY |    99 |  2871 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("ID"=100)

13 rows selected.

SQL> 

SQL> explain plan for select * from test_cardinality where id=125;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1710481294

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |    74 |  2146 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_CARDINALITY |    74 |  2146 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("ID"=125)

13 rows selected.

SQL> 
SQL> explain plan for select * from test_cardinality where id=200;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1710481294

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |     1 |    29 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_CARDINALITY |     1 |    29 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("ID"=200)

13 rows selected.

SQL> 
SQL> explain plan for select * from test_cardinality where id=150;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1710481294

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |    48 |  1392 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_CARDINALITY |    48 |  1392 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("ID"=150)

13 rows selected.

SQL> 

You can see that for ID values beyond 100, the expected cardinality does "decay". For the target value of 125, the expected cardinality is 74. For the target value of 150, the expect cardinality is 48. For the target value of 200, the expected cardinality is 1.
.
.
.