Database Reference
In-Depth Information
SQL statement leading to dynamic sampling is different. Because an expression is applied to the indexed column (
id
),
even with the
t_idx
table, no sampling on the index is performed in this specific case:
SELECT NVL(SUM(C1),0),
NVL(SUM(C2),0),
COUNT(DISTINCT C3)
FROM (
SELECT 1 AS C1,
CASE WHEN ROUND("T_IDX"."ID")=19 THEN 1 ELSE 0 END AS C2,
ROUND("T_IDX"."ID") AS C3
FROM "CHRIS"."T_IDX" SAMPLE BLOCK (20 , 1) SEED (1) "T_IDX"
) SAMPLESUB
If the level is set to 4 or higher, the query optimizer performs dynamic sampling also when two or more columns
of the same table are referenced in the
WHERE
clause. This is useful for improving estimations in the case of correlated
columns. The following query provides an example of this. If you look back at the SQL statements used to create the
test tables, you'll notice that the
id
and
n1
columns contain the same data:
SELECT *
FROM t_idx
WHERE id < 19 AND n1 < 19
Also in this case, the query optimizer performs dynamic sampling with a query that has the same structure as
the previous ones. Once more, the main difference is because of the
WHERE
clause of the SQL statement that causes
dynamic sampling:
SELECT NVL(SUM(C1),0),
NVL(SUM(C2),0)
FROM (
SELECT 1 AS C1,
CASE WHEN "T_IDX"."ID"<19 AND "T_IDX"."N1"<19 THEN 1 ELSE 0 END AS C2
FROM "CHRIS"."T_IDX" SAMPLE BLOCK (20 , 1) SEED (1) "T_IDX"
) SAMPLESUB
Summing up, you can see that level 1 and 2 are usually not very useful. In fact, tables and indexes should have
up-to-date object statistics. A common exception is when temporary tables—implemented either as global temporary
tables or as regular tables—containing temporary data are accessed. In fact, frequently no object statistics are
available for them. An exception to this is when, in version 12.1, you take advantage of the session-level statistics.
In any case, be aware that a session can share a cursor parsed by another session even if, at the moment it's used, the
segment associated to the temporary table contains very different sets of data. Level 3 and higher levels are useful
for improving selectivity estimations of “complex” predicates. Therefore, if the query optimizer can't make correct
estimations because of “complex” predicates, set the
optimizer_dynamic_sampling
initialization parameter to 4 or
higher values. Otherwise, leave it at the default value. In addition, as mentioned in Chapter 8, as of version 11.1 it's
possible to gather statistics on expressions and groups of columns. So in some situations, it should be possible to
avoid dynamic sampling.
optimizer_index_cost_adj
The
optimizer_index_cost_adj
initialization parameter is used to change the cost of table accesses through index
