It would be nice to see the explicit announcement of predicate pushing here, but that seems to be […]. RSS feed for comments on this post. TrackBack URI. You are commenting using your WordPress. You are commenting using your Google account. You are commenting using your Twitter account. You are commenting using your Facebook account.
Notify me of new comments via email. Notify me of new posts via email. This site uses Akismet to reduce spam. Learn how your comment data is processed. Email Address:. Click to subscribe. Website Powered by WordPress. The rest of this document will be devoted to describing and give examples of these hints. Consider this example: select t1.
Looking at the query as it has been presented author of the code may have been thinking symbolically of the underlying problem as: t1, t2, subquery t5 , t3 , t4 Take t1, join to it the result of applying the subquery to t2 and joining t3, then join t4.
In effect the unnest hint causes Oracle to rewrite the query as: select outer. Conclusion There are literally hundreds of hints available but, as a general guideline, there are only a few that are particularly useful and strategically sound.
Share this: Twitter Facebook. Like this: Like Loading Comments Nava, Thanks, now corrected. Very insightful! Comments and related questions are welcome. Cancel reply Enter your comment here See "Specifying a Query Block in a Hint".
If the view path is specified, the hint is resolved from left to right, where the first view must be present in the FROM clause, and each subsequent view must be specified in the FROM clause of the preceding view. For example, in Example a view v is created to return the first and last name of the employee, his or her first job and the total salary of all direct reports of that employee for each employee with the highest salary in his or her department. By using the global hint structure, you can avoid the modification of view v with the specification of the index hint in the body of view e2.
The global hint syntax also applies to unmergeable views as in Example The hints cause v2 not to be merged and specify access path hints for the employee and department tables.
These hints are pushed down into the nonmerged view v2. Hints that specify an index can use either a simple index name or a parenthesized list of columns as follows:. To use either of these indexes, the query can be hinted as follows:. 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. Also, 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 want to specify a hint for a table in a view or subquery, then the global hint syntax is recommended. See "Specifying Global Table Hints". If you decide, nonetheless, to use hints with views, the following sections describe the behavior in each case. By default, hints do not propagate inside a complex view. For example, if you specify a hint in a query that selects against a complex view, then that hint is not honored, because it is not pushed inside the view.
Unless the hints are inside the base view, they might not be honored from a query against the view. 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. Parallel execution hints in a top-level query override such hints inside a referenced view.
Parallel execution hints on the view in a top-level query override such hints inside a referenced view. With nonmergeable views, optimization approach and goal hints inside the view are ignored; the top-level query decides the optimization mode.
Because nonmergeable views are optimized separately from the top-level query, access path and join hints inside the view are preserved. For the same reason, access path hints on the view in the top-level query are ignored. 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.
This section discusses how to use the optimizer hints. The hints can be categorized as follows:. The hints described in this section let you choose between optimization approaches and goals. The optimizer goal applies only to queries submitted directly. For example, the optimizer uses the query optimization approach to optimize this statement for best throughput:.
For example, the optimizer uses the query optimization approach to optimize this statement for best response time:. In this example each department contains many employees.
The user wants the first 10 employees of department 20 to be displayed as quickly as possible. 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 query optimization approach and optimizes for best throughput.
The RULE hint disables the use of the query optimizer. This hint is unsupported and should not be used. 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. The FULL hint explicitly chooses a full table scan for the specified table.
For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".
Because the employees table has alias e the hint must refer to the table by its alias rather than by its name. Also, do not specify schema names in the hint even if they are specified in the FROM clause. It applies only to clustered objects. The HASH hint explicitly chooses a hash scan to access the specified table. It applies only to tables stored in a cluster. For a description of the indexspec syntax, see "Specifying Complex Index Hints".
If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values. Because the 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. If certain indexes are given as arguments, then the optimizer tries to use some boolean combination of those particular indexes. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.
If the statement uses an index range scan, then Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition. In a partitioned index, the results are in ascending order within each partition. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it.
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 the cost of the final plan. Complex merging can also be used to merge an IN subquery into the accessing statement if the subquery is uncorrelated. This hint lets the user have more influence over the way in which the view is accessed.
You can get personalized Oracle training by Donald Burleson, right at your shop! Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum. Verify experience!
0コメント