Database Reference
In-Depth Information
cast(round(dbms_random.value(1,100)) AS VARCHAR2(100)),
cast(dbms_random.string('p',1000) AS VARCHAR2(1000))
FROM dual
CONNECT BY level <= 1000
CREATE TABLE t_idx (id CONSTRAINT t_idx_pk PRIMARY KEY, n1, n2, pad) AS
SELECT *
FROM t_noidx
The following are the first test queries. The only difference between them is that the first one references the
t_noidx
table, and the second references the
t_idx
table:
SELECT *
FROM t_noidx t1, t_noidx t2
WHERE t1.id = t2.id AND t1.id < 19
SELECT *
FROM t_idx t1, t_idx t2
WHERE t1.id = t2.id AND t1.id < 19
If the level is set to 1, dynamic sampling is performed only for the first query because the table referenced by the
second one is indexed. The following is the recursive query used to gather the statistics for the
t_noidx
table on my
test database. Some hints have been removed and bind variables replaced with literals to make it easier to read.
Note that SQL trace was activated before executing the test queries. Then all I needed to do was inspect the generated
trace file to find out which recursive SQL statements were executed:
SELECT NVL(SUM(C1),0),
NVL(SUM(C2),0),
COUNT(DISTINCT C3),
NVL(SUM(CASE WHEN C3 IS NULL THEN 1 ELSE 0 END),0)
FROM (
SELECT 1 AS C1,
CASE WHEN "T1"."ID"<19 THEN 1 ELSE 0 END AS C2,
"T1"."ID" AS C3
FROM "CHRIS"."T_NOIDX" SAMPLE BLOCK (20 , 1) SEED (1) "T1"
) SAMPLESUB
Here are the significant points to notice:
The query optimizer counts the total number of rows, the number of rows in the range
•
specified in the
WHERE
clause (
id < 19
), and the number of distinct values and
NULL
values of
the
id
column.
The values used in the query must be known. If bind variables are used, the query optimizer
•
must be able to peek the values to perform dynamic sampling.
The
•
SAMPLE
clause is used to perform the sampling.
t_noidx
table has 155 blocks on my
database, so the sampling percentage is 20 percent (32/155).
■
Depending on the data you're working with, level 6 or 7 might be required to ensure that dynamic sampling
generates useful insights. after all, even at level 7, at most 256 blocks are sampled. Depending on the amount of data
and its distribution, sampling a small number of blocks might not correctly represent the whole content of a table.
Caution
