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

Question

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?

BlackTom AI · Accepted Answer

Let's restate the scenario and options to begin a careful evaluation of how the plan might behave with the given conditions.

Question restatement: We have a query that selects hotspotID from hotspot where postcode = 2600 AND establishmentYear > 2020 AND establishmentYear < 2022. The only available index is an unclustered B+tree on establishmentYear. With this setup, would the cost of the best plan change when the query is modified as shown?"

Option 1: Yes, because the RF value will change leading to an expensive index scan
- This claim hinges on the idea that the row fraction (RF) or selectivity changes in a way that would push the optimizer to choose a more expensive index scan. However, since the only index available is still the unclustered index on establishmentYear, the fundamental access method available to the optimizer remains the same: use the index to locate rows by establishmentYear range and then apply the remaining predicate (postcode = 2600) as a filter. While the selectivity of the year range (2020-2021) may differ from other ranges, this does not introduce a new access path or a different plan shape. The lack of a composite index on postcode or a clustered arrangement means the plan’s structural choice is unlikely to change solely due to RF variations; the best plan would still rely on the same index range scan plus a filter. In short, RF fluctuations do not automatically mandate a switch to a more expensive plan when no new index opportunities are introduced. This makes the assertion misleading in this context.

Option 2: No, eventually the result size will stay the same.
- This statement asserts that the cardinality of the result is invariant to the change in the year predicate. In reality, narrowing or widening a year range typically changes the number of matching rows. Moving from a broader context to a narrower 2021-bound window (2020 < establishmentYear < 2022) will likely yield a different result set size than other ranges, assuming data distribution over years is not perfectly uniform. Therefore, claiming that the result size will stay the same is incorrect because the predicate directly affects how many rows satisfy establishmentYear in that interval. The statement ignores data distribution and question-specific selectivity, which are central to query cardinality.

Option 3: No, the best plan is still the same.
- This option asserts that, despite the change in the query predicate to a specific year window and given only an unclustered B+tree index on establishmentYear, the optimal execution plan does not change. This aligns with typical scenarios where the available index cannot support additional access methods (no composite index on postcode, no clustering on hotspotID, etc.). The planner would still prefer using the establishmentYear index to fetch candidate rows for the year range and then apply the postcode filter as a residual predicate. Since there is no index on postcode and the index is unclustered, the plan shape remains: perform an index range scan on establishmentYear, retrieve the corresponding row pointers, and then apply the postcode predicate to filter. Therefore, the overall plan structure is expected to remain the same, even though the exact number of I/Os or the runtime might vary with the data distribution and selectivity of the year range.

Summary of reasoning across options:
- Option 1 describes a change in plan due to RF shifting toward a more expensive index scan, but without a new index or different access method available, the optimizer’s plan shape is unlikely to change in response to RF alone.
- Option 2 claims the result size remains constant across year predicates; this ignores the typical impact of selectivity changes on cardinality and incorrect as a general rule.
- Option 3 makes the claim that the best plan remains the same, which is consistent with having a single available index (unclustered on establishmentYear) and no additional index paths; the plan structure would likely not alter just due to the adjusted year bounds, though execution cost may vary with data distribution.

Overall, the reasoning supports that the presence of a single unclustered index on establishmentYear constrains the plan options, and changing the year predicate does not introduce a new access path, so the best plan’s structure remains the same; the cost could fluctuate with data but the plan shape is unchanged."

INFO20003_2025_SM2 Exam: Database Systems (INFO20003_2025_SM2)- Requires Respondus LockDown Browser

View Explanation

Log in for full answers

Similar Questions

Single Relation Plan A Compute the estimated result size for the query, and the reduction factor of each filter. 1: RF(establishmentYear) 2: RF(postcode) 3: Result Size (tuples)

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.

Which one of the following actions will definitely not reduce any query cost?

You have a user_table and a usage_details table. You want to find the rows corresponding to users who live in Nebraska (about 10% of all users) and join that subset with their usage details. Which of the following approaches is generally more efficient?

More Practical Tools for Students Powered by AI Study Helper

Homework AI Solver

Stylized AI Paper Writer

Plagiarism Checker Assistant

Citation AI Academic Writing Tool

In-Class Translation Assistant

AI Note Generator

AI Quiz Answers

Past Exam Questions from University Test Bank

Smart Practice Assistant

Adaptive Practice

Making Your Study Simpler