250+ TOP MCQs on Choice of Evaluation Plans and Answers

RDBMS Multiple Choice Questions on “Choice of Evaluation Plans”.

1. A ________ explores the space of all query evaluation plans that are equivalent to a given query.
a) Cost based optimizer
b) Space based optimizer
c) Time based optimizer
d) None of the mentioned

Answer: a
Clarification: A cost based optimizer explores the space of all query evaluation plans that are equivalent to a given query, and chooses the one with the least estimated cost.

2. What is the disadvantage of cost based optimizers?
a) It is too expensive
b) It is inefficient in producing results
c) It does not perform the desired function
d) None of the mentioned

Answer: a
Clarification: Cost based optimizers may be expensive as exploring space of all possible plans might prove to be costly.

3. A particular sort order is said to be _________ sort order if it could be useful for a later operation.
a) Interesting
b) Reusable
c) Efficient
d) Good

Answer: a
Clarification: A particular sort order is said to be interesting-sort order if it could be useful for a later operation.

4. The rule that allows transformation of a logical operation to a physical operation is called
a) Logical equivalence rule
b) Physical equivalence rule
c) Memory equivalence rule
d) None of the mentioned

Answer: b
Clarification: The rule that allows transformation of a logical operation to a physical operation is called as a physical equivalence rule. It is a new class of equivalence rules.

5. State true or false: Making multiple copies of the same sub-expressions must be avoided
a) True
b) False

Answer: a
Clarification: For promoting space efficiency, making multiple copies of the same sub-expressions must be avoided.

6. Optimizers use __________ to reduce the cost of optimization.
a) Analyzers
b) Statistics
c) Heuristics
d) Caches

Answer: c
Clarification: Cost based optimizers may be expensive as exploring space of all possible plans might prove to be costly. So, optimizers use heuristics to reduce the cost of optimization.

7. The join orders where the right operand of each join is in one of the initial relations are called as ________
a) Right deep join orders
b) Left deep join orders
c) Outer join orders
d) None of the mentioned

Answer: b
Clarification: The join orders where the right operand of each join is in one of the initial relations are called as left deep join orders.

8. Caching and reuse of query plans is called as ________
a) Query caching
b) Plan caching
c) Plan memorizing
d) None of the mentioned

Answer: b
Clarification: Caching and reuse of query plans is called as plan caching. It is used because the optimal plan for the new constants may differ from the optimal plan for the initial values.

9. What technique is used for the evaluation of a query with a nested sub query?
a) Caching
b) Decorrelated evaluation
c) Correlated evaluation
d) Time based evaluation

Answer: c
Clarification: The technique used to evaluate a query with a nested sub query is called as correlated evaluation. It is not very efficient as a large number of I/O operations may occur.

10. The process of replacing a nested query with a query with a join is known as _________
a) Correlation
b) Decorrelation
c) Cache handling
d) Join replacement

Answer: b
Clarification: The process of replacing a nested query with a query with a join is known as decorrelation. It is more complicated when the nested sub query uses aggregate functions inside it.