BINDs and Selectivity Calculations

One of the issues with doing an explain plan vs an execution plan is that if at explain time you use a bind the optimizer will "assume" a selectivity for the bind. If it's an equality operator it will use the density value from the stats, which is 1/NDV (NDV - Number of district values).

(Note: These test runs on a 11.1.0.7 database, but I have seen the same values on a 10.2.0.1 database as well.)

Given this SQL:
select /* RVD */ count(*) from big_tab where object_type = :obj_typ1
/

An explain plan shows:

INDEX RANGE SCAN on BIG_OBJTYPE_IDX with estimated rows of 59464

The table has 2,200,160 rows in it, the desity is 1/37 = .02702702702703

Doing the arithmetic: 2200160*.02702702702703 = 59463.78378379 so it sure looks like that is the calculation going on.

Interesting note is that when there is a histogram on the column, the stored density in the stats shows a .00000, not the .02070207 number. The optimizer must just do the calculation during the parse of the statement.

OK, how about when we use other operators? What selectivity does it use for them?

With <, <=, >,>=, LIKE, and BETWEEN I got this:

INDEX RANGE SCAN on BIG_OBJTYPE_IDX with estimated rows of 110K

Doing a bit of arithmetic, this is 5% (2200160*.05=110008).

This seems to prove that with a BIND it will go with 5% selectivity, except for the equality operator where it will use the 1/NDV calculation.