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

Why views are important in database management system?

Views in database management system, MCQ in DBMS, solved quiz questions in DBMS, purpose of database views

 

Q1) Why views are important in database management system?

a) They improve query execution efficiency

b) They help with access control by allowing users to see only a particular subset of the data in the database.

c) They help users to see entire database without any complexities

d) They provide data independence

Answer: (b) and (d) As view is a stored query, it restricts users from seeing the entire table. Even the actual table name is hidden from end users. Views allow programs to be independent of the actual physical layout of the data What is database view? A database view is a subset of a database and is based on a query that runs on one or more database tables. Database views are saved in the database as named queries and can be used to save frequently used, complex queries. A view is simply any SELECT query that has been given a name and saved in the database. For this reason, a view is sometimes called a named query or a stored query.

 

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

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

Advantage of database views