|
|
Oracle 8
tuning: Oracle 8 and Expert Systems Technology
Oracle Tips by Burleson Consulting |
Oracle 8 and Expert Systems Technology
Rule-Based
Optimizer Quiz
Question:
What is
true about the rule-based optimizer? (Choose all that apply)
-
The rule-based
optimizer relies on statistics from the ANALYZE command.
-
The rule-based
optimizer chooses indexes based in their selectivity.
-
The rule-based
optimizer requires careful sequencing of items in the FROM and
WHERE clause.
-
The rule-based
optimizer is sometimes faster for complex multi-table equi-joins.
Correct Answer:
C, D
Explanation:
The correct
answers are C, The rule-based optimizer requires careful sequencing
of items in the FROM and WHERE clause, and D, the rule-based
optimizer is sometimes faster for complex multi-table equi-joins.
These are two very important points to remember about rule-based
optimization. Question: What is true about the rule-based
optimizer? (Choose all that apply) A is incorrect because only the
cost-based optimizer employs statistics. B is incorrect because the
programmer must indicate selectivity by the sequence of items.
Question:
What was
listed as a tuning step for rule-based queries?
-
Ensure that an
index exists for the most selective item in the WHERE clause.
-
Place the table
that returns the largest number of rows last in the FROM clause.
-
Place the
Boolean conditions in order from most selective to least
selective.
-
Analyze the
table to get the proper statistics for the optimizer.
Correct Answer:
A
Explanation:
The correct
answer is A, ensure that an index exists for the most selective item
in the WHERE clause. This is because this determines which index
will be invoked. Question: What was listed as a tuning step for
rule-based queries? B is incorrect because the driving table should
be last, not first. C is incorrect because Booleans should be from
least selective to most selective. D is incorrect because the
rule-based optimizer does not use statistics.
Question:
What was
listed as a technique for force the use of an index when the
rule-based optimizer makes a poor choice? (Choose all that apply)
-
Invalidate the
index by placing a data type mismatch in the WHERE clause
-
Use and INDEX
hint
-
Re-sequence the
Booleans in the WHERE clause to place the desired index column
last in the WHERE clause.
-
Get index
statistics with the ANALYZE INDEX command.
Correct Answer:
A, B, C
Explanation:
The correct
answer are A, Invalidate the index by placing a data type mismatch
in the WHERE clause, B, Use and INDEX hint, and C, Re-sequence the
Booleans in the WHERE clause to place the desired index column last
in the WHERE clause. This is because these are all valid ways to
force the use of the most selective index. Question: What was
listed as a technique for force the use of an index when the
rule-based optimizer makes a poor choice? (Choose all that apply) D
is incorrect because rule-based optimization does not use
statistics.
|
If you like Oracle tuning, see the
book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |