Optimizer Hints_link管理

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

Example 5-1 Specifying a Full Set of Hints

SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b) USE_NL (glcc glf) USE_MERGE (gp gsb) */ b.application_id , b.set_of_books_id , b.personnel_id, p.vendor_id Personnel, p.segment1 PersonnelNumber, p.vendor_name Name 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 ...

Unless the hints are inside the base view, they might not be honored from a query against the view.

Local Hints Compared with Global Hints

Table hints (in other words, hints that specify a table) generally refer to tables in the DELETE , SELECT , or UPDATE statement in which the hint occurs, not to tables inside any views referenced by the statement. When you want to specify hints for tables that appear inside views, Oracle recommends using global hints instead of embedding the hint in the view. Any table hint described in this chapter can be transformed into a global hint by using an extended syntax for the table name.

The hints described in this section let you choose between the cost-based and the rule-based optimization approaches. With the cost-based approach, this also includes the goal of best throughput or best response time.

ALL_ROWS

FIRST_ROWS(n)

CHOOSE

For example, the optimizer uses the cost-based approach to optimize this statement for best throughput:

SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id FROM employees WHERE employee_id = 7566;

FIRST_ROWS( n)

The hints FIRST_ROWS ( n ) (where n is any positive integer) or FIRST_ROWS instruct Oracle to optimize an individual SQL statement for fast response. FIRST_ROWS (n) affords greater precision, because it instructs Oracle to choose the plan that returns the first n rows most efficiently. The FIRST_ROWS hint, which optimizes for the best plan to return the first single row, is retained for backward compatibility and plan stability.

first_rows_hint::=

Text description of the illustration first_rows_hint.gif

For example, the optimizer uses the cost-based approach to optimize this statement for best response time:

SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id FROM employees WHERE department_id = 20;

The optimizer ignores this hint in DELETE and UPDATE statement blocks and in SELECT statement blocks that contain any of the following syntax:

Set operators (


     UNION


     INTERSECT


     MINUS


     UNION

ALL

)


     GROUP

BY

clause

FOR


     UPDATE

clause

Aggregate functions


     DISTINCT

operator


     ORDER

BY

clauses, when there is no index on the ordering columns

These statements cannot be optimized for best response time, because Oracle must retrieve all rows accessed by the statement before returning the first row. If you specify this hint in any of these statements, then the optimizer uses the cost-based approach and optimizes for best throughput.

If you specify either the ALL_ROWS or the FIRST_ROWS hint in a SQL statement, and if the data dictionary does not have statistics about tables accessed by the statement, then the optimizer uses default statistical values (such as allocated storage for such tables) to estimate the missing statistics and, subsequently, to choose an execution plan.

These estimates might not be as accurate as those gathered by the DBMS_STATS package. Therefore, use the DBMS_STATS package to gather statistics. If you specify hints for access paths or join operations along with either the ALL_ROWS or FIRST_ROWS hint, then the optimizer gives precedence to the access paths and join operations specified by the hints.

The CHOOSE hint causes the optimizer to choose between the rule-based and cost-based approaches for a SQL statement. The optimizer bases its selection on the presence of statistics for the tables accessed by the statement. If the data dictionary has statistics for at least one of these tables, then the optimizer uses the cost-based approach and optimizes with the goal of best throughput. If the data dictionary does not have statistics for these tables, then it uses the rule-based approach.

choose_hint::=

Text description of the illustration choose_hint.gif

For example:

SELECT /*+ CHOOSE */ employee_id, last_name, salary, job_id FROM employees WHERE employee_id = 7566;

Specifying one of these hints causes the optimizer to choose the specified access path only if the access path is available based on the existence of an index or cluster and on the syntactic constructs of the SQL statement. If a hint specifies an unavailable access path, then the optimizer ignores it.

You must specify the table to be accessed exactly as it appears in the statement. If the statement uses an alias for the table, then use the alias rather than the table name in the hint. The table name within the hint should not include the schema name if the schema name is present in the statement.

If this hint specifies a single available index, then the optimizer performs a scan on this index. The optimizer does not consider a full table scan or a scan on another index on the table.

