Advanced Database Management System - Tutorials and Notes: Query optimizer quiz questions and answers in DBMS

Search Engine

Please visit, subscribe and share 10 Minutes Lectures in Computer Science

Monday, 9 November 2020

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:


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

No comments:

Post a comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents