Oracle8i Concepts
Release 8.1.5
A67781-01

Library
Product
Contents
Index

Prev Next

23
Optimizer Operations

dy/dx = 0; d2y/dx2 >0.

Leibniz

This chapter discusses how the Oracle optimizer chooses how to execute SQL statements. It includes:

Overview of Optimizer Operations

This section summarizes the operations performed by the Oracle optimizer and describes the types of SQL statements that can be optimized.

Optimizer Operations

For any SQL statement processed by Oracle, the optimizer does the following:

evaluation of expressions and conditions  

The optimizer first evaluates expressions and conditions containing constants as fully as possible. (See "Evaluation of Expressions and Conditions".)  

statement transformation  

For a complex statement involving, for example, correlated subqueries, the optimizer may transform the original statement into an equivalent join statement. (See "Transforming and Optimizing Statements".)  

view merging  

For a SQL statement that accesses 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".)  

choice of optimization approaches  

The optimizer chooses either a cost-based or rule-based approach to optimization and determines the goal of optimization. (See "Choosing an Optimization Approach and Goal".)  

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 "Choosing Access Paths".)  

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. (See "Optimizing Join Statements".)  

choice of join operations  

For any join statement, the optimizer chooses an operation to use to perform the join. (See "Optimizing Join Statements".)  

Types of SQL Statements

Oracle optimizes these different types of SQL statements:

simple statement  

An INSERT, UPDATE, DELETE, or SELECT statement that involves only a single table.  

simple query  

Another name for a SELECT statement.  

join  

A query that selects data from more than one table. A join is characterized by multiple tables in the FROM clause. Oracle pairs the rows from these tables using the condition specified in the WHERE clause and returns the resulting rows. This condition is called the join condition and usually compares columns of all the joined tables.  

equijoin  

A join condition containing an equality operator.  

nonequijoin  

A join condition containing something other than an equality operator.  

outer join  

A join condition using the outer join operator (+) with one or more columns of one of the tables. Oracle returns all rows that meet the join condition. Oracle also returns all rows from the table without the outer join operator for which there are no matching rows in the table with the outer join operator.  

Cartesian product  

A join with no join condition results in a Cartesian product, or a cross product. A Cartesian product is the set of all possible combinations of rows drawn one from each table. In other words, for a join of two tables, each row in one table is matched in turn with every row in the other. A Cartesian product for more than two tables is the result of pairing each row of one table with every row of the Cartesian product of the remaining tables.

All other kinds of joins are subsets of Cartesian products effectively created by deriving the Cartesian product and then excluding rows that fail the join condition.  

complex statement  

An INSERT, UPDATE, DELETE, or SELECT statement that contains a subquery, which is a form of the SELECT statement within another statement that produces a set of values for further processing within the statement. The outer portion of the complex statement that contains a subquery is called the parent statement.  

compound query  

A query that uses set operators (UNION, UNION ALL, INTERSECT, or MINUS) to combine two or more simple or complex statements. Each simple or complex statement in a compound query is called a component query.  

statement accessing views  

Simple, join, complex, or compound statement that accesses one or more views as well as tables.  

distributed statement  

A statement that accesses data on two or more distinct nodes of a distributed database. A remote statement accesses data on one remote node of a distributed database. See "Remote and Distributed SQL Statements".  

Evaluation of Expressions and Conditions

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.

The following sections discuss how the optimizer evaluates expressions and conditions that contain:

Constants

Computation of constants is performed only once, when the statement is optimized, rather than each time the statement is executed.

Consider these conditions that test for monthly salaries greater than 2000: 

sal > 24000/12 

sal > 2000 

sal*12 > 24000

If a SQL statement contains the first condition, the optimizer simplifies it into the second condition.

Note that the optimizer does not simplify expressions across comparison operators: in the examples above, the optimizer does not simplify the third expression into the second. For this reason, application developers should write conditions that compare columns with constants whenever possible, rather than conditions with expressions involving columns.

LIKE Operator

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), 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.

IN Operator

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'

See "Example 2: IN Subquery" for more information.

ANY or SOME Operator

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)

ALL Operator

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)

BETWEEN Operator

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

NOT Operator

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 dhen 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

Transitivity

If two conditions in the WHERE clause involve a common column, 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.

Note:

Transitivity is used only by the cost-based approach.  

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  

is any of the comparison operators =, !=, ^=, <, <>, >, <=, or >=.  

constant  

is any constant expression involving operators, SQL functions, literals, bind variables, and correlation variables.  

Example:

