| Lesson 6 | Computing Statistics |
| Objective | Describe two methods of creating statistics. |
Computing Oracle Statistics
The cost-based optimizer is the preferred method of optimization for an Oracle database. The calculation of the cost of a particular execution path is determined by a set of statistics that describe the composition of the tables and indexes in the database.
You have two choices as to when you want to gather these all-important statistics.
Compute statistics via Index
You can compute statistics when you initially create an index with the following syntax:
- Required keywords
- Unique name for the index
- The unique name of the table the index will be based on
- A list of columns whose values will make up the index
- Required keywords for computing statistics during index creation
CREATE INDEX index_name ON table_name (column_list) COMPUTE STATISTICS;
Create Index to compute statistics
Computing statistics as a part of the creation process for an index incurs very little overhead. If the
You can also compute statistics explicitly with the following syntax with the
COMPUTE STATISTICS
keywords are not a part of the
CREATE INDEXstatement, no statistics will be created until you explicitly compute them.
You can also compute statistics explicitly with the following syntax with the
ANALYZE command:
- Required keywords
- The name of the target index
- Required keywords to compute statistics
- Required keyword
- A list of the columns to compute statistics for, or the keyword ALL for all columns
ANALYZE INDEX index_name COMPUTE_STATISTICS FOR target_list;
Indexing using Oracle Database
When to compute statistics
Keep in mind that statistics are always relative. The basic relevance of statistics is to give an approximation of the size and composition of the objects in your database. These statistics are then used to calculate the cost of a particular execution path option.
If the composition of your database changes in a relatively small way, the new statistics will probably not change the decision of the cost-based optimizer. Adding 1,000 rows to a table that already contains 100,000 rows will usually not change the outcome of cost-based optimization.
However, if those 1,000 rows add 1,000 unique values to a table that currently contains only 100 unique values, the new rows could indeed affect the selection of an execution path. In general, you should compute statistics as a standard part of your maintenance procedures, or whenever an event, such as a large data load, could affect the composition of your database. In the next lesson, you will learn about domain indexing.
If the composition of your database changes in a relatively small way, the new statistics will probably not change the decision of the cost-based optimizer. Adding 1,000 rows to a table that already contains 100,000 rows will usually not change the outcome of cost-based optimization.
However, if those 1,000 rows add 1,000 unique values to a table that currently contains only 100 unique values, the new rows could indeed affect the selection of an execution path. In general, you should compute statistics as a standard part of your maintenance procedures, or whenever an event, such as a large data load, could affect the composition of your database. In the next lesson, you will learn about domain indexing.
Computing Statistics- Quiz
Click the Quiz link below to test your knowledge about index enhancements.
Computing Statistics - Quiz
Computing Statistics - Quiz
Oracle 12c Performance Tuning
