Release 2 (8.1.6)
Part Number A76992-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Designing and Tuning for Performance Release 2 (8.1.6) Part Number A76992-01 |
|
This chapter discusses SQL processing, optimization methods, and how the optimizer chooses to execute SQL statements.
This chapter contains the following sections:
The SQL processing architecture is comprised of the following main components:
Figure 4-1 illustrates the SQL processing architecture:
The parser, the optimizer, and the row source generator form the SQL Compiler. This compiles the SQL statements into a shared cursor. Associated with the shared cursor is the execution plan.
The parser performs two functions:
The optimizer is the heart of the SQL processing engine. The Oracle server provides two methods of optimization: rule-based optimizer (RBO) and cost-based optimizer (CBO).
The row source generator receives the optimal plan from the optimizer. It outputs the execution plan for the SQL statement. The execution plan is a collection of row sources structured in the form of a tree. A row source is an iterative control structure. It processes a set of rows, one row at a time, in an iterated manner. A row source produces a row set.
SQL execution is the component that operates on the execution plan associated with a SQL statement. It then produces the results of the query.
You can examine the execution plan chosen by the optimizer for a SQL statement by using the EXPLAIN PLAN statement. This causes the optimizer to choose the execution plan, and then insert data describing the plan into a database table.
Simply issue the EXPLAIN PLAN statement and then query the output table. The following output table describes the statement examined in the previous section:
ID OPERATION OPTIONS OBJECT_NAME ------------------------------------------------------------ 0 SELECT STATEMENT 1 FILTER 2 NESTED LOOPS 3 TABLE ACCESS FULL EMP 4 TABLE ACCESS BY ROWID DEPT 5 INDEX UNIQUE SCAN PK_DEPTNO 6 TABLE ACCESS FULL SALGRADE
Each box in Figure 4-2 and each row in the output table corresponds to a single step in the execution plan. For each row in the listing, the value in the ID column is the value shown in the corresponding box in Figure 4-2.
|
See Also:
For detailed information on how to use |
The optimizer determines the most efficient way to execute a SQL statement. This is an important step in the processing of any data manipulation language (DML) statement: SELECT, INSERT, UPDATE, or DELETE. There are often many different ways to execute a SQL statement; for example, by varying the order in which tables or indexes are accessed. The procedure Oracle uses to execute a statement can greatly affect how quickly the statement executes.
The optimizer considers many factors among alternative access paths. It can use either a a cost-based or a rule-based approach (see "Cost-Based Optimizer (CBO)" and "Rule-Based Optimizer (RBO)").
You can influence the optimizer's choices by setting the optimizer approach and goal, and by gathering statistics for the CBO. Sometimes, the application designer, who has more information about a particular application's data than is available to the optimizer, can choose a more effective way to execute a SQL statement. The application designer can use hints in SQL statements to specify how the statement should be executed.
|
See Also:
|
To execute a DML statement, Oracle may need to perform many steps. Each of these steps either retrieves rows of data physically from the database or prepares them in some way for the user issuing the statement. The combination of the steps Oracle uses to execute a statement is called an execution plan. An execution plan includes an access method for each table that the statement accesses and an ordering of the tables (the join order).
|
See Also:
For descriptions of the various access methods, including indexes, hash clusters, and table scans, see "Access Paths for the RBO" and "Access Paths for the CBO". |
The following SQL statement selects the name, job, salary, and department name for all employees whose salaries do not fall into a recommended salary range:
SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND NOT EXISTS(SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal);
Figure 4-2 shows a graphical representation of the execution plan for this SQL statement.
Each step of the execution plan returns a set of rows that either are used by the next step or, in the last step, are returned to the user or application issuing the SQL statement. A set of rows returned by a step is called a row source.
Figure 4-2 is a hierarchical diagram showing the flow of row sources from one step to another. The numbering of the steps reflects the order in which they are displayed in response to the EXPLAIN PLAN statement. Generally, this is not the order in which the steps are executed.
|
See Also:
|
Each step of the execution plan either retrieves rows from the database or accepts rows from one or more row sources as input:
emp and salgrade tables, respectively.
deptno value in the pk_deptno index returned by step 3. There it finds the rowids of the associated rows in the dept table.
dept table.
For more information on access paths, see "Access Paths for the RBO" and "Access Paths for the CBO". For more information on the methods by which Oracle joins row sources, see "Optimizing Joins".
See Also:
The steps of the execution plan are not performed in the order in which they are numbered. Rather, Oracle first performs the steps that appear as leaf nodes in the tree-structured graphical representation of the execution plan (steps 3, 5, and 6 in Figure 4-2). The rows returned by each step become the row sources of its parent step. Then, Oracle performs the parent steps.
For example, Oracle performs the following steps to execute the statement in Figure 4-2:
Note that Oracle performs steps 5, 4, 2, 6, and 1 once for each row returned by step 3. If a parent step requires only a single row from its child step before it can be executed, then Oracle performs the parent step (and possibly the rest of the execution plan) as soon as a single row has been returned from the child step. If the parent of that parent step also can be activated by the return of a single row, then it is executed as well.
Thus, the execution can cascade up the tree, possibly to encompass the rest of the execution plan. Oracle performs the parent step and all cascaded steps once for each row in turn retrieved by the child step. The parent steps that are triggered for each row returned by a child step include table accesses, index accesses, nested loops joins, and filters.
If a parent step requires all rows from its child step before it can be executed, then Oracle cannot perform the parent step until all rows have been returned from the child step. Such parent steps include sorts, sort-merge joins, and aggregate functions.
By default, the goal of the CBO is the best throughput; i.e., using the least amount of resources necessary to process all rows accessed by the statement.
Oracle can also optimize a statement with the goal of best response time; i.e., using the least amount of resources necessary to process the first row accessed by a SQL statement.
For parallel execution of a SQL statement, the optimizer can choose to minimize elapsed time at the expense of resource consumption. The initialization parameter OPTIMIZER_PERCENT_PARALLEL specifies how much the optimizer attempts to parallelize execution.
The execution plan produced by the optimizer can vary depending on the optimizer's goal. Optimizing for best throughput is more likely to result in a full table scan rather than an index scan, or a sort-merge join rather than a nested loops join. Optimizing for best response time, however, more likely results in an index scan or a nested loops join.
For example, suppose you have a join statement that is executable with either a nested loops operation or a sort-merge operation. The sort-merge operation may return the entire query result faster, while the nested loops operation may return the first row faster. If your goal is to improve throughput, then the optimizer is more likely to choose a sort-merge join. If your goal is to improve response time, then the optimizer is more likely to choose a nested loops join.
Choose a goal for the optimizer based on the needs of your application:
ROWNUM to limit the number of rows, optimize for best response time. Because of the semantics of ROWNUM queries, optimizing for response time provides the best results.
The optimizer's behavior when choosing an optimization approach and goal for a SQL statement is affected by the following factors:
The OPTIMIZER_MODE initialization parameter establishes the default behavior for choosing an optimization approach for the instance. It can have the following values:
If the optimizer uses the cost-based approach for a SQL statement, and if some tables accessed by the statement have no statistics, then the optimizer uses internal information (such as the number of data blocks allocated to these tables) to estimate other statistics for these tables.
Oracle stores statistics about columns, tables, clusters, indexes, and partitions in the data dictionary for the CBO. You can collect exact or estimated statistics about physical storage characteristics and data distribution in these schema objects by using the DBMS_STATS package, the ANALYZE statement, or the COMPUTE STATISTICS clause of the CREATE or ALTER INDEX statement.
To provide the optimizer with up-to-date statistics, you should collect new statistics after modifying the data or structure of schema objects in ways that could affect their statistics.
The OPTIMIZER_GOAL parameter of the ALTER SESSION statement can override the optimizer approach and goal established by the OPTIMIZER_MODE initialization parameter for an individual session.
The value of this parameter affects the optimization of SQL statements issued by stored procedures and functions called during the session, but it does not affect the optimization of recursive SQL statements that Oracle issues during the session.
The OPTIMIZER_GOAL parameter can have these values:
A FIRST_ROWS, ALL_ROWS, CHOOSE, or RULE hint in an individual SQL statement can override the effects of both the OPTIMIZER_MODE initialization parameter and the OPTIMIZER_GOAL parameter of the ALTER SESSION statement.
By default, the cost-based approach optimizes for best throughput. You can change the goal of the CBO in the following ways:
ALTER SESSION SET OPTIMIZER_MODE statement with the ALL_ROWS or FIRST_ROWS clause.
ALL_ROWS or FIRST_ROWS hint.
The following statement changes the goal of the CBO for your session to best response time:
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
In general, you should always use the cost-based approach. The rule-based approach is available for the benefit of existing applications.
The CBO determines which execution plan is most efficient by considering available access paths and by factoring in information based on statistics for the schema objects (tables or indexes) accessed by the SQL statement. The CBO also considers hints, which are optimization suggestions placed in a comment in the statement.
The CBO consists of the following steps:
The cost is an estimated value proportional to the expected resource use needed to execute the statement with a particular plan. The optimizer calculates the cost of each possible access method and join order based on the estimated computer resources, including (but not limited to) I/O and memory, that are required to execute the statement using the plan.
Serial plans with greater costs take more time to execute than those with smaller costs. When using a parallel plan, however, resource use is not directly related to elapsed time.
To maintain the effectiveness of the CBO, you must gather statistics and keep them current. Gather statistics on your objects using either of the following:
ANALYZE statement.
DBMS_STATS package.
For table columns which contain skewed data (i.e., values with large variations in number of duplicates), you must collect histograms.
The resulting statistics provide the CBO with information about data uniqueness and distribution. Using this information, the CBO is able to compute plan costs with a high degree of accuracy. This enables the CBO to choose the best execution plan based on the least cost.
The CBO consists of the following three main components:
The CBO architecture is illustrated in Figure 4-3.
The input to query transformer is a parsed query, which is represented by a set of query blocks. The query blocks are nested or interrelated to each other. The form of the query determines how the query blocks are interrelated to each other. The main objective of the query transformer is to determine if it is advantageous to change the form of the query, so that it enables generation of a better query plan. Three different query transformation techniques are employed by the query transformer: view merging, subquery unnesting, and query rewrite using materialized views. Any combination of these transformations may be applied to a given query.
Each view referenced in a query is expanded by the parser into a separate query block. The query block essentially represents the view definition, and therefore the result of a view. One option for the optimizer is to optimize the view query block separately, and generate a subplan. Then, optimize the rest of the query by using the view subplan in the generation of overall query plan. Doing so usually leads to a sub-optimal query plan, because the view is optimized separately from rest of the query.
The query transformer removes the potential sub-optimality by merging the view query block into the query block that contains the view. Most of the views are merged, with an exception of few types of views. When a view is merged, the query block representing the view is merged into the containing query block. Now, there is no need to generate a subplan, because view query block is eliminated.
For those views that are not merged, the query transformer pushes the relevant predicates from the containing query block into the view query block. Doing so improves the subplan of the non-merged view, because the pushed in predicates act either as index drivers or as filters.
Like a view, a subquery is also represented by a separate query block. Because a subquery is nested within the main query or another subquery, this constrains the plan generator in trying out different possible plans before it finds a plan with the lowest cost. For this reason, the query plan produced may not be the optimal one. The restrictions due to the nesting of subqueries can be removed by unnesting the subqueries and converting them into joins. Most of the subqueries are unnested. For those subqueries that remain as nested subqueries, separate subplans are generated. To improve the execution speed of the overall query plan, the subplans are ordered in an efficient manner.
|
See Also:
For more information on subquery unnesting, see "Use Care When Unnesting Subqueries" in Chapter 9, "Optimizing SQL Statements". |
A materialized view is like a query whose result is materialized and stored in a table. When a user query is found compatible with the query associated with a materialized view, the user query can be rewritten in terms of the materialized view. Doing so improves the execution of the user query, because most of the query result has already been precomputed. The query transformer looks for any materialized views that are compatible with the user query, and selects one or more materialized views to rewrite the user query. The use of materialized views to rewrite a query is cost-based. That is, the query is not rewritten if the plan generated without the materialized views has lower cost than the plan generated with the materialized views.
The estimator is the heart of the CBO. Its estimates three different types of measures: selectivity, cardinality, and cost. These measures are related to each other, and one is derived from another. The end goal of the estimator is to estimate the overall cost of a given plan. If statistics are available, then the estimator uses them to compute the measures. The statistics improve the degree of accuracy of the measures.
The first type of measure is the selectivity, which represents a fraction of rows from a row set. The row set can be a base table, a view, or the result of a join or a GROUP BY operator. The selectivity is tied to a query predicate, such as last_name = 'Smith', or a combination of predicates, such as last_name = 'Smith' AND job_type = 'Clerk'. A predicate acts as a filter that filters certain number of rows from a row set. Therefore, the selectivity of a predicate indicates how many rows from a row set will pass the predicate test. The selectivity lies in the value range 0.0 to 1.0. A selectivity of 0.0 means that no rows will be selected from a row set, and a selectivity of 1.0 means that all rows will be selected.
The estimator uses an internal default value for the selectivity if no statistics are available. Different internal defaults are used depending on the predicate type. For example, the internal default for an equality predicate (last_name = 'Smith') is lower than the internal default for a range predicate (last_name > 'Smith'). This is because an equality predicate is expected to usually return a smaller fraction of rows than a range predicate.
When statistics are available, the estimator estimates selectivity based on statistics. For example, for an equality predicate (last_name = 'Smith') the selectivity is set to the reciprocal of the number of distinct values of last_name, because the query selects rows that all contain one out of N distinct values. If a histogram is available on the last_name column, then the estimator uses it instead of the number of distinct values statistic. The histogram captures the distribution of different values in a column, so its use yields better selectivity estimate. Therefore, having histograms on columns that contain skewed data (i.e., values with large variations in number of duplicates) greatly helps the CBO to generate good plans.
Cardinality represents the number of rows in a row set. Here, the row set can be a base table, a view, or the result from a join or GROUP BY operator. The base cardinality is the number of rows in a base table. The base cardinality can be captured by analyzing the table. If table statistics are not available, then the estimator uses the number of extents occupied by the table to estimate the base cardinality.
The effective cardinality is the number of rows that will be selected from a base table. The effective cardinality is dependent on the predicates specified on different columns of a base table. This is because each predicate acts as a successive filter on the rows of a base table. The effective cardinality is computed as the product of base cardinality and combined selectivity of all predicates specified on a table. When there is no predicate on a table, its effective cardinality equals its base cardinality.
The join cardinality is the number of rows produced when two row sets are joined together. A join is a Cartesian product of two row sets with the join predicate applied as a filter to the result. Therefore, the join cardinality is the product of the cardinalities of two row sets, multiplied by the selectivity of the join predicate.
A distinct cardinality is the number of distinct values in a column of a row set. The distinct cardinality of a row set is based on the data in the column. For example, in a row set of 100 rows, if distinct column values are found in 20 rows, then the distinct cardinality is 20.
The group cardinality is the number of rows produced from a row set after the GROUP BY operator is applied. The effect of the GROUP BY operator is to decrease the number of rows in a row set. The group cardinality depends on the distinct cardinality of each of the grouping columns. For example, if a row set of 100 rows is grouped by colx, whose distinct cardinality is 30, then the group cardinality is 30. If the row set of 100 rows is grouped by colx and coly, and distinct cardinalities of colx and coly are 30 and 60 respectively, then the group cardinality lies between max(30,60) and 100.
The cost represents units of work or resource used. The CBO uses disk I/O as a unit of work. The other possible work units are cpu and network usage. So, the cost used by the CBO represents an estimate of the number of disk I/Os incurred in performing an operation. The operation can be scanning a table, accessing rows from a table using an index, joining two tables together, or sorting a row set. The cost of a query plan is the number of disk I/Os that are expected to be incurred when the query is executed and its result produced.
The access cost represents the number of units of work done in accessing data from a base table. The access path can be a table scan, a fast full index scan, or an index scan. During table scan or fast full index scan, multiple blocks are read from the disk in a single I/O operation. Therefore, the cost of a table scan or a fast full index scan depends on the number of blocks to scan and the multiblock read count value. The cost for an index scan depends on the levels in the B-tree, the number of index leaf blocks to scan, and the number of rows to fetch using the rowid in the index keys. The cost to fetch rows using rowids depends on the index clustering factor. The higher the clustering factor, the more randomly scattered the individual rows are on the disk. So, a higher clustering factor means it costs more to fetch rows by rowid.
The join cost represents the combination of the individual access costs of the two row sets being joined. In a join, one row set is called inner, and the other is called outer. In a nested loops join, for every row in the outer row set, the inner row set is accessed to find all matching rows to join. Therefore, in a nested loops join, the inner row set is accessed as many times as the number of rows in the outer row set. The cost of nested loops join = outer access cost + (inner access cost * outer cardinality).
In sort merge join, the two row sets being joined are sorted by the join keys, if they are not already in key order. The cost of sort merge join = outer access cost + inner access cost + sort costs (if sort used).
In hash join, the inner row set is hashed into memory, and a hash table is built using the join key. Then, each row from the outer row set is hashed, and the hash table is probed to join to all matching rows. If the inner row set is very large, then only a portion of it is hashed into memory. This is called a hash partition.
Each row from the outer row set is hashed to probe matching rows in the hash partition. After this, the next portion of the inner row set is hashed into memory, followed by a probe from the outer row set. This process is repeated until all partitions of the inner row set are exhausted. The cost of hash join = (outer access cost * # of hash partitions) + inner access cost.
The main function of the plan generator is to try out different possible plans for a given query and pick the one that has the lowest cost. Many different plans are possible because of the various combination of different access paths, join methods and join orders that can be used to access and process data in different ways and produce the same result.
A join order is the order in which different join items (such as tables) are accessed and joined together. For example, in a join order of t1, t2, and t3, table t1 is accessed first. This is followed by access of t2, whose data is joined to t1 data to produce a join of t1 and t2. Finally, t3 is accessed, and its data is joined to the result of join between t1 and t2.
The plan for a query is established by first generating subplans for each of the unnested subqueries and non-merged views. Each unnested subquery or non-merged view is represented by a separate query block. The query blocks are optimized separately in a bottom-up order. That is, the innermost query block is optimized first, and a subplan is generated for it. The outermost query block, which represents the entire query, is optimized last.
The plan generator explores different plans for a query block by trying out different access paths, join methods, and join orders. The number of possible plans for a query block is proportional to the number of join items in the FROM clause. This number rises exponentially with the number of join items.
Because of this reason, the plan generator uses an internal cutoff to reduce the number of plans it tries to find the one with the lowest cost. The cutoff is based on the cost of the current best plan. If current best cost is large, then the plan generator tries harder (i.e., explores more alternate plans) to find a better plan with lower cost. If current best cost is small, then the plan generator ends the search swiftly, because further cost improvement will not be significant.
The cutoff works very well if the plan generator starts with an initial join order that produces a plan with cost close to optimal. Finding a good initial join order is a difficult problem. The plan generator uses a simple heuristic for the initial join order. It orders the join items by their effective cardinalities. The join item with the smallest effective cardinality goes first, and the join item with the largest effective cardinality goes last.
The use of any of the following features requires the use of the CBO:
SAMPLE clauses in a SELECT statement
To use the CBO for a statement, collect statistics for the tables accessed by the statement, and enable the CBO using one of the following methods:
OPTIMIZER_MODE initialization parameter is set to its default value of CHOOSE.
ALTER SESSION SET OPTIMIZER_MODE statement with the ALL_ROWS or FIRST_ROWS clause.
RULE.
The plans generated by the CBO depend upon the sizes of the tables, and potentially on the data distributions as well, if histograms are being used. When using the CBO with a small amount of data to test an application prototype, do not assume that the plan chosen for the full-size database will be the same as that chosen for the prototype.
One of the most important choices the optimizer makes when formulating an execution plan is how to retrieve data from the database. For any row in any table accessed by a SQL statement, there may be many access paths by which that row can be located and retrieved. The optimizer chooses one of them.
This section describes the basic methods by which Oracle can access data.
|
See Also:
For the a list of the access paths that are available for the RBO, as well as their ranking, see "Access Paths for the RBO". |
A full table scan retrieves rows from a table. To perform a full table scan, Oracle reads all rows in the table, examining each row to determine whether it satisfies the statement's WHERE clause. Oracle reads every data block allocated to the table sequentially, so a full table scan can be performed very efficiently using multiblock reads. Oracle reads each data block only once.
A sample table scan retrieves a random sample of data from a table. This access method is used when the statement's FROM clause includes the SAMPLE clause or the SAMPLE BLOCK clause. To perform a sample table scan when sampling by rows (the SAMPLE clause), Oracle reads a specified percentage of rows in the table and examines each of these rows to determine whether it satisfies the statement's WHERE clause. To perform a sample table scan when sampling by blocks (the SAMPLE BLOCK clause), Oracle reads a specified percentage of the table's blocks and examines each row in the sampled blocks to determine whether it satisfies the statement's WHERE clause.
Oracle does not support sample table scans when the query involves a join or a remote table. However, you can perform an equivalent operation by using a CREATE TABLE AS SELECT query to materialize a sample of an underlying table and then rewrite the original query to refer to the newly created table sample. Additional queries can be written to materialize samples for other tables. Sample table scans require the CBO.
The following statement uses a sample table scan to access 1% of the emp table, sampling by blocks:
SELECT * FROM emp SAMPLE BLOCK (1);
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT TABLE ACCESS SAMPLE EMP
A table access by rowid also retrieves rows from a table. The rowid of a row specifies the datafile and data block containing the row and the location of the row in that block. Locating a row by its rowid is the fastest way for Oracle to find a single row.
To access a table by rowid, Oracle first obtains the rowids of the selected rows, either from the statement's WHERE clause or through an index scan of one or more of the table's indexes. Oracle then locates each selected row in the table based on its rowid.
From a table stored in an indexed cluster, a cluster scan retrieves rows that have the same cluster key value. In an indexed cluster, all rows with the same cluster key value are stored in the same data blocks. To perform a cluster scan, Oracle first obtains the rowid of one of the selected rows by scanning the cluster index. Oracle then locates the rows based on this rowid.
Oracle can use a hash scan to locate rows in a hash cluster based on a hash value. In a hash cluster, all rows with the same hash value are stored in the same data blocks. To perform a hash scan, Oracle first obtains the hash value by applying a hash function to a cluster key value specified by the statement. Oracle then scans the data blocks containing rows with that hash value.
An index scan retrieves data from an index based on the value of one or more columns of the index. To perform an index scan, Oracle searches the index for the indexed column values accessed by the statement. If the statement accesses only columns of the index, then Oracle reads the indexed column values directly from the index, rather than from the table.
The index contains not only the indexed value, but also the rowids of rows in the table having that value. Therefore, if the statement accesses other columns in addition to the indexed columns, then Oracle can find the rows in the table with a table access by rowid or a cluster scan.
An index scan can be one of the following types:
|
Attention: Bitmap indexes are available only if you have purchased the Oracle8i Enterprise Edition. For more information on purchasing options, see Getting to Know Oracle8i. |
The CBO chooses an access path based on the following factors:
To choose an access path, the optimizer first determines which access paths are available by examining the conditions in the statement's WHERE clause (and its FROM clause for the SAMPLE or SAMPLE BLOCK clause). The optimizer then generates a set of possible execution plans using available access paths and estimates the cost of each plan using the statistics for the index, columns, and tables accessible to the statement. Finally, optimizer chooses the execution plan with the lowest estimated cost.
The optimizer's choice among available access paths can be overridden with hints, except when the statement's FROM clause contains SAMPLE or SAMPLE BLOCK.
To choose among available access paths, the optimizer considers the following factors:
The optimizer is more likely to choose an index scan over a full table scan for a query with good selectivity than for one with poor selectivity. Index scans are usually more efficient than full table scans for queries that access only a small percentage of a table's rows, while full table scans are usually faster for queries that access a large percentage.
To determine the selectivity of a query, the optimizer considers these sources of information:
WHERE clause.
WHERE clause.
The examples below illustrate how the optimizer uses selectivity.
DB_FILE_MULTIBLOCK_READ_COUNT: Full table scans use multiblock reads, so the cost of a full table scan depends on the number of multiblock reads required to read the entire table. This depends on the number of blocks read by a single multiblock read, which is specified by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. For this reason, the optimizer may be more likely to choose a full table scan when the value of this parameter is high.
The following query uses an equality condition in its WHERE clause to select all employees named Jackson:
SELECT * FROM emp WHERE ename = 'JACKSON';
If the ename column is a unique or primary key, then the optimizer determines that there is only one employee named Jackson, and the query returns only one row. In this case, the query is very selective, and the optimizer is most likely to access the table using a unique scan on the index that enforces the unique or primary key.
Consider again the query in the previous example. If the ename column is not a unique or primary key, then the optimizer can use these statistics to estimate the query's selectivity:
USER_TAB_COLUMNS.NUM_DISTINCT is the number of values for each column in the table.
USER_TABLES.NUM_ROWS is the number of rows in each table.
By dividing the number of rows in the emp table by the number of distinct values in the ename column, the optimizer estimates what percentage of employees have the same name. By assuming that the ename values are uniformly distributed, the optimizer uses this percentage as the estimated selectivity of the query.
The following query selects all employees with employee ID numbers less than 7500:
SELECT * FROM emp WHERE empno < 7500;
To estimate the selectivity of the query, the optimizer uses the boundary value of 7500 in the WHERE clause condition and the values of the HIGH_VALUE and LOW_VALUE statistics for the empno column, if available. These statistics can be found in the USER_TAB_COL_STATISTICS view (or the USER_TAB_COLUMNS view). The optimizer assumes that empno values are evenly distributed in the range between the lowest value and highest value. The optimizer then determines what percentage of this range is less than the value 7500 and uses this value as the estimated selectivity of the query.
The following query uses a bind variable rather than a literal value for the boundary value in the WHERE clause condition:
SELECT * FROM emp WHERE empno < :e1;
The optimizer does not know the value of the bind variable e1. Indeed, the value of e1 may be different for each execution of the query. For this reason, the optimizer cannot use the means described in the previous example to determine selectivity of this query. In this case, the optimizer heuristically guesses a small value for the selectivity. This is an internal default value. The optimizer makes this assumption whenever a bind variable is used as a boundary value in a condition with one of the operators <, >, <=, or >=.
The optimizer's treatment of bind variables can cause it to choose different execution plans for SQL statements that differ only in the use of bind variables rather than constants. In one case in which this difference may be especially apparent, the optimizer may choose different execution plans for an embedded SQL statement with a bind variable in an Oracle precompiler program and the same SQL statement with a constant in SQL*Plus.
The following query uses two bind variables as boundary values in the condition with the BETWEEN operator:
SELECT * FROM emp WHERE empno BETWEEN :low_e AND :high_e;
The optimizer decomposes the BETWEEN condition into these two conditions:
empno >= :low_e empno <= :high_e
The optimizer heuristically estimates a small selectivity (an internal default value) for indexed columns in order to favor the use of the index.
The following query uses the BETWEEN operator to select all employees with employee ID numbers between 7500 and 7800:
SELECT * FROM emp WHERE empno BETWEEN 7500 AND 7800;
To determine the selectivity of this query, the optimizer decomposes the WHERE clause condition into these two conditions:
empno >= 7500 empno <= 7800
The optimizer estimates the individual selectivity of each condition using the means described in a previous example. The optimizer then uses these selectivities (S1 and S2) and the absolute value function (ABS) in this formula to estimate the selectivity (S) of the BETWEEN condition:
S = ABS( S1 + S2 - 1 )
This section contains some, but not all, of the parameters specific to the optimizer. The following sections may be especially useful when tuning Oracle Applications.
The following parameters affect cost-based optimizer plans:
In data warehousing applications, you often need to set the following parameters:
You rarely need to change the following parameters:
The following two parameters address the optimizer's use of indexes for a wide range of statements, particularly nested-loop join statements in both OLTP and DSS applications.
To enable the CBO for Oracle Applications, you must set the following parameters:
You can set the following parameters to enable additional CBO-related features:
To verify that the initialization parameters have been set correctly, execute the following statement against the dictionary's PARAMETER view:
SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE 'optimizer%';
This returns the following typical data:
NAME VALUE ------------------------------ --------------------- optimizer_features_enable 8.1.6 optimizer_mode CHOOSE optimizer_max_permutations 80000 optimizer_index_cost_adj 100 optimizer_index_caching 0 optimizer_percent_parallel 0 optimizer_search_limit 5
The extensible optimizer is part of the CBO. It allows the authors of user-defined functions and domain indexes to control the three main components that the CBO uses to select an execution plan: statistics, selectivity, and cost evaluation.
The extensible optimizer lets you:
ANALYZE statement to invoke user-defined statistics collection and deletion functions.
For details about the extensible optimizer, see Oracle8i Data Cartridge Developer's Guide.
See Also:
You can define statistics collection functions for domain indexes, individual columns of a table, and user-defined datatypes.
Whenever a domain index is analyzed to gather statistics, Oracle calls the associated statistics collection function. Whenever a column of a table is analyzed, Oracle collects the standard statistics for that column and calls any associated statistics collection function. If a statistics collection function exists for a datatype, then Oracle calls it for each column that has that datatype in the table being analyzed.
The selectivity of a predicate in a SQL statement is used to estimate the cost of a particular access method; it is also used to determine the optimal join order. The optimizer cannot compute an accurate selectivity for predicates that contain user-defined operators, because it does not have any information about these operators.
You can define selectivity functions for predicates containing user-defined operators, stand-alone functions, package functions, or type methods. The optimizer calls the user-defined selectivity function whenever it encounters a predicate that contains the operator, function, or method in one of the following relations with a constant: <, <=, =, >=, >, or LIKE.
The optimizer cannot compute an accurate estimate of the cost of a domain index because it does not know the internal storage structure of the index. Also, the optimizer may underestimate the cost of a user-defined function that invokes PL/SQL, uses recursive SQL, accesses a BFILE, or is CPU-intensive.
You can define costs for domain indexes and user-defined stand-alone functions, package functions, and type methods. These user-defined costs can be in the form of default costs that the optimizer simply looks up or they can be full-fledged cost functions that the optimizer calls to compute the cost.
Although Oracle supports the rule-based optimizer, you should design new applications to use the cost-based optimizer. You should also use the CBO for data warehousing applications, because the CBO supports enhanced features for DSS. Many new performance features, such as partitioned tables, improved star query processing, and materialized views, are only available with the CBO.
If OPTIMIZER_MODE=CHOOSE, if statistics do not exist, and if you do not add hints to your SQL statements, then your statements use the RBO. You can use the RBO to access both relational data and object types. If OPTIMIZER_MODE=FIRST_ROWS or ALL_ROWS and no statistics exist, then the CBO uses default statistics. You should migrate your existing applications to use the cost-based approach.
You can enable the CBO on a trial basis simply by collecting statistics. You can then return to the RBO by deleting the statistics or by setting either the value of the OPTIMIZER_MODE initialization parameter or the OPTIMIZER_MODE clause of the ALTER SESSION statement to RULE. You can also use this value if you want to collect and examine statistics for your data without using the cost-based approach.
Using the RBO, the optimizer chooses an execution plan based on the access paths available and the ranks of these access paths. Oracle's ranking of the access paths is heuristic. If there is more than one way to execute a SQL statement, then the RBO always uses the operation with the lower rank. Usually, operations of lower rank execute faster than those associated with constructs of higher rank.
The access paths and their ranking are listed below:
Path 2: Single Row by Cluster Join
Path 3: Single Row by Hash Cluster Key with Unique or Primary Key
Path 4: Single Row by Unique or Primary Key
Path 10: Bounded Range Search on Indexed Columns
Path 11: Unbounded Range Search on Indexed Columns
Path 13: MAX or MIN of Indexed Column
Path 14: ORDER BY on Indexed Column
Each of the following sections describes an access path, discusses when it is available, and shows the output generated for it by the EXPLAIN PLAN statement.
This access path is available only if the statement's WHERE clause identifies the selected rows by rowid or with the CURRENT OF CURSOR embedded SQL syntax supported by the Oracle precompilers. To execute the statement, Oracle accesses the table by rowid.
SELECT * FROM emp WHERE ROWID = 'AAAA7bAA5AAAA1UAAA';
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT TABLE ACCESS BY ROWID EMP
This access path is available for statements that join tables stored in the same cluster if both of the following conditions are true:
WHERE clause contains conditions that equate each column of the cluster key in one table with the corresponding column in the other table.
WHERE clause also contains a condition that guarantees that the join returns only one row. Such a condition is likely to be an equality condition on the column(s) of a unique or primary key.
These conditions must be combined with AND operators. To execute the statement, Oracle performs a nested loops operation.
In the following statement, the emp and dept tables are clustered on the deptno column, and the empno column is the primary key of the emp table:
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno AND emp.empno = 7900;
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT NESTED LOOPS TABLE ACCESS BY ROWID EMP INDEX UNIQUE SCAN PK_EMP TABLE ACCESS CLUSTER DEPT
Pk_emp is the name of an index that enforces the primary key.
This access path is available if both of the following conditions are true:
WHERE clause uses all columns of a hash cluster key in equality conditions. For composite cluster keys, the equality conditions must be combined with AND operators.
To execute the statement, Oracle applies the cluster's hash function to the hash cluster key value specified in the statement to obtain a hash value. Oracle then uses the hash value to perform a hash scan on the table.
In the following statement, the orders and line_items tables are stored in a hash cluster, and the orderno column is both the cluster key and the primary key of the orders table:
SELECT * FROM orders WHERE orderno = 65118968;
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT TABLE ACCESS HASH ORDERS
This access path is available if the statement's WHERE clause uses all columns of a unique or primary key in equality conditions. For composite keys, the equality conditions must be combined with AND operators. To execute the statement, Oracle performs a unique scan on the index on the unique or primary key to retrieve a single rowid, and then accesses the table by that rowid.
In the following statement, the empno column is the primary key of the emp table:
SELECT * FROM emp WHERE empno = 7900;
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT TABLE ACCESS BY ROWID EMP INDEX UNIQUE SCAN PK_EMP
Pk_emp is the name of the index that enforces the primary key.
This access path is available for statements that join tables stored in the same cluster if the statement's WHERE clause contains conditions that equate each column of the cluster key in one table with the corresponding column in the other table. For a composite cluster key, the equality conditions must be combined with AND operators. To execute the statement, Oracle performs a nested loops operation.
In the following statement, the emp and dept tables are clustered on the deptno column:
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT NESTED LOOPS TABLE ACCESS FULL DEPT TABLE ACCESS CLUSTER EMP
This access path is available if the statement's WHERE clause uses all the columns of a hash cluster key in equality conditions. For a composite cluster key, the equality conditions must be combined with AND operators. To execute the statement, Oracle applies the cluster's hash function to the hash cluster key value specified in the statement to obtain a hash value. Oracle then uses this hash value to perform a hash scan on the table.
In the following statement, the orders and line_items tables are stored in a hash cluster, and the orderno column is the cluster key:
SELECT * FROM line_items WHERE orderno = 65118968;
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT TABLE ACCESS HASH LINE_ITEMS
This access path is available if the statement's WHERE clause uses all the columns of an indexed cluster key in equality conditions. For a composite cluster key, the equality conditions must be combined with AND operators.
To execute the statement, Oracle performs a unique scan on the cluster index to retrieve the rowid of one row with the specified cluster key value. Oracle then uses that rowid to access the table with a cluster scan. Because all rows with the same cluster key value are stored together, the cluster scan requires only a single rowid to find them all.
In the following statement, the emp table is stored in an indexed cluster, and the deptno column is the cluster key:
SELECT * FROM emp WHERE deptno = 10;
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT TABLE ACCESS CLUSTER EMP INDEX UNIQUE SCAN PERS_INDEX
Pers_index is the name of the cluster index.
This access path is available if the statement's WHERE clause uses all columns of a composite index in equality conditions combined with AND operators. To execute the statement, Oracle performs a range scan on the index to retrieve rowids of the selected rows, and then accesses the table by those rowids.
In the following statement, there is a composite index on the job and deptno columns:
SELECT * FROM emp WHERE job = 'CLERK' AND deptno = 30;
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT TABLE ACCESS BY ROWID EMP INDEX RANGE SCAN JOB_DEPTNO_INDEX
Job_deptno_index is the name of the composite index on the job and deptno columns.
This access path is available if the statement's WHERE clause uses the columns of one or more single-column indexes in equality conditions. For multiple single-column indexes, the conditions must be combined with AND operators.
If the WHERE clause uses the column of only one index, then Oracle executes the statement by performing a range scan on the index to retrieve the rowids of the selected rows, and then accesses the table by these rowids.
In the following statement, there is an index on the job column of the emp table:
SELECT * FROM emp WHERE job = 'ANALYST';
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT TABLE ACCESS BY ROWID EMP INDEX RANGE SCAN JOB_INDEX
Job_index is the index on emp.job.
If the WHERE clauses uses columns of many single-column indexes, then Oracle executes the statement by performing a range scan on each index to retrieve the rowids of the rows that satisfy each condition. Oracle then merges the sets of rowids to obtain a set of rowids of rows that satisfy all conditions. Oracle then accesses the table using these rowids.
Oracle can merge up to five indexes. If the WHERE clause uses columns of more than five single-column indexes, then Oracle merges five of them, accesses the table by rowid, and then tests the resulting rows to determine whether they satisfy the remaining conditions before returning them.
In the following statement, there are indexes on both the job and deptno columns of the emp table:
SELECT * FROM emp WHERE job = 'ANALYST' AND deptno = 20;
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT TABLE ACCESS BY ROWID EMP AND-EQUAL INDEX RANGE SCAN JOB_INDEX INDEX RANGE SCAN DEPTNO_INDEX
The AND-EQUAL operation merges the rowids obtained by the scans of the job_index and the deptno_index, resulting in a set of rowids of rows that satisfy the query.
This access path is available if the statement's WHERE clause contains a condition that uses either the column of a single-column index or one or more columns that make up a leading portion of a composite index:
column = expr column >[=] expr AND column <[=] expr column BETWEEN expr AND expr column LIKE 'c%'
Each of these conditions specifies a bounded range of indexed values that are accessed by the statement. The range is said to be bounded because the conditions specify both its least value and its greatest value. To execute such a statement, Oracle performs a range scan on the index, and then accesses the table by rowid.
This access path is not available if the expression expr references the indexed column.
In the following statement, there is an index on the sal column of the emp table:
SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000;
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT TABLE ACCESS BY ROWID EMP INDEX RANGE SCAN SAL_INDEX
Sal_index is the name of the index on emp.sal.
In the following statement, there is an index on the ename column of the emp table:
SELECT * FROM emp WHERE ename LIKE 'S%';
This access path is available if the statement's WHERE clause contains one of the following conditions that use either the column of a single-column index or one or more columns of a leading portion of a composite index:
WHERE column >[=] expr WHERE column <[=] expr
Each of these conditions specifies an unbounded range of index values accessed by the statement. The range is said to be unbounded, because the condition specifies either its least value or its greatest value, but not both. To execute such a statement, Oracle performs a range scan on the index, and then accesses the table by rowid.
In the following statement, there is an index on the sal column of the emp table:
SELECT * FROM emp WHERE sal > 2000;
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT TABLE ACCESS BY ROWID EMP INDEX RANGE SCAN SAL_INDEX
In the following statement, there is a composite index on the order and line columns of the line_items table:
SELECT * FROM line_items WHERE order > 65118968;
The access path is available, because the WHERE clause uses the order column, a leading portion of the index.
This access path is not available in the following statement, in which there is an index on the order and line columns:
SELECT * FROM line_items WHERE line < 4;
The access path is not available because the WHERE clause only uses the line column, which is not a leading portion of the index.
This access path is available for statements that join tables that are not stored together in a cluster if the statement's WHERE clause uses columns from each table in equality conditions. To execute such a statement, Oracle uses a sort-merge operation. Oracle can also use a nested loops operation to execute a join statement.
In the following statement, the emp and dept tables are not stored in the same cluster:
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT MERGE JOIN SORT JOIN TABLE ACCESS FULL EMP SORT JOIN TABLE ACCESS FULL DEPT
This access path is available for a SELECT statement, and all of the following conditions are true:
MAX or MIN function to select the maximum or minimum value of either the column of a single-column index or the leading column of a composite index. The index cannot be a cluster index. The argument to the MAX or MIN function can be any expression involving the column, a constant, or the addition operator (+), the concatenation operation (||), or the CONCAT function.
WHERE clause or GROUP BY clause.
To execute the query, Oracle performs a range scan of the index to find the maximum or minimum indexed value. Because only this value is selected, Oracle need not access the table after scanning the index.
In the following statement, there is an index on the sal column of the emp table:
SELECT MAX(sal) FROM emp;
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT AGGREGATE GROUP BY INDEX RANGE SCAN SAL_INDEX
This access path is available for a SELECT statement, and all of the following conditions are true:
ORDER BY clause that uses either the column of a single-column index or a leading portion of a composite index. The index cannot be a cluster index.
PRIMARY KEY or NOT NULL integrity constraint that guarantees that at least one of the indexed columns listed in the ORDER BY clause contains no nulls.
NLS_SORT parameter is set to BINARY.
To execute the query, Oracle performs a range scan of the index to retrieve the rowids of the selected rows in sorted order. Oracle then accesses the table by these rowids.
In the following statement, there is a primary key on the empno column of the emp table:
SELECT * FROM emp ORDER BY empno;
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT TABLE ACCESS BY ROWID EMP INDEX RANGE SCAN PK_EMP
Pk_emp is the name of the index that enforces the primary key. The primary key ensures that the column does not contain nulls.
This access path is available for any SQL statement, regardless of its WHERE clause conditions, except when its FROM clause contains SAMPLE or SAMPLE BLOCK.
Note that the full table scan is the lowest ranked access path on the list. This means that the RBO always chooses an access path that uses an index if one is available, even if a full table scan might execute faster.
The following conditions make index access paths unavailable:
where column1 and column2 are in the same table.
regardless of whether column is indexed.
where expr is an expression that operates on a column with an operator or function, regardless of whether the column is indexed.
NOT EXISTS subquery
ROWNUM pseudocolumn in a view
Any SQL statement that contains only these constructs and no others that make index access paths available must use full table scans.
The following statement uses a full table scan to access the emp table:
SELECT * FROM emp;
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT TABLE ACCESS FULL EMP
This section describes the types of SQL statements that can be optimized and summarizes the operations performed by the optimizer.
Oracle optimizes the following types of SQL statements:
For any SQL statement processed by Oracle, the optimizer does the following:
|
1 |
Evaluation of expressions and conditions |
The optimizer first evaluates expressions and conditions containing constants as fully as possible. (See "Evaluation of Expressions and Conditions".) |
|
2 |
Statement transformation |
For complex statements involving, for example, correlated subqueries, the optimizer may transform the original statement into an equivalent join statement. (See "Transforming and Optimizing Statements".) |
|
3 |
View merging |
For SQL statements that access a view, the optimizer often merges the query in the statement with that in the view, and then optimizes the result. (See "Optimizing Statements That Access Views".) |
|
4 |
Choice of optimizer approaches |
The optimizer chooses either a cost-based or rule-based approach and determines the goal of optimization. (See "Optimizing Joins".) |
|
5 |
Choice of access paths |
For each table accessed by the statement, the optimizer chooses one or more of the available access paths to obtain the table's data. (See "Access Paths for the CBO".) |
|
6 |
Choice of join orders |
For a join statement that joins more than two tables, the optimizer chooses which pair of tables is joined first, and then which table is joined to the result, and so on. |
|
7 |
Choice of join operations |
For any join statement, the optimizer chooses an operation to use to perform the join. |
This section discusses how the Oracle optimizer executes SQL statements that contain joins, anti-joins, and semi-joins. It also describes how the optimizer can use bitmap indexes to execute star queries, which join a fact table to multiple dimension tables.
To choose an execution plan for a join statement, the optimizer must make these interrelated decisions:
|
Access Paths |
As for simple statements, the optimizer must choose an access path to retrieve data from each table in the join statement. (see "Access Paths for the RBO" and "Access Paths for the CBO".) |
|
Join Operations |
To join each pair of row sources, Oracle must perform one of these operations:
|
|
Join Order |
To execute a statement that joins more than two tables, Oracle joins two of the tables, and then joins the resulting row source to the next table. This process is continued until all tables are joined into the result. |
To perform a nested loops join, Oracle performs the following steps:
For example, consider table A and B. Each row of B is joined back to A.
For rows 1, 2, 3, .....n-1, n in B, each row in B is joined to each row in A
For rows 1, 2, 3, ..... n-1, n in A
Total selectivity = selectivity (A) * selectivity (B)
Figure 4-4 shows the execution plan for the following statement using a nested loops join:
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;
To execute this statement, Oracle performs the following steps:
emp) with a full table scan.
emp.deptno value to perform a unique scan on the pk_dept index.
dept).
Oracle can only perform a sort-merge join for an equijoin. To perform a sort-merge join, Oracle performs the following steps:
Figure 4-5 shows the execution plan for this statement using a sort-merge join:
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;
To execute this statement, Oracle performs the following steps:
emp and dept tables.
All relevant table A rows are fetched, sorted, and placed in a sort area. The resulting data is:
Table A 1 5 8 11
All relevant table B rows are fetched, sorted, and placed in a sort area. The resulting data is:
Table B 2 4 5 7
A merge is then performed using a merge join algorithm to produce the resulting data:
Merged Data from A and B 1 2 4 5 7 8 11
Oracle can only perform a hash join for an equijoin. Hash join is not available with the RBO. You must enable hash join optimization, using the initialization parameter HASH_JOIN_ENABLED (which can be set with the ALTER SESSION statement) or the USE_HASH hint.
To perform a hash join, Oracle performs the following steps:
Figure 4-6 shows the execution plan for this statement using a hash join:
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;
To execute this statement, Oracle performs the following steps:
emp and dept tables.
The initialization parameter HASH_AREA_SIZE controls the amount of memory used for hash join operations and the initialization parameter HASH_MULTIBLOCK_IO_COUNT controls the number of blocks a hash join operation should read and write concurrently.
Consider a hash join of table A and B, where table B is the inner table. If the column value of NUM_DISTINCT data from the DBA_TAB_COLUMN dictionary table is small, then this implies that most of the rows have the same column value.
For example, the table emp has a gender column with two distinct values: male and female. It is assumed that queries on the gender column have a selectivity of one divided by two, or 50%. This means that half of the table rows are fetched. In this particular case, a hash join is most efficient.
|
See Also:
For more information, see "Verifying Column Statistics" in Chapter 8, "Gathering Statistics". |
Oracle can perform a cluster join only for an equijoin that equates the cluster key columns of two tables in the same cluster. In a cluster, rows from both tables with the same cluster key values are stored in the same blocks, so Oracle only accesses those blocks.
Figure 4-7 shows the execution plan for this statement in which the emp and dept tables are stored together in the same cluster:
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;
To execute this statement, Oracle performs the following steps:
dept) with a full table scan.
dept.deptno value to find the matching rows in the inner table (emp) with a cluster scan.
A cluster join is nothing more than a nested loops join involving two tables that are stored together in a cluster. Because each row from the dept table is stored in the same data blocks as the matching rows in the emp table, Oracle can access matching rows most efficiently.
The optimizer costs each join method and chooses the method with the least cost. If a join returns many rows, then the optimizer considers the following three factors:
The cost of a nested loops join = access cost of A + (access cost of B * number of rows from A)
The cost of a merge join = access cost of A + access cost of B + (sort cost of A + sort cost of B)
An exception is when the data is pre-sorted. In the pre-sorted case, merge join costs = access cost of A + access cost of B where ( sort cost of A + sort cost of B) = 0.
Estimated costs to perform a hash join = (access cost of A * number of hash partitions of B) + access cost of B
The following example illustrates the use of the ORDERED hint, which specifies the join order that the optimizer should use when joining tables. The ORDERED hint causes the join order to proceed in the order that the tables are listed in the FROM clause. In this example, the optimizer will start with the table jl_br_journals first, followed by jl_br_balances, followed by gl_code_combinations, etc. When using the ORDERED hint, it is important that the tables in the FROM clause are listed in the correct order, so as to prevent Cartesian joins.
SELECT /*+ ORDERED */ glcc.segment1||' '||glcc.segment2||' '||glcc.segment3||' ' ||glcc.segment4||' ' ||glcc.segment5 account, glcc.code_combination_id ccid, REPLACE(SUBSTR(glf.description,1,40),'.',' '), b.application_id, b.set_of_books_id, b.personnel_id, p.vendor_id FROM jl_br_journals j, jl_br_balances b, gl_code_combinations glcc, fnd_flex_values_vl glf, gl_periods gp, gl_sets_of_books gsb, po_vendors p WHERE j.application_id = b.application_id(+) AND j.set_of_books_id = b.set_of_books_id(+) AND j.code_combination_id = b.code_combination_id(+) AND j.personnel_id = b.personnel_id(+) AND j.period_name = b.period_name(+) AND j.code_combination_id= glcc.code_combination_id AND j.period_name = gp.period_name AND j.set_of_books_id = gsb.set_of_books_id AND gp.period_set_name = gsb.period_set_name AND glcc.segment1 || '' = '01' AND glf.flex_value_set_id||'' = :c_account_vs AND glcc.segment3 = glf.flex_value AND gp.start_date = add_months('01-SEP-98',-1) AND gp.period_set_name = gsb.period_set_name AND j.application_id = 200 AND j.set_of_books_id = 225 AND j.personnel_id = p.vendor_id GROUP BY glcc.segment1||' '||glcc.segment2||' '||glcc.segment3|| ' '||glcc.segment4||' '||glcc.segment5, glcc.code_combination_id, REPLACE(SUBSTR(glf.description,1,40),'.',' '), b.application_id, b.set_of_books_id, b.personnel_id, p.vendor_id Cost=13 SELECT STATEMENT Cost=13 SORT GROUP BY Cost=11 NESTED LOOPS Cost=10 NESTED LOOPS Cost=9 NESTED LOOPS Cost=7 NESTED LOOPS Cost=6 NESTED LOOPS Cost=3 NESTED LOOPS Cost=2 NESTED LOOPS OUTER Cost=1 TABLE ACCESS BY INDEX ROWID JL_BR_JOURNALS_ALL Cost=2 INDEX RANGE SCAN JL_BR_JOURNALS_U1: Cost=1 TABLE ACCESS FULL JL_BR_BALANCES_ALL Cost=1 TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS Cost= INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1: Cost=3 TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUES Cost=2 INDEX RANGE SCAN FND_FLEX_VALUES_N1: Cost=1 TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUES_TL Cost= INDEX UNIQUE SCAN FND_FLEX_VALUES_TL_U1: Cost=2 TABLE ACCESS BY INDEX ROWID GL_PERIODS Cost=1 INDEX RANGE SCAN GL_PERIODS_N1: Cost=1 TABLE ACCESS BY INDEX ROWID GL_SETS_OF_BOOKS Cost= INDEX UNIQUE SCAN GL_SETS_OF_BOOKS_U2: Cost=1 TABLE ACCESS BY INDEX ROWID PO_VENDORS Cost= INDEX UNIQUE SCAN PO_VENDORS_U1:
This section describes how the optimizer chooses an execution plan for a join statement:
The following considerations apply to both the cost-based and rule-based approaches:
UNIQUE and PRIMARY KEY constraints on the tables. If such a situation exists, then the optimizer places these tables first in the join order. The optimizer then optimizes the join of the remaining set of tables.
With the CBO, the optimizer generates a set of execution plans based on the possible join orders, join operations, and available access paths. The optimizer then estimates the cost of each plan and chooses the one with the lowest cost. The optimizer estimates costs in these ways:
SORT_AREA_SIZE.
DB_FILE_MULTIBLOCK_READ_COUNT.
With the CBO, the optimizer's choice of join orders can be overridden with the ORDERED hint. If the ORDERED hint specifies a join order that violates the rule for outer join, then the optimizer ignores the hint and chooses the order. You can also override the optimizer's choice of join operations with hints.
With the rule-based approach, the optimizer performs the following steps to choose an execution plan for a statement that joins R tables:
Usually, the optimizer does not consider the order in which tables appear in the FROM clause when choosing an execution plan. The optimizer makes this choice by applying the following rules in order:
FROM clause.
An anti-join returns rows from the left side of the predicate for which there is no corresponding row on the right side of the predicate. That is, it returns rows that fail to match (NOT IN) the subquery on the right side. For example, an anti-join can select a list of employees who are not in a particular set of departments:
SELECT * FROM emp WHERE deptno NOT IN (SELECT deptno FROM dept WHERE loc = 'HEADQUARTERS');
The optimizer uses a nested loops algorithm for NOT IN subqueries by default, unless the initialization parameter ALWAYS_ANTI_JOIN is set to MERGE or HASH and various required conditions are met that allow the transformation of the NOT IN subquery into a sort-merge or hash anti-join. You can place a MERGE_AJ or HASH_AJ hint in the NOT IN subquery to specify which algorithm the optimizer should use.
A semi-join returns rows that match an EXISTS subquery, without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery. For example:
SELECT * FROM dept WHERE EXISTS (SELECT * FROM emp WHERE dept.ename = emp.ename AND emp.bonus > 5000);
In this query, only one row needs to be returned from dept even though many rows in emp might match the subquery. If there is no index on the bonus column in emp, then a semi-join can be used to improve query performance.
The optimizer uses a nested loops algorithm for EXISTS subqueries by default, unless the initialization parameter ALWAYS_SEMI_JOIN is set to MERGE or HASH and various required conditions are met. You can place a MERGE_SJ or HASH_SJ hint in the EXISTS subquery to specify which algorithm the optimizer should use.
One type of data warehouse design centers around what is known as a star schema, which is characterized by one or more very large fact tables that contain the primary information in the data warehouse and a number of much smaller dimension tables (or lookup tables), each of which contains information about the entries for a particular attribute in the fact table.
A star query is a join between a fact table and a number of lookup tables. Each lookup table is joined to the fact table using a primary-key to foreign-key join, but the lookup tables are not joined to each other.
The CBO recognizes star queries and generates efficient execution plans for them. (Star queries are not recognized by the RBO.)
A typical fact table contains keys and measures. For example, a simple fact table might contain the measure Sales, and keys Time, Product, and Market. In this case there would be corresponding dimension tables for Time, Product, and Market. The Product dimension table, for example, would typically contain information about each product number that appears in the fact table.
A star join is a primary-key to foreign-key join of the dimension tables to a fact table. The fact table normally has a concatenated index on the key columns to facilitate this type of join or a separate bitmap index on each key column.
Common subexpression elimination is an optimization heuristic that identifies, removes, and collects common subexpression from disjunctive (i.e., OR) branches of a query. In most cases, it results in the reduction of the number of joins that would be performed.
Common subexpression elimination is enabled with initialization parameter OPTIMIZER_FEATURES_ENABLE or by setting the _ELIMINATE_COMMON_SUBEXPR parameter to TRUE.
A query is considered valid for common sub-expression elimination if its WHERE clause is in following form:
ORed logs.
ANDed logs.
AND or OR.)
The following query finds names of employees who work in a department located in L.A. and who make more than 40K or who are accountants.
SELECT emp.ename FROM emp E, dept D WHERE (D.deptno = E.deptno AND E.position = 'Accountant' AND D.location ='L.A.')OR E.deptno = D.deptno AND E.sal > 40000 AND D.location = 'L.A.');
The following query contains common subexpressions in its two disjunctive branches. The elimination of the common subexpressions transforms this query into the following query, thereby reducing the number of joins from two to one.
SELECT emp.ename FROM emp E, dept D WHERE (D.deptno = E.deptno AND D.location = 'L.A.')AND (E.position = 'Accountant' OR E.sal > 40000);
The following query contains common subexpression in its three disjunctive branches:
SELECT SUM (l_extendedprice* (1 - l_discount)) FROM PARTS, LINEITEM WHERE (p_partkey = l_partkeyAND p_brand = 'Brand#12' AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND l_quantity >= 1 AND l_quantity <= 1 + 10 AND p_size >= 1 AND p_size <= 5 AND l_shipmode IN ('AIR', 'REG AIR') AND l_shipinstruct = 'DELIVER IN PERSON')OR (l_partkey = p_partkey)AND p_brand = 'Brand#23' AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND l_quantity >= 10 AND l_quantity <= 10 + 10 AND p_size >= 1 AND p_size <= 10 AND p_size BETWEEN 1 AND 10 AND l_shipmode IN ('AIR', 'REG AIR') AND l_shipinstruct = 'DELIVER IN PERSON')OR (p_partkey = l_partkeyAND p_brand = 'Brand#34' AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND l_quantity >= 20 AND l_quantity <= 20 + 10 AND p_size >= 1 AND p_size <= 15 AND l_shipmode IN ('AIR', 'REG AIR') AND l_shipinstruct = 'DELIVER IN PERSON');
The above query is transformed by common subexpression elimination as the following, thereby reducing the number joins from three down to one.
SELECT SUM (l_extendedprice* (1 - l_discount)) FROM PARTS, LINEITEM WHERE (p_partkey = l_partkey /* these are the four common subexpressions */AND p_size >= 1 AND l_shipmode IN ('AIR', 'REG AIR') AND l_shipinstruct = 'DELIVER IN PERSON') AND((p_brand = 'Brand#12' AND p_container IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND l_quantity >= 1 AND l_quantity <= 1 + 10 AND p_size <= 5)OR (p_brand = 'Brand#23'AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND l_quantity >= 10 AND l_quantity <= 10 + 10 AND p_size <= 10)OR (p_brand = 'Brand#34'AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND l_quantity >= 20 AND l_quantity <= 20 + 10 AND p_size <= 15));
The optimizer fully evaluates expressions whenever possible and translates certain syntactic constructs into equivalent constructs. The reason for this is either that Oracle can more quickly evaluate the resulting expression than the original expression, or that the original expression is merely a syntactic equivalent of the resulting expression. Different SQL constructs can sometimes operate identically (for example, = ANY (subquery) and IN (subquery)); Oracle maps these to a single construct.
This section discusses how the optimizer evaluates expressions and conditions that contain the following:
Computation of constants is performed only once, when the statement is optimized, rather than each time the statement is executed.
For example, the following conditions test for monthly salaries greater than 2000:
sal > 24000/12 sal > 2000 sal*12 > 24000
If a SQL statement contains the first condition, then the optimizer simplifies it into the second condition.
The optimizer simplifies conditions that use the LIKE comparison operator to compare an expression with no wildcard characters into an equivalent condition that uses an equality operator instead. For example, the optimizer simplifies the first condition below into the second:
ename LIKE 'SMITH' ename = 'SMITH'
The optimizer can simplify these expressions only when the comparison involves variable-length datatypes. For example, if ename was of type CHAR(10), then the optimizer cannot transform the LIKE operation into an equality operation due to the equality operator following blank-padded semantics and LIKE not following blank-padded semantics.
The optimizer expands a condition that uses the IN comparison operator to an equivalent condition that uses equality comparison operators and OR logical operators. For example, the optimizer expands the first condition below into the second:
ename IN ('SMITH', 'KING', 'JONES') ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES'
The optimizer expands a condition that uses the ANY or SOME comparison operator followed by a parenthesized list of values into an equivalent condition that uses equality comparison operators and OR logical operators. For example, the optimizer expands the first condition below into the second:
sal > ANY (:first_sal, :second_sal) sal > :first_sal OR sal > :second_sal
The optimizer transforms a condition that uses the ANY or SOME operator followed by a subquery into a condition containing the EXISTS operator and a correlated subquery. For example, the optimizer transforms the first condition below into the second:
x > ANY (SELECT sal FROM emp WHERE job = 'ANALYST') EXISTS (SELECT sal FROM emp WHERE job = 'ANALYST' AND x > sal)
The optimizer expands a condition that uses the ALL comparison operator followed by a parenthesized list of values into an equivalent condition that uses equality comparison operators and AND logical operators. For example, the optimizer expands the first condition below into the second:
sal > ALL (:first_sal, :second_sal) sal > :first_sal AND sal > :second_sal
The optimizer transforms a condition that uses the ALL comparison operator followed by a subquery into an equivalent condition that uses the ANY comparison operator and a complementary comparison operator. For example, the optimizer transforms the first condition below into the second:
x > ALL (SELECT sal FROM emp WHERE deptno = 10) NOT (x <= ANY (SELECT sal FROM emp WHERE deptno = 10) )
The optimizer then transforms the second query into the following query using the rule for transforming conditions with the ANY comparison operator followed by a correlated subquery:
NOT EXISTS (SELECT sal FROM emp WHERE deptno = 10 AND x <= sal)
The optimizer always replaces a condition that uses the BETWEEN comparison operator with an equivalent condition that uses the >= and <= comparison operators. For example, the optimizer replaces the first condition below with the second:
sal BETWEEN 2000 AND 3000 sal >= 2000 AND sal <= 3000
The optimizer simplifies a condition to eliminate the NOT logical operator. The simplification involves removing the NOT logical operator and replacing a comparison operator with its opposite comparison operator. For example, the optimizer simplifies the first condition below into the second one:
NOT deptno = (SELECT deptno FROM emp WHERE ename = 'TAYLOR') deptno <> (SELECT deptno FROM emp WHERE ename = 'TAYLOR')
Often, a condition containing the NOT logical operator can be written many different ways. The optimizer attempts to transform such a condition so that the subconditions negated by NOTs are as simple as possible, even if the resulting condition contains more NOTs. For example, the optimizer simplifies the first condition below into the second, and then into the third.
NOT (sal < 1000 OR comm IS NULL) NOT sal < 1000 AND comm IS NOT NULL sal >= 1000 AND comm IS NOT NULL
If two conditions in the WHERE clause involve a common column, then the optimizer can sometimes infer a third condition using the transitivity principle. The optimizer can then use the inferred condition to optimize the statement. The inferred condition could potentially make available an index access path that was not made available by the original conditions.
Imagine a WHERE clause containing two conditions of these forms:
WHERE column1 comp_oper constant AND column1 = column2
In this case, the optimizer infers the condition:
column2 comp_oper constant
where:
|
comp_oper |
Any of the comparison operators =, !=, ^=, <, <>, >, <=, or >=. |
|
constant |
Any constant expression involving operators, SQL functions, literals, bind variables, and correlation variables. |
In the following query, the WHERE clause contains two conditions, each of which uses the emp.deptno column:
SELECT * FROM emp, dept WHERE emp.deptno = 20 AND emp.deptno = dept.deptno;
Using transitivity, the optimizer infers this condition:
dept.deptno = 20
If an index exists on the dept.deptno column, then this condition makes available access paths using that index.
In some cases, the optimizer can use a previously calculated value, rather than executing a user-written function. This is only safe for functions that behave in a restricted manner. The function must always return the same output return value for any given set of input argument values.
The function's result must not differ because of differences in the content of package variables or the database, or session parameters such as the NLS parameters. Furthermore, if the function is redefined in the future, then its output return value must still be the same as that calculated with the prior definition for any given set of input argument values. Finally, there must be no meaningful side-effects such that using a precalculated value instead of executing the function again would alter the application.
The creator of a function can promise to the Oracle server that the function behaves according to these restrictions by using the keyword DETERMINISTIC when declaring the function with a CREATE FUNCTION statement or in a CREATE PACKAGE or CREATE TYPE statement. The server does not attempt to verify this declaration--even a function that obviously manipulates the database or package variables can be declared DETERMINISTIC. It is the programmer's responsibility to use this keyword only when appropriate.
Calls to a DETERMINISTIC function may be replaced by the use of an already calculated value when the function is called multiple times within the same query, or if there is a function-based index or a materialized view defined that includes a relevant call to the function.
|
See Also:
|
SQL is a very flexible query language; there are often many statements you could use to achieve the same goal. Sometimes, the optimizer transforms one such statement into another that achieves the same goal if the second statement can be executed more efficiently.
This section discusses the following topics:
For additional information about optimizing statements that contain joins, semi-joins, or anti-joins, see "Optimizing Joins".
See Also:
If a query contains a WHERE clause with multiple conditions combined with OR operators, then the optimizer transforms it into an equivalent compound query that uses the UNION ALL set operator if this makes it execute more efficiently:
OR transformation for IN-lists or ORs on the same column; instead, it uses the INLIST iterator operator.
For information on access paths and how indexes make them available, see the "Access Paths for the RBO" section and "How the CBO Chooses an Access Path".
See Also:
In the following query, the WHERE clause contains two conditions combined with an OR operator:
SELECT * FROM emp WHERE job = 'CLERK' OR deptno = 10;
If there are indexes on both the job and deptno columns, then the optimizer may transform this query into the equivalent query below:
SELECT * FROM emp WHERE job = 'CLERK' UNION ALL SELECT * FROM emp WHERE deptno = 10 AND job <> 'CLERK';
When the CBO is deciding whether to make a transformation, the optimizer compares the cost of executing the original query using a full table scan with that of executing the resulting query.
With the RBO, the optimizer makes this UNION ALL transformation, because each component query of the resulting compound query can be executed using an index. The RBO assumes that executing the compound query using two index scans is faster than executing the original query using a full table scan.
The execution plan for the transformed statement might look like the illustration in Figure 4-8.
To execute the transformed query, Oracle performs the following steps:
job and deptno columns using the conditions of the component queries. These steps obtain rowids of the rows that satisfy the component queries.
If either of the job or deptno columns is not indexed, then the optimizer does not even consider the transformation, because the resulting compound query would require a full table scan to execute one of its component queries. Executing the compound query with a full table scan in addition to an index scan could not possibly be faster than executing the original query with a full table scan.
The following query assumes that there is an index on the ename column only:
SELECT * FROM emp WHERE ename = 'SMITH' OR sal > comm;
Transforming the query above would result in the compound query below:
SELECT * FROM emp WHERE ename = 'SMITH' UNION ALL SELECT * FROM emp WHERE sal > comm;
Because the condition in the WHERE clause of the second component query (sal > comm) does not make an index available, the compound query requires a full table scan. For this reason, the optimizer does not make the transformation, and it chooses a full table scan to execute the original statement.
To optimize a complex statement, the optimizer chooses one of the following:
The optimizer transforms a complex statement into a join statement whenever the resulting join statement is guaranteed to return exactly the same rows as the complex statement. This transformation allows Oracle to execute the statement by taking advantage of join optimizer techniques described in "Optimizing Joins".
The following complex statement selects all rows from the accounts table whose owners appear in the customers table:
SELECT * FROM accounts WHERE custno IN (SELECT custno FROM customers);
If the custno column of the customers table is a primary key or has a UNIQUE constraint, then the optimizer can transform the complex query into the following join statement that is guaranteed to return the same data:
SELECT accounts.* FROM accounts, customers WHERE accounts.custno = customers.custno;
The execution plan for this statement might look like Figure 4-9.
To execute this statement, Oracle performs a nested-loops join operation.
If the optimizer cannot transform a complex statement into a join statement, then the optimizer chooses execution plans for the parent statement and the subquery as though they were separate statements. Oracle then executes the subquery and uses the rows it returns to execute the parent query.
The following complex statement returns all rows from the accounts table that have balances greater than the average account balance:
SELECT * FROM accounts WHERE accounts.balance > (SELECT AVG(balance) FROM accounts);
No join statement can perform the function of this statement, so the optimizer does not transform the statement.
To optimize a statement that accesses a view, the optimizer chooses one of the following:
To merge the view's query into a referencing query block in the accessing statement, the optimizer replaces the name of the view with the names of its base tables in the query block and adds the condition of the view's query's WHERE clause to the accessing query block's WHERE clause.
This optimization applies to select-project-join views, which are views that contain only selections, projections, and joins--that is, views that do not contain set operators, aggregate functions, DISTINCT, GROUP BY, CONNECT BY, and so on (as described in "Mergeable and Non-mergeable Views").
The following view is of all employees who work in department 10:
CREATE VIEW emp_10 AS SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp WHERE deptno = 10;
The following query accesses the view. The query selects the IDs greater than 7800 of employees who work in department 10:
SELECT empno FROM emp_10 WHERE empno > 7800;
The optimizer transforms the query into the following query that accesses the view's base table:
SELECT empno FROM emp WHERE deptno = 10 AND empno > 7800;
If there are indexes on the deptno or empno columns, then the resulting WHERE clause makes them available.
The optimizer can merge a view into a referencing query block when the view has one or more base tables, provided the view does not contain the following:
UNION, UNION ALL, INTERSECT, MINUS)
CONNECT BY clause
ROWNUM pseudocolumn
AVG, COUNT, MAX, MIN, SUM) in the select list
When a view contains one of the following structures, it can be merged into a referencing query block only if complex view merging (described below) is enabled:
View merging is not possible for a view that has multiple base tables if it is on the right side of an outer join. However, if a view on the right side of an outer join has only one base table, then the optimizer can use complex view merging, even if an expression in the view can return a non-null value for a NULL.
If a view's query contains a GROUP BY clause or DISTINCT operator in the select list, then the optimizer can merge the view's query into the accessing statement only if complex view merging is enabled. Complex merging can also be used to merge an IN subquery into the accessing statement if the subquery is uncorrelated (see "Example 2: IN Subquery").
Complex merging is not cost-based--it must be enabled with the initialization parameter OPTIMIZER_FEATURES_ENABLE, the MERGE hint, or the parameter _COMPLEX_VIEW_MERGING. Without this hint or parameter setting, the optimizer uses another approach (see "Pushing the Predicate into the View").
The view avg_salary_view contains the average salaries for each department:
CREATE VIEW avg_salary_view AS SELECT deptno, AVG(sal) AS avg_sal_dept, FROM emp GROUP BY deptno;
If complex view merging is enabled, then the optimizer can transform the following query, which finds the average salaries of departments in London:
SELECT dept.loc, avg_sal_dept FROM dept, avg_salary_view WHERE dept.deptno = avg_salary_view.deptno AND dept.loc = 'London';
into the following query:
SELECT dept.loc, AVG(sal) FROM dept, emp WHERE dept.deptno = emp.deptno AND dept.loc = 'London' GROUP BY dept.rowid, dept.loc;
The transformed query accesses the view's base table, selecting only the rows of employees who work in London and grouping them by department.
Complex merging can be used for an IN clause with a non-correlated subquery, as well as for views. The view min_salary_view contains the minimum salaries for each department:
SELECT deptno, MIN(sal) FROM emp GROUP BY deptno;
If complex merging is enabled, then the optimizer can transform the following query, which finds all employees who earn the minimum salary for their department in London:
SELECT emp.ename, emp.sal FROM emp, dept WHERE (emp.deptno, emp.sal) IN min_salary_view AND emp.deptno = dept.deptno AND dept.loc = 'London';
into the following query (where e1 and e2 represent the emp table as it is referenced in the accessing query block and the view's query block, respectively):
SELECT e1.ename, e1.sal FROM emp e1, dept, emp e2 WHERE e1.deptno = dept.deptno AND dept.loc = 'London' AND e1.deptno = e2.deptno GROUP BY e1.rowid, dept.rowid, e1.ename, e1.sal HAVING e1.sal = MIN(e2.sal);
The optimizer can transform a query block that accesses a non-mergeable view by pushing the query block's predicates inside the view's query.
The two_emp_tables view is the union of two employee tables. The view is defined with a compound query that uses the UNION set operator:
CREATE VIEW two_emp_tables (empno, ename, job, mgr, hiredate, sal, comm, deptno) AS SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp1 UNION SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp2;
The following query accesses the view. The query selects the IDs and names of all employees in either table who work in department 20:
SELECT empno, ename FROM two_emp_tables WHERE deptno = 20;
Because the view is defined as a compound query, the optimizer cannot merge the view's query into the accessing query block. Instead, the optimizer can transform the accessing statement by pushing its predicate, the WHERE clause condition (deptno = 20), into the view's compound query.
The resulting statement looks like the following:
SELECT empno, ename FROM ( SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp1 WHERE deptno = 20 UNION SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp2 WHERE deptno = 20 );
If there is an index on the deptno column, then the resulting WHERE clauses make it available.
Figure 4-10 shows the execution plan of the resulting statement.
To execute this statement, Oracle performs the following steps:
emp1 and emp2 tables.
UNION-ALL operation returning all rows returned by either step 5 or step 6, including all copies of duplicates.
The view emp_group_by_deptno contains the department number, average salary, minimum salary, and maximum salary of all departments that have employees:
CREATE VIEW emp_group_by_deptno AS SELECT deptno, AVG(sal) avg_sal, MIN(sal) min_sal, MAX(sal) max_sal FROM emp GROUP BY deptno;
The following query selects the average, minimum, and maximum salaries of department 10 from the emp_group_by_deptno view:
SELECT * FROM emp_group_by_deptno WHERE deptno = 10;
The optimizer transforms the statement by pushing its predicate (the WHERE clause condition) into the view's query. The resulting statement looks like the following:
SELECT deptno, AVG(sal) avg_sal, MIN(sal) min_sal, MAX(sal) max_sal, FROM emp WHERE deptno = 10 GROUP BY deptno;
If there is an index on the deptno column, then the resulting WHERE clause makes it available. Figure 4-11 shows the execution plan for the resulting statement. The execution plan uses an index on the deptno column.
To execute this statement, Oracle performs the following operations:
emp_deptno_index (an index on the deptno column of the emp table) to retrieve the rowids of all rows in the emp table with a deptno value of 10.
emp table using the rowids retrieved by step 4.
sal values.
The optimizer can transform a query that contains an aggregate function (AVG, COUNT, MAX, MIN, SUM) by applying the function to the view's query.
The following query accesses the emp_group_by_deptno view defined in the previous example. This query derives the averages for the average department salary, the minimum department salary, and the maximum department salary from the employee table:
SELECT AVG(avg_sal), AVG(min_sal), AVG(max_sal) FROM emp_group_by_deptno;
The optimizer transforms this statement by applying the AVG aggregate function to the select list of the view's query:
SELECT AVG(AVG(sal)), AVG(MIN(sal)), AVG(MAX(sal)) FROM emp GROUP BY deptno;
Figure 4-12 shows the execution plan of the resulting statement.
To execute this statement, Oracle performs these operations:
emp table.
deptno values and calculates the average, minimum, and maximum sal value of each group.
For a view that is on the right side of an outer join, the optimizer can use one of two methods, depending on how many base tables the view accesses:
A view that has one base table and is on the right side of an outer join can be merged into the query block of an accessing statement. (See "Merging the View's Query into the Statement".) View merging is possible even if an expression in the view can return a non-null value for a NULL.
Consider the view name_view, which concatenates first and last names from the emp table:
CREATE VIEW name_view AS SELECT emp.firstname || emp.lastname AS emp_fullname, emp.deptno FROM emp;
and consider this outer join statement, which finds the names of all employees in London and their departments, as well as any departments that have no employees:
SELECT dept.deptno, name_view.emp_fullname FROM emp_fullname, dept WHERE dept.deptno = name_view.deptno(+) AND dept.loc = 'London';
The optimizer merges the view's query into the outer join statement. The resulting statement looks like this:
SELECT dept.deptno, DECODE(emp.rowid, NULL, NULL, emp.firstname || emp.lastname) FROM emp, dept WHERE dept.deptno = emp.deptno(+) AND dept.loc = 'London';
The transformed statement selects only the employees who work in London.
For a view with multiple base tables on the right side of an outer join, the optimizer can push the join predicate into the view (see "Pushing the Predicate into the View") if the initialization parameter _PUSH_JOIN_PREDICATE is set to TRUE or the accessing query contains the PUSH_PRED hint.
Pushing a join predicate is a cost-based transformation that can enable more efficient access path and join methods, such as transforming hash joins into nested loops joins, and full table scans to index scans.
Consider the view london_emp, which selects the employees who work in London:
CREATE VIEW london_emp AS SELECT emp.ename FROM emp, dept WHERE emp.deptno = dept.deptno AND dept.loc = 'London';
and consider this outer join statement, which finds the engineers and accountants working in London who received bonuses:
SELECT bonus.job, london_emp.ename FROM bonus, london_emp WHERE bonus.job IN ('engineer', 'accountant') AND bonus.ename = london_emp.ename(+);
The optimizer pushes the outer join predicate into the view. The resulting statement (which does not conform to standard SQL syntax) looks like this:
SELECT bonus.job, london_emp.ename FROM bonus, (SELECT emp.ename FROM emp, dept WHERE bonus.ename = london_emp.ename(+) AND emp.deptno = dept.deptno AND dept.loc = 'London') WHERE bonus.job IN ('engineer', 'accountant');
Consider the following example:
SELECT 'PAYMENT' c_tx_type, c.check_id c_tx_id, 1 c_je_header_id,c.status_lookup_code, c_tx_status, DECODE(:c_bank_curr_dsp,:c_gl_currency_ code, NVL(c.base_amount,NVL(c.amount,0)), NVL(c.amount,0)) c_tx_ba_amount, DECODE(SIGN(:c_julian_as_of_date - TO_CHAR(c.check_date,'J')),-1, DECODE(:c_bank_curr_dsp,:c_gl_currency_code, NVL(c.base_amount,NVL(c.amount,0)), NVL(c.amount,0)),0) c_tx_ba_future_ amount, NULL c_tx_dr_cr, cs.future_pay_code_combination_id c_tx_clearing_ ccid, NVL(c.exchange_rate, 0) c_tx_exchange_rateFROM ap_checks c, ap_check_stocks cs WHERE (c.check_stock_id(+) = cs.check_stock_id ) AND (:c_sl_reference_type = 'PAYMENT') AND (:c_sl_reference_id= c.check_id) AND (:c_sl_je_header_id = 1);
Without pushing the join predicate: 41 minutes, 1,492,141 buffer gets, 125,202 disk reads
Cost=20003 SELECT STATEMENT Cost= FILTER Cost= FILTER Cost= NESTED LOOPS OUTER Cost=1 TABLE ACCESS FULL AP_CHECK_STOCKS_ALL Cost=20002 TABLE ACCESS FULL AP_CHECKS_ALL
After pushing the join predicate: 0.01 seconds, 6 buffer gets, 5 disk reads
Cost=4 SELECT STATEMENT Cost= FILTER Cost=4 NESTED LOOPS OUTER Cost=3 TABLE ACCESS BY INDEX ROWID AP_CHECKS_ALL Cost=2 INDEX UNIQUE SCAN AP_CHECKS_U1: Cost=1 TABLE ACCESS BY INDEX ROWID AP_CHECK_STOCKS_ALL Cost= INDEX UNIQUE SCAN AP_CHECK_STOCKS_U1:
The optimizer cannot transform all statements that access views into equivalent statements that access base table(s). For example, if a query accesses a ROWNUM pseudocolumn in a view, then the view cannot be merged into the query, and the query's predicate cannot be pushed into the view.
To execute a statement that cannot be transformed into one that accesses base tables, Oracle issues the view's query, collects the resulting set of rows, and then accesses this set of rows with the original statement as though it were a table.
Consider the emp_group_by_deptno view defined in the previous section:
CREATE VIEW emp_group_by_deptno AS SELECT deptno, AVG(sal) avg_sal, MIN(sal) min_sal, MAX(sal) max_sal FROM emp GROUP BY deptno;
The following query accesses the view. The query joins the average, minimum, and maximum salaries from each department represented in this view and to the name and location of the department in the dept table:
SELECT emp_group_by_deptno.deptno, avg_sal, min_sal, max_sal, dname, loc FROM emp_group_by_deptno, dept WHERE emp_group_by_deptno.deptno = dept.deptno;
Because there is no equivalent statement that accesses only base tables, the optimizer cannot transform this statement. Instead, the optimizer chooses an execution plan that issues the view's query and then uses the resulting set of rows as it would the rows resulting from a table access.
|
See Also:
For more information on how Oracle performs a nested loops join operation, see "Optimizing Joins". |
Figure 4-13 shows the execution plan for this statement.
To execute this statement, Oracle performs the following operations:
emp table.
sal values selected by the query for the emp_group_by_deptno view.
deptno value to perform a unique scan of the pk_dept index.
deptno table with the matching deptno value.
To choose the execution plan for a compound query, the optimizer chooses an execution plan for each of its component queries, and then combines the resulting row sources with the union, intersection, or minus operation, depending on the set operator used in the compound query.
Figure 4-14 shows the execution plan for the following statement, which uses the UNION ALL operator to select all occurrences of all parts in either the orders1 table or the orders2 table:
SELECT part FROM orders1 UNION ALL SELECT part FROM orders2;
To execute this statement, Oracle performs the following steps:
orders1 and orders2 tables.
UNION-ALL operation returning all rows that are returned by either step 2 or step 3 including all copies of duplicates.
Figure 4-15 shows the execution plan for the following statement, which uses the UNION operator to select all parts that appear in either the orders1 or orders2 table:
SELECT part FROM orders1 UNION SELECT part FROM orders2;
This execution plan is identical to the one for the UNION-ALL operator shown in Figure 4-14, except that in this case, Oracle uses the SORT operation to eliminate the duplicates returned by the UNION-ALL operation.
Figure 4-16 shows the execution plan for the following statement, which uses the INTERSECT operator to select only those parts that appear in both the orders1 and orders2 tables:
SELECT part FROM orders1 INTERSECT SELECT part FROM orders2;
To execute this statement, Oracle performs the following steps:
orders1 and orders2 tables.
INTERSECTION operation that returns only rows that are returned by both steps 2 and 4.
The optimizer chooses execution plans for SQL statements that access data on remote databases in much the same way that it chooses executions for statements that access only local data:
When choosing a cost-based execution plan for a distributed statement, the optimizer considers the available indexes on remote databases just as it does indexes on the local database. The optimizer also considers statistics on remote databases for the CBO. Furthermore, the optimizer considers the location of data when estimating the cost of accessing it. For example, a full scan of a remote table has a greater estimated cost than a full scan of an identical local table.
For a rule-based execution plan, the optimizer does not consider indexes on remote tables.
|
See Also:
For more information on tuning distributed queries, see Chapter 9, "Optimizing SQL Statements". |
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|