Database Reference
In-Depth Information
Let's take a look at a couple of examples based on the selectivity.sql script. In the following query, the
selectivity of the operation accessing the table is 1. That's because no WHERE clause is applied, and therefore, the query
returns all rows stored in the table. The cardinality, which is equal to the number of rows stored in the table, is 10,000:
SQL> SELECT * FROM t;
...
10000 rows selected.
In the following query, the cardinality of the operation accessing the table is 2,601, and therefore the selectivity is
0.2601 (2,601 rows returned out of 10,000):
SQL> SELECT * FROM t WHERE n1 BETWEEN 6000 AND 7000;
...
2601 rows selected.
In the following query, the cardinality of the operation accessing the table is 0, and therefore the selectivity is also
0 (0 rows returned out of 10,000):
SQL> SELECT * FROM t WHERE n1 = 19;
no rows selected.
In the previous three examples, the selectivity related to the operation accessing the table is computed by
dividing the cardinality of the query by the number of rows stored in the table. This is possible because the three
queries don't contain joins or operations leading to aggregations. As soon as a query contains a GROUP BY clause or
aggregate functions in the SELECT clause, the execution plan contains at least one aggregate operation. The following
query illustrates this (note the presence of the sum aggregate function):
SQL> SELECT sum(n2) FROM t WHERE n1 BETWEEN 6000 AND 7000;
SUM(N2)
----------
70846
1 row selected.
In this type of situation, it's not possible to compute the selectivity of the access operation based on the
cardinality of the query (in this case, 1). Instead, a query like the following should be executed to find out how many
rows are returned by the access operation and passed as input to the aggregate operation. Here, the cardinality of the
access operation accessing the table is 2,601, and therefore the selectivity is 0.2601 (2,601/10,000):
SQL> SELECT count(*) FROM t WHERE n1 BETWEEN 6000 AND 7000;
COUNT(*)
----------
2601
1 row selected.
 
Search WWH ::




Custom Search