If this hint specifies a list of available indexes, then the optimizer considers the cost of a scan on each index in the list and then performs the index scan with the lowest cost. The optimizer can also choose to scan multiple indexes from this list and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan or a scan on an index not listed in the hint.

If this hint specifies no indexes, then the optimizer considers the cost of a scan on each available index on the table and then performs the index scan with the lowest cost. The optimizer can also choose to scan multiple indexes and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan.

For example, consider this query that selects the name, height, and weight of all male patients in a hospital:

SELECT name, height, weight FROM patients WHERE sex = 'm';

Assume that there is an index on the SEX column and that this column contains the values m and f . If there are equal numbers of male and female patients in the hospital, then the query returns a relatively large percentage of the table's rows, and a full table scan is likely to be faster than an index scan. However, if a very small percentage of the hospital's patients are male, then the query returns a relatively small percentage of the table's rows, and an index scan is likely to be faster than a full table scan.

Barring the use of frequency histograms, the number of occurrences of each distinct column value is not available to the optimizer. The cost-based approach assumes that each value has an equal probability of appearing in each row. For a column having only two distinct values, the optimizer assumes each value appears in 50% of the rows, so the cost-based approach is likely to choose a full table scan rather than an index scan.

If you know that the value in the WHERE clause of the query appears in a very small percentage of the rows, then you can use the INDEX hint to force the optimizer to choose an index scan. In this statement, the INDEX hint explicitly chooses an index scan on the sex_index , the index on the sex column:

SELECT /*+ INDEX(patients sex_index) use sex_index because there are few male patients */ name, height, weight FROM patients WHERE sex = 'm';

The INDEX hint applies to IN -list predicates; it forces the optimizer to use the hinted index, if possible, for an IN -list predicate. Multicolumn IN -lists will not use an index.

INDEX_ASC

Because Oracle's default behavior for a range scan is to scan index entries in ascending order of their indexed values, this hint does not specify anything more than the INDEX hint. However, you might want to use the INDEX_ASC hint to specify ascending range scans explicitly should the default behavior change.

INDEX_COMBINE

The INDEX_COMBINE hint explicitly chooses a bitmap access path for the table. If no indexes are given as arguments for the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate for the table. If certain indexes are given as arguments, then the optimizer tries to use some Boolean combination of those particular bitmap indexes.

index_combine_hint::=

Text description of the illustration index_combine_hint.gif

For example:

SELECT /*+INDEX_COMBINE(employees salary_bmi hire_date_bmi)*/ * FROM employees WHERE salary < 50000 AND hire_date < '01-JAN-1990';

INDEX_JOIN

For example, the following query uses an index join to access the employee_id and department_id columns, both of which are indexed in the employees table.

SELECT /*+index_join(employees emp_emp_id_pk emp_department_ix)*/ employee_id, department_id FROM employees WHERE department_id > 50;

INDEX_DESC

SELECT /*+ INDEX_DESC(a ord_order_date_ix) */ a.order_date, a.promotion_id, a.order_id FROM orders a WHERE a.order_date = :b1;

INDEX_FFS

SELECT /*+ INDEX_FFS ( o order_pk ) */ COUNT(*) FROM order_items l, orders o WHERE l.order_id > 50 AND l.order_id = o.order_id;

If this hint specifies a single available index, then the optimizer does not consider a scan on this index. Other indexes not specified are still considered.

If this hint specifies a list of available indexes, then the optimizer does not consider a scan on any of the specified indexes. Other indexes not specified in the list are still considered.

If this hint specifies no indexes, then the optimizer does not consider a scan on any index on the table. This behavior is the same as a


     NO_INDEX

hint that specifies a list of all available indexes for the table.

The NO_INDEX hint applies to function-based, B-tree, bitmap, cluster, or domain indexes. If a NO_INDEX hint and an index hint ( INDEX , INDEX_ASC , INDEX_DESC , INDEX_COMBINE , or INDEX_FFS ) both specify the same indexes, then both the NO_INDEX hint and the index hint are ignored for the specified indexes and the optimizer considers the specified indexes.

For example:

