Databases Reference
In-Depth Information
In the following screenshot, observe the output of these operations, using the
DBMS_STATS
package and the
CREATE_STAT_TABLE
and
EXPORT_SCHEMA_STATS
procedures:
We are now ready to test how statistics affect the execution plan of the queries. Let's create a
baseline, executing a simple query on the
CUSTOMERS
table, as in step 6. The execution plan
of this query is as follows:
We can see that, to answer our query, the optimizer choice is to use the
CUSTOMERS_YOB_BIX
bitmap index to access the required data.
In step 7, we execute the
DELETE_SCHEMA_STATS
procedure of the
DBMS_STATS
package,
to delete statistics from the
SH
schema. We execute the same query again in step 8, and then
we can see the effects of working without statistics. The execution plan can change, but in
our example—thanks to dynamic sampling, a feature we will discuss in the next section—the
execution plan remains the same.
