Explore topic-wise InterviewSolutions in .

Right answer is (d) PARAMETRIC QUERY optimization

Easy EXPLANATION: If a query is optimized without providing specific VALUES for its parameters the technique is called parametric query optimization.

The correct CHOICE is (b) Multi QUERY optimization

For explanation: REDUCING the complexity of complex queries by similarly handling sub-queries is KNOWN as multi query optimization.

Correct ANSWER is (c) Halloween problem

For explanation I would SAY: The problem of an update affecting the EXECUTION of a query associated with the update is known as the Halloween problem. This was because it was first recognized on the Halloween day at IBM.

The correct CHOICE is (B) Workload

The BEST I can explain: The sequence of queries that reflect the typical workload on the SYSTEM are known as workload.

Right ANSWER is (d) More than one of the mentioned

The best I can explain: The changes MADE to a relation or and expression are known as DIFFERENTIALS in materialized VIEW MANAGEMENT.

Right option is (d) All of the mentioned

The best I can explain: Incremental, immediate and DEFERRED are all different types of MATERIALIZED VIEW management TECHNIQUES. Most database systems perform immediate view management.

Correct CHOICE is (b) Modifying only the affected parts of the view

Explanation: Modifying only the affected parts of the materialized view is called as INCREMENTAL view maintenance. It is MUCH more efficient than ORDINARY view management.

The correct choice is (b) View maintenance

Best explanation: The TASK of KEEPING a view up to DATE with the UNDERLYING DATA is called as view maintenance.

Correct choice is (c) Materialized VIEW

Explanation: A view whose CONTENTS are computed and STORED is called as a materialized view. I does not only STORE the QUERY defining the view.

Right answer is (b) Decorrelation

Easiest explanation: 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.

The correct CHOICE is (c) Correlated evaluation

For explanation I would SAY: 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.

Correct option is (b) Plan caching

Easy explanation: 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.

Correct ANSWER is (c) Heuristics

Explanation: 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.

Correct answer is (B) Physical equivalence rule

For explanation I WOULD say: 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.

Correct OPTION is (a) Interesting

For explanation: A particular sort order is SAID to be interesting-sort order if it could be useful for a LATER operation.

Right answer is (a) It is too EXPENSIVE

The explanation: Cost based optimizers MAY be expensive as exploring space of all possible PLANS might PROVE to be costly.

Correct option is (a) Cost based optimizer

Explanation: 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.

Correct answer is (c) Aggregation

Explanation: The SIZE of an aggregation is simply V(A,r) where r is the relation and A is a DISTINCT value SINCE there is one tuple in the aggregation for every distinct value in A.

Right choice is (d) All of the mentioned

The best explanation: Size estimation can be done for projection, aggregation, set operations, join operations and ALSO the SELECTION OPERATION.

Right option is (b) False

To explain: Estimation of the RESULT of a join is possible and can be done USING the Cartesian product and intersections of the two relations. It is more complicated than estimating SIZE of a selection.

Right CHOICE is (a) Conjunctive selection

The EXPLANATION: The intersection of all records satisfying the individual SIMPLE CONDITIONS Oi is called as conjunctive selection. It is a form of a complex selection.

Correct CHOICE is (B) Disjunctive selection

To EXPLAIN: The union of all RECORDS satisfying the INDIVIDUAL simple conditions Oi is called as disjunctive selection. It is a form of a complex selection.

Correct CHOICE is (b) Analyze

For EXPLANATION I would say: The analyze command is USED to generate STATISTICS on a PARTICULAR set of relations. The runstats command is also valid but not in SQL. It is used in IBM DB2.

Correct option is (a) A random SAMPLE

The BEST I can explain: A random sample must be taken into consideration for STATISTICAL analysis because it gives a fair result on the DATA analysis.

Correct answer is (b) Divides range of values into EQUAL sized RANGES

To explain I WOULD say: The equi width HISTOGRAM divides the range of values into equal sized ranges.

Correct choice is (a) Histogram

The EXPLANATION is: Most of the DATABASES store the DISTRIBUTION of values for each attribute as a histogram.in which the values for the ATTRIBUTES are divided into various RANGES.

The CORRECT answer is (a) True

For EXPLANATION I would say: Multiple EQUIVALENCE rules can be used one after the other on a QUERY or on parts of the query. This can be used to simplify the query greatly thus reducing its time COMPLEXITY.

Correct answer is (d) All of the mentioned

The best explanation: The SELECTION OPERATION distributes over SET union, intersection, set difference.

Correct option is (c) Minimal

Explanation: A set of EQUIVALENCE RULES is said to be minimal if no rule can be DERIVED from any COMBINATION of others. Query optimizers use minimal sets of equivalence rules.

The correct answer is (d) Wait()

EXPLANATION: The ITERATOR does not provide the wait() FUNCTION but it provides OPEN(), NEXT() and close() operations.

The CORRECT answer is (a) True

The explanation is: Hybrid HASH JOIN is partially pipelined on the probe relation since it can output tuples from the first partition as tuples are RECEIVED for the probe relation.

Right choice is (d) None of the mentioned

Explanation: Pipelined join TECHNIQUE, both left and right are EVALUATION techniques that can be USED to organize DATA.

Correct answer is (b) False

The explanation: JOIN is not an INHERENTLY blocking algorithm. This is because all the tuples NEED not be EXAMINED to execute the join operation.

The CORRECT answer is (a) True

Explanation: SORTING is an INHERENTLY blocking OPERATION i.e. output is not given until all the tuples from their input are EXAMINED

The correct OPTION is (b) Producer driven pipeline

Explanation: If the operations do not WAIT to PRODUCE tuples, then it is called as producer driven pipeline. IT does not wait to produce tuples but it PRODUCES tuples eagerly.

Correct choice is (c) Materialized evaluation

To explain I would say: The RESULT of each intermediate operation are created and then are USED foe valuation of the next level operations, this evaluation is CALLED as Materialized evaluation. This is not space effective as we need to construct the temporary relations which must be written to a DISK.

The CORRECT choice is (b) Pipeline

The best I can explain: If the results of one operation are passed on to the other, it is called as PIPELINED. This can be used to EVALUATE several expressions SIMULTANEOUSLY.