SELECT /*+NO_INDEX(employees emp_empid)*/ employee_id FROM employees WHERE employee_id > 200;

AND_EQUAL

The REWRITE hint forces the cost-based optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. Use the REWRITE hint with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, then Oracle uses that view regardless of its cost.

Oracle does not consider views outside of the list. If you do not specify a view list, then Oracle searches for an eligible materialized view and always uses it regardless of its cost.

rewrite_hint::=

Text description of the illustration rewrite_hint.gif

Oracle9i Database Concepts and Oracle9i Advanced Replication for more information on materialized views

Oracle9i Data Warehousing Guide for more information on using


    REWRITE

with materialized views

Then, for each branch of the UNION ALL , Oracle tries a rewrite with a materialized view. The rewrite may do a joinback and rollup of the materialized view. Finally, Oracle looks at the branches not rewritten and tries to represent them as a single query block with grouping sets. So for example, if only the last branch of the UNION ALL was rewritten with materialized view MV , Oracle replaces the first two branches with a the equivalent GROUPING SET query, as follows:

SELECT year, quarter, month, sum(sales) FROM T GROUP BY grouping set ( (year, quarter, month), (year, quarter) ) UNION ALL SELECT year, null, null, sum_sales FROM MV

NOREWRITE

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.

Complex merging is not cost-based; that is, the accessing query block must include the MERGE hint. Without this hint, the optimizer uses another approach.

merge_hint::=

Text description of the illustration merge_hint.gif

For example:

SELECT /*+MERGE(v)*/ e1.last_name, e1.salary, v.avg_salary FROM employees e1, (SELECT department_id, avg(salary) avg_salary FROM employees e2 GROUP BY department_id) v WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary; SELECT /*+NO_MERGE(dallas_dept)*/ e1.last_name, dallas_dept.dname FROM employees e1, (SELECT department_id, dname FROM departments WHERE loc = 'DALLAS') dallas_dept WHERE e1.department_id = dallas_dept.department_id;

When the NO_MERGE hint is used without an argument, it should be placed in the view query block. When NO_MERGE is used with the view name as an argument, it should be placed in the surrounding query.

STAR_TRANSFORMATION

The STAR_TRANSFORMATION hint makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a cost-based decision to use the best plan generated without the transformation, instead of the best plan for the transformed query.

Even if the hint is given, there is no guarantee that the transformation will take place. The optimizer only generates the subqueries if it seems reasonable to do so. If no subqueries are generated, then there is no transformed query, and the best plan for the untransformed query is used, regardless of the hint.

star_transformation_hint::=

Text description of the illustration star_transformation_hint.gif

The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.

If you omit the ORDERED hint from a SQL statement performing a join, then the optimizer chooses the order in which to join the tables. You might want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information lets you choose an inner and outer table better than the optimizer could.

ordered_hint::=

Text description of the illustration ordered_hint.gif

The following query is an example of the use of the ORDERED hint:

SELECT /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity FROM customers c, order_items l, orders o WHERE c.cust_last_name = :b1 AND o.customer_id = c.customer_id AND o.order_id = l.order_id;

The STAR hint forces a star query plan to be used, if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least three tables, the large table's concatenated index has at least three columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.

star_hint::=

Text description of the illustration star_hint.gif

Usually, if you analyze the tables, then the optimizer selects an efficient star plan. You can also use hints to improve the plan. The most precise method is to order the tables in the FROM clause in the order of the keys in the index, with the large table last. Then use the following hints:

/*+ ORDERED USE_NL(FACTS) INDEX(facts fact_concat) */

where facts is the table and fact_concat is the index. A more general method is to use the STAR hint.

Hints for Join Operations

In the hint you must specify a table exactly the same way it appears in the statement. If the statement uses an alias for the table, then you must use the alias rather than the table name in the hint. However, the table name within the hint should not include the schema name, if the schema name is present in the statement.

Use of the USE_NL and USE_MERGE hints is recommended with the ORDERED hint. Oracle uses these hints when the referenced table is forced to be the inner table of a join; the hints are ignored if the referenced table is the outer table.

