Hi, According to this:
Selectivity is the value between 0 and 1, and it is the fraction of rows returned after applying a filter on the table. For example if a table has 10,000 rows and the query returns 2601 rows, the the selectivity would be 2601/10000 or .26 or 26 percent. Selectivity enables you (or optimizer for that matter) to decide which data access method is optimum in the execution plan.
, I need some clarifications: ok, that table has 10000 rows and only 2601 are returned by the query. But what if, that query block contains three joined tables, or it contains a subquery in the where clause? So in the FROM clause there are three tables, and the fourth table is part of a where clause subquery.. then how is this selectivity calculated?
Selectivity = number of rows satisfying a condition (from which table?) / total number of rows (from all the four tables?)
Same question for cardinality (cardinality = selectivity * total number of rows).
I found many articles about this, but each of them exemplifies these concepts with simple select statements, based on a single table or a single where clause condition.
Can someone give me an example of how are these measures calculated in case of a bit more complex query (on "hr" schema, or other training purpose schema), meaning subqueries in the FROM clause, or WHERE clause, and so on?
Thank you.