Consider this query in which the WHERE clause contains two conditions, each or 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, this condition makes available access paths using that index.

Note:

The optimizer only infers conditions that relate columns to constant expressions, rather than columns to other columns. Imagine a WHERE clause containing two conditions of these forms: 

WHERE column1 comp_oper column3 
  AND column1 = column2

In this case, the optimizer does not infer this condition: 

column2 comp_oper column3
 

DETERMINISTIC Functions

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. And if the function is redefined in the future, 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 matter to the correctness of 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. (See "DETERMINISTIC Functions".) 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.

Additional Information:

See the description of the pragma RESTRICT_REFERENCES in the Oracle8i Application Developer's Guide - Fundamentals and the descriptions of CREATE FUNCTION, CREATE INDEX, and CREATE MATERIALIZED VIEW in the Oracle8i SQL Reference. Also see "Function-Based Indexes" for a description of function-based indexes, and see Oracle8i Tuning for detailed information about materialized views.  

Transforming and Optimizing Statements

SQL is a very flexible query language; there are often many statements you could formulate 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 Chapter 24, "Optimization of Joins".

Transforming ORs into Compound Queries

If a query contains a WHERE clause with multiple conditions combined with OR operators, the optimizer transforms it into an equivalent compound query that uses the UNION ALL set operator if this makes it execute more efficiently:

For information on access paths and how indexes make them available, see Table 23-1 and the sections that follow it.

Example:

Consider this query with a WHERE clause that 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, 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';

If you are using the cost-based approach, the optimizer compares the cost of executing the original query using a full table scan with that of executing the resulting query when deciding whether to make the transformation.

If you are using the rule-based approach, the optimizer makes this UNION ALL transformation because each component query of the resulting compound query can be executed using an index. The rule-based approach 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 23-1.

Figure 23-1 Execution Plan for a Transformed Query Containing OR


To execute the transformed query, Oracle performs the following steps:

If either of the JOB or DEPTNO columns is not indexed, 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.

Example:

Consider this query and assume 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;

Since 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.

Transforming Complex Statements into Join Statements

To optimize a complex statement, the optimizer chooses one of these alternatives:

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 optimization techniques described in "Optimizing Join Statements".

Consider this complex statement that 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, the optimizer can transform the complex query into this 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 23-2.

Figure 23-2 Execution Plan for a Nested Loops Join


To execute this statement, Oracle performs a nested-loops join operation. For information on nested loops joins, see "Join Operations".

If the optimizer cannot transform a complex statement into a join statement, 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.

Consider this complex statement that 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. Note that complex queries whose subqueries contain aggregate functions such as AVG cannot be transformed into join statements.

Optimizing Statements That Access Views

To optimize a statement that accesses a view, the optimizer chooses one of these alternatives:

Merging the View's Query into the Statement

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 Unmergeable Views").

Example:

Consider this view 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;

Consider this query that 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, the resulting WHERE clause makes them available.

Mergeable and Unmergeable Views

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:

When a view contains one of the following structures, it can be merged into a referencing query block only if complex view merging is enabled (as described below):

View merging is not possible for a view that has multiple base tables if it is on the right side of an outer join. If a view on the right side of an outer join has only one base table, however, the optimizer can use complex view merging even if an expression in the view can return a non-null value for a NULL. See "Views in Outer Joins" for more information.

Complex View Merging

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 or the MERGE hint, that is, either the COMPLEX_VIEW_MERGING parameter must be set to TRUE or the accessing query block must include the MERGE hint. Without this hint or parameter setting, the optimizer uses another approach (see "Pushing the Predicate into the View").

Additional Information:

See Oracle8i Tuning for details about the MERGE and NO_MERGE hints.  

Example 1: View with a GROUP BY Clause

Consider the view AVG_SALARY_VIEW, which 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 this query, which finds the average salaries of departments in London: 

SELECT dept.deptloc, avg_sal_dept 
  FROM dept, avg_salary_view 
  WHERE dept.deptno = avg_salary_view.deptno 
    AND dept.deptloc = 'London';

into this query: 

SELECT dept.deptloc, AVG(sal) 
  FROM dept, emp 
  WHERE dept.deptno = emp.deptno 
    AND dept.deptloc = 'London' 
  GROUP BY dept.rowid, dept.deptloc;

The transformed query accesses the view's base table, selecting only the rows of employees who work in London and grouping them by department.

Example 2: IN Subquery

Complex merging can be used for an IN clause with a noncorrelated subquery, as well as for views. Consider the view MIN_SALARY_VIEW, which 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 this 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.deptloc = 'London';

