Questions
Questions

COMP90050_2025_SM2 Quiz 2

Multiple choice

Select all the options from the following that are steps or tasks of a query optimizer. You need to choose multiple options if they are the answers.

Options
A.Generate different alternative query plans
B.Estimate the costs of the alternative query plans
C.Generate logically equivalent expressions of the query
D.Choose the cheapest plan based on estimated cost
View Explanation

View Explanation

Verified Answer
Please login to view
Step-by-Step Analysis
To approach this question, I will evaluate what a query optimizer typically does and map each option to that process. Option 1: Generate different alternative query plans. This is a core activity of the optimizer: it explo......Login to view full explanation

Log in for full answers

We've collected over 50,000 authentic exam questions and detailed explanations from around the globe. Log in now and get instant access to the answers!

Similar Questions

Query Optimisation Plan 2  Compute the following results for the below Plan 2:  The result size of the selection on vendor in pages (marked as 'A' in the diagram) The result size of the child join in pages (marked as 'B' in the diagram) The cost of the selection on vendor in I/Os (note this also accounts for the index access cost as well) (marked as 'C' in the diagram) The cost of the child join in I/Os (marked as 'D' in the diagram) The cost of the parent join in I/Os (marked as 'E' in the diagram)   Figure 3.3.2: PLAN 2. 1: The result size of the selection on Vendor in PAGES (marked as 'A' in the diagram) 2: The result size of the child join in PAGES (marked as 'B' in the diagram) 3: The cost of the selection on Vendor in I/Os (note this also accounts for the index access cost as well) (marked as 'C' in the diagram) 4: The cost of the child join in I/Os (marked as 'D' in the diagram) 5: The cost of the parent join in I/Os (marked as 'E' in the diagram)

Query Optimisation Plan 1  Compute the following results for the below Plan 1:  The result size of the child join in pages (marked as 'A' in the diagram) The cost of the child join in I/Os (marked as 'B' in the diagram).  The cost of the parent join in I/Os (marked as 'C' in the diagram)     Figure 3.3.1: PLAN 1. 1: The result size of the child join in PAGES (marked as 'A' in the diagram) 2: The cost of the child join in I/Os (marked as 'B' in the diagram) 3: The cost of the parent join in I/Os (marked as 'C' in the diagram)

Single Relation Plan C What would happen if our query changed and became:  SELECT hotspotID FROM hotspot WHERE postcode = 2600 AND establishmentYear > 2020 AND establishmentYear < 2022; Assuming that the unclustered B+tree index on establishmentYear from the previous question is the only index available, would the cost of the best plan change? 

Single Relation Plan B Compute the estimated cost of plan alternatives, assuming that an unclustered B+tree index on (establishmentYear) is the only index available. Suppose there are 10 index pages.  Give the lowest (estimated) cost in I/Os after considering all access methods available.  Round up any decimals to the nearest integer (e.g., 3.3 rounds up to 4).

More Practical Tools for Students Powered by AI Study Helper

Join us and instantly unlock extensive past papers & exclusive solutions to get a head start on your studies!