Database Reference
In-Depth Information
When gathering statistics on a table, DBMS_STATS gathers information about the data distribution of the columns
within the table. The most basic information about the data distribution, such as the maximum and minimum values
for the column, may not be sufficient for the optimizer's needs if the data within the column is skewed. For skewed
data distributions, histograms will have to be created as part of the column statistics to describe the data distribution
of a given column. This is particularly helpful for data warehouse implementations where skewed data is normally
present, and histograms help the optimizer to generate efficient execution plans. Histograms provide improved
selectivity estimates in the presence of data skew, resulting in optimal execution plans with nonuniform data
distributions.
Apart from the statistics, the optimizer also depends on the features of the Oracle database version that is
available and the value of the version defined by the parameter OPTIMIZER_FEATURES_ENABLE . Using these parameters,
specific versions of the optimizer features could be used by the database:
SELECT NAME,
VALUE
FROM v$parameter
WHERE NAME = 'optimizer_features_enable';
NAME VALUE
--------------------------------------------- ----------------------
optimizer_features_enable 12.1.0.1
Table 7-3 illustrates the various types of join types used by the optimizer when generating access plans.
Table 7-3. Optimizer Join Types 4
Join Type
Explanation
Nested Loop Joins
For every row in the outer table, Oracle accesses all the rows in the inner table. Useful when
joining small subsets of data and there is an efficient way to access the second table, for
example, using index lookups.
Hash Joins
The smaller of the two tables is scanned and the resulting rows are used to build a hash
table on the join key in memory. The larger table is then scanned, the join column of
the resulting rows are hashed, and the values used to probe the hash table to finish the
matching rows. Useful for larger tables and if equality predicates.
Sort Merge Joins
Consists of two steps.
Both the inputs are sorted on the join key.
The sorted lists are merged together.
This is useful when the join condition between two tables is an inequality condition or one
of the tables is already ordered, e.g., index access.
Cartesian Joins
Joins every row from one data source with every row from the other data source, creating
the Cartesian Product of the two sets. Only good if tables are very small. The only choice if
there is no join condition specified in the query.
Outer Joins
Returns all rows that satisfy the join condition and also returns all of the rows from the table
without the (+) for which no rows from the other table satisfy the join condition.
4 Source: Oracle Corporation.
 
 
Search WWH ::




Custom Search