Hello Experts,
As far as I know the selectivity has only one formula which is the following without the usage of histograms.
Selectivity = 1 / NDV (number of ditinct values)
As far as I learned that the above formula only use for equality predicates, right? For ex.: SELECT ... FROM TABLE WHERE COL = 'A';
When the following will be issued, will the selectivity formula change? Because I learned a new thing called range selectivity formula in Wolfgang Breitling paper.
SELECT ... FROM TABLE WHERE COL BETWEEN 'C' AND 'L';
Also what happen if more than one predicate performed? Like WHERE col1 = 'A' and (col2 = 14 or col3 = 'Italy' );
Are the followings correct? If so, how can I use them?
selectivity(predicate1 AND predicate2) = selectivity(predicate1) * selectivity(predicate2).
selectivity(predicate1 OR predicate2) = selectivity(predicate1) + selectivity(predicate2) - selectivity(predicate1 AND predicate2)
I have just read the Fallacies of the Cost Based Optimizer - Wolfgang Breitling paper.
I mean, what are the selectivity formulas for different cases? Where can I learn clearly? I did not find this information in Oracle docs.
Regards
Charlie