into this 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.deptloc = 'London' 
    AND e1.deptno = e2.deptno 
  GROUP BY e1.rowid, dept.rowid, e1.ename, e1.sal 
  HAVING e1.sal = MIN(e2.sal);

Pushing the Predicate into the View

The optimizer can transform a query block that accesses an unmergeable view by pushing the query block's predicates inside the view's query.

Example 1:

Consider the TWO_EMP_TABLES view, which 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;

Consider this query that 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 this: 

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, the resulting WHERE clauses make it available.

Figure 23-3 shows the execution plan of the resulting statement.

Figure 23-3 Accessing a View Defined with the UNION Set Operator


To execute this statement, Oracle performs these steps:

Example 2:

Consider the view EMP_GROUP_BY_DEPTNO, which 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;

Consider this query, which 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 this: 

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, the resulting WHERE clause makes it available.

Figure 23-4 shows the execution plan for the resulting statement. The execution plan uses an index on the DEPTNO column.

Figure 23-4 Accessing a View Defined with a GROUP BY Clause


To execute this statement, Oracle performs these operations:

Applying an Aggregate Function to the View

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.

Example:

Consider a query that 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 23-5 shows the execution plan of the resulting statement.

Figure 23-5 Applying Aggregate Functions to a View Defined with GROUP BY Clause


To execute this statement, Oracle performs these operations:

Accessing the View's Rows with the Original Statement

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, 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.

Example:

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;

Consider this query, which 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;

Since 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.

Figure 23-6 shows the execution plan for this statement. For more information on how Oracle performs a nested loops join operation, see "Join Operations".

Figure 23-6 Joining a View Defined with a GROUP BY Clause to a Table


To execute this statement, Oracle performs these operations:

Optimizing Compound Queries

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 23-7 shows the execution plan for this 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;

Figure 23-7 Compound Query with UNION ALL Set Operator


To execute this statement, Oracle performs these steps:

Figure 23-8 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;

Figure 23-8 Compound Query with UNION Set Operator


This execution plan is identical to the one for the UNION-ALL operator shown in Figure 23-7, except that in this case Oracle uses the SORT operation to eliminate the duplicates returned by the UNION-ALL operation.

Figure 23-9 shows the execution plan for this 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;

Figure 23-9 Compound Query with INTERSECT Set Operator


To execute this statement, Oracle performs these steps:

Optimizing Distributed Statements

The optimizer chooses execution plans for SQL statements that access data on remote databases in much the same way 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 cost-based optimization. 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.

Choosing an Optimization Approach and Goal

The optimizer's behavior when choosing an optimization approach and goal for a SQL statement is affected by these factors:

The OPTIMIZER_MODE Initialization Parameter

The OPTIMIZER_MODE initialization parameter establishes the default behavior for choosing an optimization approach for the instance. It can have these values:

CHOOSE  

The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available for the cost-based approach. If the data dictionary contains statistics for at least one of the accessed tables, the optimizer uses a cost-based approach and optimizes with a goal of best throughput. If the data dictionary contains no statistics for any of the accessed tables, the optimizer uses a rule-based approach. This is the default value for the parameter.  

ALL_ROWS  

The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).  

FIRST_ROWS  

The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best response time (minimum resource use to return the first row of the result set).  

RULE  

The optimizer chooses a rule-based approach for all SQL statements regardless of the presence of statistics.  

If the optimizer uses the cost-based approach for a SQL statement and some tables accessed by the statement have no statistics, the optimizer uses internal information (such as the number of data blocks allocated to these tables) to estimate other statistics for these tables.

Statistics in the Data Dictionary

Oracle stores statistics about columns, tables, clusters, indexes, and partitions in the data dictionary for cost-based optimization. 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 command, or the COMPUTE STATISTICS clause of the CREATE or ALTER INDEX command.

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. See "Statistics for Cost-Based Optimization" for more information about statistics.

The OPTIMIZER_GOAL Parameter of the ALTER SESSION Command

The OPTIMIZER_GOAL parameter of the ALTER SESSION command can override the optimization 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 optimization approach for recursive SQL statements is affected only by the value of the OPTIMIZER_MODE initialization parameter.

The OPTIMIZER_GOAL parameter can have these values:

CHOOSE  

The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available for the cost-based approach. If the data dictionary contains statistics for at least one of the accessed tables, the optimizer uses a cost-based approach and optimizes with a goal of best throughput. If the data dictionary contains no statistics for any of the accessed tables, the optimizer uses a rule-based approach.  

ALL_ROWS  

The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).  

FIRST_ROWS  