USE_NL

For example, consider this statement, which joins the accounts and customers tables. Assume that these tables are not stored together in a cluster:

SELECT accounts.balance, customers.last_name, customers.first_name FROM accounts, customers WHERE accounts.customer_id = customers.customer_id;

Because the default goal of the cost-based approach is best throughput, the optimizer chooses either a nested loops operation, a sort-merge operation, or a hash operation to join these tables, depending on which is likely to return all the rows selected by the query more quickly.

However, you might want to optimize the statement for best response time or the minimal elapsed time necessary to return the first row selected by the query, rather than best throughput. If so, then you can force the optimizer to choose a nested loops join by using the USE_NL hint. In this statement, the USE_NL hint explicitly chooses a nested loops join with the customers table as the inner table:

SELECT /*+ ORDERED USE_NL(customers) to get first row faster */ accounts.balance, customers.last_name, customers.first_name FROM accounts, customers WHERE accounts.customer_id = customers.customer_id;

In many cases, a nested loops join returns the first row faster than a sort merge join. A nested loops join can return the first row after reading the first selected row from one table and the first matching row from the other and combining them, while a sort merge join cannot return the first row until after reading and sorting all selected rows of both tables and then combining the first rows of each sorted row source.

In the following statement where a nested loop is forced through a hint, orders is accessed through a full table scan and the filter condition l.order_id = h.order_id is applied to every row. For every row that meets the filter condition, order_items is accessed through the index order_id .

SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity FROM orders h ,order_items l WHERE l.order_id = h.order_id;

Adding an INDEX hint to the query could avoid the full table scan on orders , resulting in an execution plan similar to one used on larger systems, even though it might not be particularly efficient here.

USE_MERGE

The following query shows an inventory usage report in which the optimizer avoids a sort for the GROUP BY operation by using the sort merge operation specified by the USE_MERGE hint.

SELECT /*+ USE_MERGE(inv l) */inv.product_id, SUM(l.quantity) FROM inventories inv, order_items l WHERE inv.product_id = l.product_id(+) GROUP BY inv.product_id; SELECT /*+USE_MERGE(h l) FULL(h l) */ h.customer_id, l.unit_price * l.quantity FROM orders h ,order_items l WHERE l.order_id = h.order_id;

USE_HASH

SELECT /*+USE_HASH(l l2) */ l.order_date, l.order_id, l2.product_id, SUM(l2.unit_price*quantity) FROM orders l, order_items l2 WHERE l.order_id = l2.order_id GROUP BY l2.product_id, l.order_date, l.order_id;

where table is the name or alias of a table to be used as the first table in the join order.

HASH_AJ, MERGE_AJ, and NL_AJ

Alternatively, the SQL predicate NOT IN can be evaluated using an anti-join to subtract two sets. Thus, employees . department_id can be anti-joined to departments . department_id to select all employees who are not in a set of departments, and you can get a list of all employees who are not in the shipping or receiving departments.

HASH_SJ, MERGE_SJ, and NL_SJ

WHERE exists (SELECT /*+HASH_SJ*/ * FROM employees WHERE employees.department_id = departments.department_id AND salary > 200000);

There can only be one table in the subquery.

The outer query block must not itself be a subquery.

The subquery must be correlated with an equality predicate.

The subquery must have no


     GROUP

BY


     CONNECT

BY

, or


     ROWNUM

references.

The hints described in this section determine how statements are parallelized or not parallelized when using parallel execution.

PARALLEL

NOPARALLEL

PQ_DISTRIBUTE

PARALLEL_INDEX

NOPARALLEL_INDEX

The PARALLEL hint must use the table alias, if an alias is specified in the query. The hint can then take two values, separated by commas after the table name. The first value specifies the degree of parallelism for the given table, and the second value specifies how the table is to be split among the Oracle Real Application Clusters instances. Specifying DEFAULT or no value signifies that the query coordinator should examine the settings of the initialization parameters to determine the default degree of parallelism. In the following example, the PARALLEL hint overrides the degree of parallelism specified in the employees table definition:

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 5) */ last_name FROM hr.employees hr_emp;

