Database Reference
In-Depth Information
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
| 2 | NESTED LOOPS | | 1 | | 75808 |
| 3 | NESTED LOOPS | | 1 | 32 | 75808 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 32 | 80016 |
|* 5 | INDEX RANGE SCAN | T1_COL1 | 1 | 32 | 80016 |
|* 6 | INDEX UNIQUE SCAN | T2_PK | 80016 | 1 | 75808 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 75808 | 1 | 75808 |
-----------------------------------------------------------------------------
5 - access("T1"."COL1"=666)
6 - access("T1"."ID"="T2"."ID")
To understand the problem, you have to carefully analyze why the query optimizer can't compute good
estimations. The cardinality is computed by multiplying the selectivity by the number of rows in the table. Therefore,
if the cardinality is wrong, the problem can have only three possible causes: a wrong selectivity, a wrong number of
rows, or a bug in the query optimizer.
In this case, our analysis should start by looking at the estimation performed for operation 5. In other words,
the estimation related to the
"T1"."COL1"=666
predicate. Because the query optimizer bases its estimation on object
statistics, let's see whether they describe the current data. With the following query, you're able to get the object
statistics for the
t1_col1
index used for operation 5. At the same time, it's possible to compute the average number of
rows per key. This is basically the value used for the query optimizer when no histogram is available:
SQL> SELECT num_rows, distinct_keys, num_rows/distinct_keys AS avg_rows_per_key
2 FROM user_indexes
3 WHERE index_name = 'T1_COL1';
NUM_ROWS DISTINCT_KEYS AVG_ROWS_PER_KEY
-------- ------------- ----------------
160000 5000 32
It's useful to notice that in this case, the average number of rows, 32, is the same as the estimated cardinality in the
previous execution plan. To check whether these object statistics are good, you have to compare them with the actual
data. So, let's execute the following query on the
t1
table. As you can see, the query not only computes the object
statistics of the previous query but also counts the number of rows for which the
col1
column is different from 666:
SQL> SELECT count(*) AS num_rows, count(DISTINCT col1) AS distinct_keys,
2 count(nullif(col1,666)) AS rows_per_key_666
3 FROM t1;
NUM_ROWS DISTINCT_KEYS ROWS_PER_KEY_666
-------- ------------- ----------------
160000 5000 79984