The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best response time (minimum resource use to return the first row of the result set).  

RULE  

The optimizer chooses a rule-based approach for all SQL statements issued to the Oracle instance regardless of the presence of statistics.  

The FIRST_ROWS, ALL_ROWS, CHOOSE, and RULE Hints

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 command.

Additional Information:

See Oracle8i Tuning for information on how to use hints.  

PL/SQL and the Optimizer Goal

The optimizer goal applies only to queries submitted directly, not queries submitted from within PL/SQL.

You can use hints to determine the access path for SQL statements submitted from within PL/SQL.

Choosing Access Paths

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 discusses:

Access Methods

This section describes the basic methods by which Oracle can access data.

Full Table Scans

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.

Sample Table Scans

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 option or the SAMPLE BLOCK option. To perform a sample table scan when sampling by rows (the SAMPLE option), 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 option), 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. See "Types of SQL Statements" for information about joins and distributed statements.

Table Access by Rowid

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.

Cluster Scans

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.

Hash Scans

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.

Index Scans

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, 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, 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 these types:

unique scan  

A unique scan of an index returns only a single rowid. Oracle performs a unique scan only in cases in which a single rowid is required, rather than many rowids. For example, Oracle performs a unique scan if there is a UNIQUE or a PRIMARY KEY constraint that guarantees that the statement accesses only a single row.  

range scan  

A range scan of an index can return zero or more rowids depending on how many rows the statement accesses.  

full scan  

Full index scan is available if a predicate references one of the columns in the index. The predicate does not have to be an index driver. Full scan is also available when there is no predicate if all of the columns in the table referenced in the query are included in the index and at least one of the index columns is not nullable. Full scan can be used to eliminate a sort operation. It reads the blocks singly.  

fast full scan  

Fast full index scan is an alternative to a full table scan when the index contains all the columns that are needed for the query and at least one column in the index key has the NOT NULL constraint. Fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation. It reads the entire index using multiblock reads (unlike a full index scan) and can be parallelized.

Fast full scan is available only with cost-based optimization. You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint.  

index join  

An index join is a hash join of several indexes that together contain all the columns from the table that are referenced in the query. If an index join is used, no table access is needed since all the relevant column values can be retrieved from the indexes. An index join cannot be used to eliminate a sort operation.

Index join is available only with cost-based optimization. You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_JOIN hint.  

bitmap  

Bitmap indexes use a bitmap for key values and a mapping function that converts each bit position to a rowid. Bitmaps can efficiently merge indexes that correspond to several conditions in a WHERE clause, using Boolean operations to resolve AND and OR conditions (see "Bitmap Indexes").

Bitmap access is available only with cost-based optimization.  

Attention:

Bitmap indexes are available only if you have purchased the Oracle8i Enterprise Edition. See Getting to Know Oracle8i for more information.  

Access Paths

Table 23-1 lists the data access paths. The optimizer can only choose to use a particular access path for a table if the statement contains a WHERE clause condition or other construct that makes that access path available.

Each of the following sections describes an access path and discusses when it is available, the method Oracle uses to access data with it, and the output generated for it by the EXPLAIN PLAN command.

Path 1: Single Row by Rowid

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.

Example:

This access path is available in the following statement: 

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

Path 2: Single Row by Cluster Join

This access path is available for statements that join tables stored in the same cluster if both of these conditions are true:

These conditions must be combined with AND operators. To execute the statement, Oracle performs a nested loops operation. (For information on the nested loops operation, see "Join Operations".)

Example:

This access path is available for the following statement in which 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.

Path 3: Single Row by Hash Cluster Key with Unique or Primary Key

This access path is available if both of these conditions are true:

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.

Example:

This access path is available in the following statement in which 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

Path 4: Single Row by Unique or Primary Key

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.

Example:

This access path is available in the following statement in which 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.

Path 5: Clustered Join

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. (For information on nested loops operations, see "Join Operations".)

Example:

This access path is available in the following statement in which 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

Path 6: Hash Cluster Key

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.

Example:

This access path is available for the following statement in which 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

Path 7: Indexed Cluster Key

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. Since all rows with the same cluster key value are stored together, the cluster scan requires only a single rowid to find them all.

Example:

This access path is available in the following statement in which 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.

Path 8: Composite 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.

Example:

This access path is available in the following statement in which 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.

Path 9: Single-Column Indexes

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, Oracle executes the statement by performing a range scan on the index to retrieve the rowids of the selected rows and then accessing the table by these rowids.

Example:

This access path is available in the following statement in which 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, 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, 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.