In the next example, the PARALLEL hint overrides the degree of parallelism specified in the employees table definition and tells the optimizer to use the default degree of parallelism determined by the initialization parameters. This hint also specifies that the table should be split among all of the available instances, with the of parallelism on each instance.

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, DEFAULT,DEFAULT) */ last_name FROM hr.employees hr_emp;

NOPARALLEL


     table_name

is the name or alias of a table to be used as the inner table of a join.


     outer_distribution

is the distribution for the outer table.


     inner_distribution

is the distribution for the inner table.

There are six combinations for table distribution. Only a subset of distribution method combinations for the joined tables is valid, as explained in Table 5-1 .

Table 5-1 Distribution Hint Combinations

Maps the rows of each table to consumer query servers, using a hash function on the join keys. When mapping is complete, each query server performs the join between a pair of resulting partitions. This hint is recommended when the tables are comparable in size and the join operation is implemented by hash-join or sort merge join.

All rows of the outer table are broadcast to each query server. The inner table rows are randomly partitioned. This hint is recommended when the outer table is very small compared to the inner table. As a general rule, use the Broadcast/None hint when inner table size * number of query servers > outer table size .

All rows of the inner table are broadcast to each consumer query server. The outer table rows are randomly partitioned. This hint is recommended when the inner table is very small compared to the outer table. As a general rule, use the None/Broadcast hint when inner table size * number of query servers < outer table size .

Maps the rows of the outer table, using the partitioning of the inner table. The inner table must be partitioned on the join keys. This hint is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers; for example, 14 partitions and 15 query servers.

Note: The optimizer ignores this hint if the inner table is not partitioned or not equijoined on the partitioning key.

Maps the rows of the inner table using the partitioning of the outer table. The outer table must be partitioned on the join keys. This hint is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers; for example, 14 partitions and 15 query servers.

Note: The optimizer ignores this hint if the outer table is not partitioned or not equijoined on the partitioning key.

For example: Given two tables, r and s , that are joined using a hash-join, the following query contains a hint to use hash distribution:

SELECT column_list /*+ORDERED PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s)*/ FROM r,s WHERE r.c=s.c;

The hint can take two values, separated by commas after the table name. The first value specifies the degree of parallelism for the given table. The second value specifies how the table is to be split among the Oracle Real Application Clusters instances. Specifying DEFAULT or no value signifies the query coordinator should examine the settings of the initialization parameters to determine the default degree of parallelism.

For example:

