Which three statements are true about the usage of optimizer hints?
A. Whenever a query uses table aliases, the hints in the query must use the aliases.
B. The OPTIMIZER_FEATURES_ENABLE parameter must be set to a version supports the hints used.
C. The optimizer uses the execution plan with lower cost even if a hint is specified.
D. A schema name for the table must be used in the hint if the table is qualified in the FROM clause.
E. Hints can be used to override the optimization approach specified with the OPTIMIZER_MODE parameter.
F. A statement block can have only one hint, and that hint must be immediately after SELECT,
UPDATE, INSERT, MERGE, or DELETE keyword.
Correct Answer: ACE
Explanation/Reference:
--MI DAVA A,E--
A: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.
E:If a SQL statement has a hint specifying an optimization approach and goal, then the optimizer uses the specified approach regardless of the presence or absence of statistics, the value of the OPTIMIZER_MODE initialization parameter, and the OPTIMIZER_MODE parameter of the ALTER SESSION statement.
Note:
*Optimizer hints can be used with SQL statements to alter execution plans.
*Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know. Hints provide a mechanism to instruct the optimizer to choose a certain query execution plan based on the specific criteria.
For example, you might know that a certain index is more selective for certain queries. Based on this information, you might be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to instruct the optimizer to use the optimal execution plan.
*Hints apply only to the optimization of the block of a statement in which they appear. A statement block is any one of the following statements or parts of statements:
A simple SELECT, UPDATE, or DELETE statement
A parent statement or subquery of a complex statement
A part of a compound query
Reference:Oracle Database Performance Tuning Guide,Using Optimizer Hints
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