Example:

This access path is available in the following statement in which 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.

Path 10: Bounded Range Search on Indexed Columns

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.

Example:

This access path is available in this statement in which 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.

Example:

This access path is also available in the following statement in which there is an index on the ENAME column of the EMP table: 

SELECT * 
    FROM emp 
    WHERE ename LIKE 'S%';

Path 11: Unbounded Range Search on Indexed Columns

This access path is available if the statement's WHERE clause contains one of these 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.

Example:

This access path is available in the following statement in which 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
Example:

This access path is available in the following statement in which 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.

Example:

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.

Path 12: Sort-Merge Join

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. (For information on these operations, see "Optimizing Join Statements".)

Example:

This access path is available for the following statement in which 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

Path 13: MAX or MIN of Indexed Column

This access path is available for a SELECT statement for which all of these conditions are true:

To execute the query, Oracle performs a range scan of the index to find the maximum or minimum indexed value. Since only this value is selected, Oracle need not access the table after scanning the index.

Example:

This access path is available for the following statement in which 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

Path 14: ORDER BY on Indexed Column

This access path is available for a SELECT statement for which all of these conditions are true:

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.

Example:

This access path is available for the following statement in which 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.

Path 15: Full Table Scan

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 these 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.

Any SQL statement that contains only these constructs and no others that make index access paths available must use full table scans.

Example:

This 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

Sample Table Scan (Unranked Access Path)

This access path is available for a SELECT statement that includes the SAMPLE or SAMPLE BLOCK option in its FROM clause. Sample table scans require cost-based optimization.

Example:

This 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

Index Join (Unranked Access Path)

This access path is available for a SELECT statement that accesses data found in two or more indexed columns of a table. No table access is needed since all the relevant column values can be retrieved from the indexes. Index joins require cost-based optimization.

Example:

This statement uses an index join to access the EMPNO and SAL columns, both of which are indexed, in the EMP table: 

SELECT empno, sal 
    FROM emp 
    WHERE sal > 2000;

The EXPLAIN PLAN output for this statement might look like this: 

OPERATION               OPTIONS          OBJECT_NAME 
--------------------------------------------------------- 
SELECT STATEMENT 
  VIEW                                   index$_join$_001 
    HASH JOIN 
      INDEX             RANGE SCAN       EMP_SAL 
      INDEX             FAST FULL SCAN   EMP_EMPNO

Choosing Among Access Paths

This section describes how the optimizer chooses among available access paths when using the cost-based or rule-based approach.

Choosing an Access Path with the Cost-Based Approach

With the cost-based approach, the optimizer chooses an access path based on these 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 option). 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. The optimizer then 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.

Additional Information:

See Oracle8i Tuning for information about hints in SQL statements.  

To choose among available access paths, the optimizer considers these factors:

Example:

Consider this query, which 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, 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 (access path 4).

Example:

Consider again the query in the previous example. If the ENAME column is not a unique or primary key, the optimizer can use these statistics to estimate the query's selectivity:

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.

Example:

Consider this query, which 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.

Example:

Consider this query, which 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 of the column (because it is indexed). 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.

Example:

Consider this query, which 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 selectiviy for indexed columns in order to favor the use of the index.

Example:

Consider this query, which 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 )

Choosing an Access Path with the Rule-Based Approach

With the rule-based approach, the optimizer chooses whether to use an access path based on these factors:

To choose an access path, the optimizer first examines the conditions in the statement's WHERE clause to determine which access paths are available. The optimizer then chooses the most highly ranked available access path.

Note that the full table scan is the lowest ranked access path on the list. This means that the rule-based approach always chooses an access path that uses an index if one is available, even if a full table scan might execute faster.

The order of the conditions in the WHERE clause does not normally affect the optimizer's choice among access paths.

Example:

Consider this SQL statement, which selects the employee numbers of all employees in the EMP table with an ENAME value of 'CHUNG' and with a SAL value greater than 2000: 

SELECT empno 
  FROM emp 
  WHERE ename = 'CHUNG' 
    AND sal > 2000;

Consider also that the EMP table has these integrity constraints and indexes:

Based on the conditions in the WHERE clause of the SQL statement, the integrity constraints, and the indexes, these access paths are available:

Note that the PK_EMPNO index does not make the single row by primary key access path available because the indexed column does not appear in a condition in the WHERE clause.

Using the rule-based approach, the optimizer chooses the access path that uses the ENAME_IND index to execute this statement. The optimizer chooses this path because it is the most highly ranked path available.


Prev
Next
Oracle
Copyright © 1999 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index