SELECT /*+ PARALLEL_INDEX(table1, index1, 3, 2) +/

In this example, there are three parallel execution processes to be used on each of two instances.

NOPARALLEL_INDEX

In the following example, the CACHE hint overrides the table's default caching specification:

SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name FROM hr.employees hr_emp;

NOCACHE

Starting with Oracle9 i , Release 2 (9.2), small tables are automatically cached, according to the criteria in Table 5-2 .

Table 5-2 Table Caching Criteria

Automatic caching of small tables is disabled for tables that are created or altered with the CACHE attribute.

UNNEST

Oracle9i SQL Reference for more information on unnesting nested subqueries and the conditions that make a subquery block valid

"Subquery Unnesting" and "How the CBO Unnests Subqueries" for more information on the use of unnesting

The PUSH_SUBQ hint causes non-merged subqueries to be evaluated at the earliest possible step in the execution plan. Generally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then it improves performance to evaluate the subquery earlier.

This hint has no effect if the subquery is applied to a remote table or one that is joined using a merge join.

push_subq_hint::=

Text description of the illustration push_subq_hint.gif

NO_PUSH_SUBQ

If you do not use the ORDERED_PREDICATES hint, then Oracle evaluates all predicates in the following order:

Predicates without user-defined functions, type methods, or subqueries are evaluated first, in the order specified in the


     WHERE

clause.

Predicates with user-defined functions and type methods that have user-computed costs are evaluated next, in increasing order of their cost.

Predicates with user-defined functions and type methods without user-computed costs are evaluated next, in the order specified in the


     WHERE

clause.

Predicates not specified in the


     WHERE

clause (for example, predicates transitively generated by the optimizer) are evaluated next.

Predicates with subqueries are evaluated last, in the order specified in the


     WHERE

clause.

Oracle can replace literals in SQL statements with bind variables, if it is safe to do so. This is controlled with the CURSOR_SHARING startup parameter. The CURSOR_SHARING_EXACT hint causes this behavior to be switched off. In other words, Oracle executes the SQL statement without any attempt to replace literals by bind variables.

cursor_sharing_exact_hint::=

Text description of the illustration cursor_sharing_exact_hint.gif

DYNAMIC_SAMPLING

The DYNAMIC_SAMPLING hint lets you control dynamic sampling to improve server performance by determining more accurate selectivity and cardinality estimates. You can set the value of DYNAMIC_SAMPLING to a value from 0 to 10. The higher the level, the more effort the compiler puts into dynamic sampling and the more broadly it is applied. Sampling defaults to cursor level unless you specify a table.

dynamic_sampling_hint::=

Text description of the illustration dynamic_sampling_hint.gif

where:


     table

specifies the name or alias of the table on which the dynamic sampling is to be performed.


     integer

is a value from

indicating the degree of sampling. If the statement does not use aliases, then the table name is the default alias.

There is more than one table in the query.

Some table has not been analyzed and has no indexes.

The optimizer determines that a relatively expensive table scan would be required for this table that has not been analyzed.

The sampling levels are as follows if the dynamic sampling level used is from a cursor hint or from the optimizer_dynamic_sampling parameter:

Level 0: Do not use dynamic sampling.

Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).

Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is the default number of dynamic sampling blocks.

Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks.

Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks.

Level 5: Apply dynamic sampling to all tables that meet the Level 4 criteria using 2 times the default number of dynamic sampling blocks.

Level 6: Apply dynamic sampling to all tables that meet the Level 5 criteria using 4 times the default number of dynamic sampling blocks.

Level 7: Apply dynamic sampling to all tables that meet the Level 6 criteria using 8 times the default number of dynamic sampling blocks.

Level 8: Apply dynamic sampling to all tables that meet the Level 7 criteria using 32 times the default number of dynamic sampling blocks.

Level 9: Apply dynamic sampling to all tables that meet the Level 8 criteria using 128 times the default number of dynamic sampling blocks.

Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.

The sampling levels are as follows if the dynamic sampling level used is from a table hint:

Level 0: Do not use dynamic sampling.

Level 1: The number of blocks sampled is the default number of dynamic sampling blocks (32).

Level 2: The number of blocks sampled is 2 times the default number of dynamic sampling blocks.

Level 3: The number of blocks sampled is 4 times the default number of dynamic sampling blocks.

Level 4: The number of blocks sampled is 8 times the default number of dynamic sampling blocks.

Level 5: The number of blocks sampled is 16 times the default number of dynamic sampling blocks.

Level 6: The number of blocks sampled is 32 times the default number of dynamic sampling blocks.

Level 7: The number of blocks sampled is 64 times the default number of dynamic sampling blocks.

Level 8: The number of blocks sampled is 128 times the default number of dynamic sampling blocks.

Level 9: The number of blocks sampled is 256 times the default number of dynamic sampling blocks.

Level 10: Read all blocks in the table.

If there is a table hint, dynamic sampling is used unless the table is analyzed and there are no predicates on the table. For example, the following query will not result in any dynamic sampling if employees is analyzed:

SELECT /*+ dynamic_sampling(e 1) */ count(*) FROM employees e;

To force cardinality estimation even for an analyzed table, you can use a further hint, dynamic_sampling_est_cdn , as in the following example:

SELECT /*+ dynamic_sampling(e 1) dynamic_sampling_est_cdn(t) */ count(*) FROM employees e;

This forces cardinality estimation for employees , even if the table is analyzed. The following query does both selectivity and cardinality estimation for employees :

SELECT /*+ dynamic_sampling(e 1) dynamic_sampling_est_cdn(e) */ count(*) FROM employees e WHERE cols > 3;

Using Hints with Views

