A Little Story on Selectivity Concept

Oracle’s Query Optimizer’s functions are:
1. Transforming queries
2. Estimating
3. Generating plans

The input to the query transformer is a parsed query block which can be as a complete query, nested subquery or nonmerged view. The main objective of the query transformer is to determine if it is advantageous to change the form of the query so that it enables generation of a better query plan. The transformed query is then sent to the estimator which generates three types of measures:
• Cardinality
• Cost
• And Selectivity

The end goal of the estimator is to estimate the overall cost of a given plan. If statistics are available then the estimator uses them to compute these measures. The information required by the optimizer is higly dependent to accurate statistics which are stored in the data dictionary. If no statistics are available then the optimizer uses either dynamic sampling or an internal default value, depending on the value of the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter.

Here Cardinality represents the number of rows in a row source which can be a base table, a view or the result of a join or GROUP BY operator.

And Cost represents the number of units of work or resources that are used. The query optimizer uses disk I/O, CPU usage and memory usage as units of work.

So lets come to Selectivity, this represents a fraction of rows from a row set which can be a base table, a view or the result of a join or a GROUP BY operator.

When statistics are available the estimator uses them to estimate selectivity, for an equality predicate selectivity is set to the reciprocal of the number of n distinct values of last_name, but if a histogram is available on the equality column then the estimator uses it instead of the number of distinct values. The selectivity depends on the predicate, such as last_name = ‘YILMAZ’ or a combination of predicates such as last_name = ‘YILMAZ’ AND first_name = ‘HASAN TONGUÇ’.

And here comes my little story, my first name Hasan is a very popular religious name in Muslim World, and it was given to me as of my grandfather’s name. My middle name Tonguç is a form of Turkish word Doğuş meaning birth. And my last name Yılmaz means simply who never give up.

During my last two abroad travels to USA and UK I had trouble with my name, I was told that there is a similar person they are after with my first name and last name living in Turkey, shock! But my middle name was the surviving filter in both cases, the Selectivity of my middle name was unique with together my first and last names, thanks to god I was only held for an hour in a room lonely and then left to go in both cases. Now OOW is near and I am nearly sure that I will again be hold and then will thank to my mother for resisting in giving me this Selective middle name :)

Advertisement