Query optimizer quiz questions and answers in DBMS

Query optimization process in DBMS, solved quiz questions in DBMS, query plan, query processing

 

Optimal query plan - Query optimizer

Q1) Which of the given conditions would the query optimizer most likely decide to execute first (e.g., as one of the bottom-most nodes in the query plan) if it wants to generate an optimal query plan for the SQL query given below? Note: Assume that additional information regarding the statistics of relations R, S and T are not given.

SELECT * FROM R, S, T WHERE R.r = S.s AND S.id = T.id AND R.no = 102 AND S.price > 100;

a) R.r = S.s

b) S.id = T.id

c) S.price > 100

d) R.no = 10

Answer: (d) R.no = 10 Typically, the best choice of bottom-most node is the one that will reduce the size of the input relation. Since the predicate “R.no = 10” is most selective, option (d) will be the most preferred one. Clearly neither of the multi-table join predicates (options (a) and (b)) would be a good choice to run first; option (c) may end up with more records. If enough statistics provided, then either of options (c) and (d) would be best. If the number of records resulted from applying the condition S.price>100 is very much less than the result produced by R.no=10, then option (c) would be best or vice versa.

 

************************

Related posts:

• Go to Multiple Choice Questions in DBMS home

• Go to Normalization - MCQs page

• Go to General Mixed Quiz in all topics of DBMS page

• Go to Advanced DBMS Concepts page

Quiz questions with answers on DBMS introduction concepts

find the optimal query plan for a given SQL query

How does query optimizer work in DBMS