Oracle does not encourage the use of hints inside or on views (or subqueries). This is because you can define views in one context and use them in another. However, such hints can result in unexpected execution plans. In particular, hints inside views or on views are handled differently, depending on whether the view is mergeable into the top-level query.

If you decide, nonetheless, to use hints with views, the following sections describe the behavior in each case.

Hints and Mergeable Views

Hints and Nonmergeable Views

If you want to specify a hint for a table in a view or subquery, then the global hint syntax is recommended. The following section describes this in detail.

Global Hints

Hints and Mergeable Views

If there is such a hint in the top-level query, then that hint is used regardless of any such hints inside the views.

If there is no top-level optimizer mode hint, then mode hints in referenced views are used as long as all mode hints in the views are consistent.

If two or more mode hints in the referenced views conflict, then all mode hints in the views are discarded and the session mode is used, whether default or user-specified.

Access Path and Join Hints on Views

Access path and join hints on referenced views are ignored, unless the view contains a single table (or references an Additional Hints view with a single table). For such single-table views, an access path hint or a join hint on the view applies to the table inside the view.

Access Path and Join Hints Inside Views

If the view is a subquery (that is, if it appears in the


     FROM

clause of a


     SELECT

statement), then all access path and join hints inside the view are preserved when the view is merged with the top-level query.

For views that are not subqueries, access path and join hints in the view are preserved only if the top-level query references no other tables or views (that is, if the


     FROM

clause of the


     SELECT

statement contains only the view).

Parallel Execution Hints on Views

PARALLEL , NOPARALLEL , PARALLEL_INDEX , and NOPARALLEL_INDEX hints on views are applied recursively to all the tables in the referenced view. Parallel execution hints in a top-level query override such hints inside a referenced view.

Parallel Execution Hints Inside Views

PARALLEL , NOPARALLEL , PARALLEL_INDEX , and NOPARALLEL_INDEX hints inside views are preserved when the view is merged with the top-level query. Parallel execution hints on the view in a top-level query override such hints inside a referenced view.

Hints and Nonmergeable Views

However, join hints on the view in the top-level query are preserved because, in this case, a nonmergeable view is similar to a table.

Global Hints

Table hints (hints that specify a table) normally refer to tables in the DELETE , SELECT , or UPDATE statement in which the hint occurs, not to tables inside any views referenced by the statement. When you want to specify hints for tables that appear inside views, use global hints instead of embedding the hint in the view. You can transform any table hint in this chapter into a global hint by using an extended syntax for the table name, described as follows.

Consider the following view definitions and SELECT statement:

CREATE OR REPLACE VIEW v1 AS SELECT * FROM employees WHERE employee_id < 150; CREATE OR REPLACE VIEW v2 AS SELECT v1.employee_id employee_id, departments.department_id department_id FROM v1, departments WHERE v1.department_id = departments.department_id; SELECT /*+ INDEX( v2.v1.employees emp_emp_id_pk ) FULL(v2.departments) */ * FROM v2 WHERE department_id = 30;

The view V1 retrieves all employees whose employee number is less than 150. The view V2 performs a join between the view V1 and the department table. The SELECT statement retrieves rows from the view V2 restricting it to the department whose number is 30.

There are two global hints in the SELECT statement. The first hint specifies an index scan for the employee table referenced in the view V1 , which is referenced in the view V2 . The second hint specifies a full table scan for the department table referenced in the view V2 . Note the dotted syntax for the view tables.

A hint such as:

INDEX(employees emp_emp_id_pk)

The global hint syntax also applies to unmergeable views. Consider the following SELECT statement:

SELECT /*+ NO_MERGE(v2) INDEX(v2.v1.employees emp_emp_id_pk) FULL(v2.departments) */ * FROM v2 WHERE department_id = 30;

If a global hint references a UNION or UNION ALL view, then the hint is applied to the first branch that contains the hinted table. Consider the INDEX hint in the following SELECT statement:

SELECT /*+ INDEX(v.employees emp_emp_id_pk) */ * FROM (SELECT * FROM employees WHERE employee_id < 150 UNION ALL SELECT * FROM employees WHERE employee_id > 175) v WHERE department_id = 30;