Database Reference
In-Depth Information
Table 8-1.
Features Provided by the
dbms_stats
Package
Feature
Database
Dictionary
Schema
Table*
Index*
Gather/Delete
•
•
•
•
•
Lock/Unlock
•
•
Copy
•
Restore
•
•
•
•
Export/Import
•
•
•
•
•
Get/Set
•
•
*For partitioned objects, limiting the processing to a single partition is possible.
What Object Statistics Are Available?
There are three types of object statistics: table statistics, column statistics, and index statistics. For each type, there
are up to three subtypes: table/index-level statistics, partition-level statistics, and subpartition-level statistics. It may
be obvious that partition and subpartition statistics exist only when an object is partitioned and subpartitioned,
respectively.
Object statistics are shown in the data dictionary views reported in Table
8-2
. Of course, for each view there are
dba
,
all
, and, in a 12.1 multitenant environment,
cdb
versions as well—for example,
dba_tab_statistics
,
all_tab_statistics
and
cdb_tab_statistics
.
Table 8-2.
Data Dictionary Views Showing Object Statistics of Relational Tables
Object
Table/Index-Level Statistics
Partition-Level Statistics
Subpartition-Level Statistics
user_tab_statistics
user_tab_statistics
user_tab_statistics
Tables
user_tab_col_statistics
user_tab_histograms
user_part_col_statistics
user_part_histograms
user_subpart_col_statistics
user_subpart_histograms
Columns
user_ind_statistics
user_ind_statistics
user_ind_statistics
Indexes
The rest of this section describes the most important object statistics available in the data dictionary. For this
purpose, I created a test table with the following SQL statements. These SQL statements, as well as all other queries in
this section, are available in the
object_statistics.sql
script:
CREATE TABLE t
AS
SELECT rownum AS id,
50+round(dbms_random.normal*4) AS val1,
100+round(ln(rownum/3.25+2)) AS val2,
100+round(ln(rownum/3.25+2)) AS val3,
dbms_random.string('p',250) AS pad
FROM dual
CONNECT BY level <= 1000
ORDER BY dbms_random.value;
