Database Reference
In-Depth Information
If the level is set to 2, dynamic sampling is performed for both test queries because, at that level, dynamic
sampling is always used when object statistics are missing. The recursive query used to gather the statistics for both
tables is the same as the one shown earlier. The sampling percentage increases because, at that level, it's based on
64 blocks instead of 32. In addition, for the
t_idx
table, the following recursive query is executed as well. Its aim is to
scan the index instead of the table as in the previous query. This is done because a quick sampling on the table may
miss the rows in the range specified by the predicate present in the
WHERE
clause. Instead, a quick scan of the index
will certainly locate them, if they exist:
SELECT NVL(SUM(C1),0),
NVL(SUM(C2),0),
NVL(SUM(C3),0)
FROM (
SELECT 1 AS C1,
1 AS C2,
1 AS C3
FROM "CHRIS"."T_IDX" "T1"
WHERE "T1"."ID"<19
AND ROWNUM <= 2500
) SAMPLESUB
The next level of dynamic sampling is 3. Starting with that level, dynamic sampling is also used when object
statistics are available in the data dictionary. Before executing further tests, object statistics were gathered with the
following PL/SQL block:
BEGIN
dbms_stats.gather_table_stats(ownname => user,
tabname => 't_noidx',
method_opt => 'for all columns size 1');
dbms_stats.gather_table_stats(ownname => user,
tabname => 't_idx',
method_opt => 'for all columns size 1',
cascade => true);
END;
If the level is set to 3 or higher, the query optimizer performs dynamic sampling to estimate the selectivity of a
predicate by measuring the selectivity over a sample of the table rows, instead of using the statistics from the data
dictionary and possibly hard-coded values. The following two queries illustrate this:
SELECT *
FROM t_idx
WHERE id = 19
SELECT *
FROM t_idx
WHERE round(id) = 19
For the first one, the query optimizer is able to estimate the selectivity of the
id=19
predicate based on the
column statistics and histograms. Thus, no dynamic sampling is necessary. Instead, for the second one (except if
extended statistics for the
round(id)
expression are in place), the query optimizer can't infer the selectivity of the
round(id)=19
predicate. In fact, the column statistics and histograms provide information only about the
id
column
itself, not about its rounded values. The following query is the one used for the dynamic sampling. As you can see, it
