FIT2094 Chap.7 Advanced SQL and Query Optimisation
Advanced SQL and Query Optimisation
Advanced SQL adds set operators, views, outer and recursive joins, multiple relationship roles and built-in functions. Each construct has a row-preservation rule. UNION combines compatible sets and removes duplicates, INTERSECT keeps shared rows and difference excludes membership.
Self-joins use aliases to represent roles, while outer joins retain unmatched rows only when later filter placement does not discard them.
Query optimisation seeks a cheaper way to return the same required result. Relational algebra explains why selective operations and reduced intermediate relations can matter, but SQL duplicate and null semantics must be preserved.
Execution plans and measured workloads provide evidence. Indexes trade storage and write cost for possible read improvements, and views provide reusable semantics rather than automatic speed.
Separate semantic review from performance review. First prove that unmatched rows, duplicates and nulls have the required meaning by comparing the original and revised queries on adversarial data.
Then inspect the plan and measure the actual workload using the same parameters and data distribution. An index is useful only when its selectivity, access frequency and maintenance cost fit that workload. A view can centralise a reliable join or calculation, but it does not excuse ambiguous grain.
Record the before and after result signature with the timing evidence so a faster query cannot silently change which rows the application receives.
What this chapter covers
- 01
UNION, INTERSECT and difference semantics
- 02
Column compatibility and duplicate preservation
- 03
Views as named query boundaries
- 04
Outer joins, self-joins and multiple relationship roles
- 05
Built-in functions and implicit conversion risks
- 06
Relational algebra and equivalent query rewrites
- 07
Execution plans, indexes and evidence-based optimisation
Worked example · free
Preserve every project while counting open tasks
- +2Start from PROJECT because every project must appear, then left join TASK on the project identifier.
- +2Place TASK.status = Open inside the ON predicate so non-open tasks do not match while projects with no open task remain preserved.
- +1Group by the project key and any displayed project attributes required by Oracle.
- +2Count a non-null TASK key rather than COUNT(*) so the null-extended row for a project with no open task produces zero.
- +1Test one project with no tasks, one with only closed tasks and one with multiple open tasks.
Key terms
- Set compatibility
- The requirement that corresponding columns in set-operation inputs have compatible counts, positions and value domains.
- Self-join
- A join of one table under different aliases so rows can participate in distinct roles such as employee and manager.
- View
- A named query that presents reusable relational semantics or restricted columns without necessarily storing the result.
- Execution plan
- The database's chosen operations and access paths for producing a query result.
- Index
- An auxiliary access structure that can accelerate selected reads while consuming space and adding maintenance to data changes.
- Query equivalence
- The condition that two query forms return the same required result across valid states, including duplicates and nulls.
- Selectivity
- The proportion of rows a condition retains, which influences whether an access path can avoid substantial work.
Advanced SQL and Query Optimisation FAQ
When should I use UNION instead of UNION ALL?
Use UNION when the required result is unique membership across either set. Use UNION ALL when repeated rows are meaningful or will be handled deliberately later. Test a value present in both inputs to expose the difference.
Why can a WHERE condition break an outer join?
The unmatched side produces null columns. A WHERE predicate requiring an optional-side value removes those rows, turning the result into inner-join behaviour. Put match conditions in ON when unmatched parents must remain.
Does creating a view make a query faster?
Not automatically. A normal view names a query and can simplify reuse or access control, but the database still evaluates the underlying operations. Inspect the expanded execution plan and workload before claiming a performance benefit.
How should I demonstrate an optimisation?
First prove result equivalence on boundary cases. Then compare execution plans and measured work under a stable dataset. Identify which operation became smaller or disappeared, and include the write or maintenance cost of any new index.
Assessment move
Translate inclusion words into semantics before choosing syntax: both suggests intersection, either suggests union, first but not second suggests difference, and all including those with none suggests an outer join. Build counterexample datasets with duplicate membership, null keys and unmatched parents. For optimisation, save a correct baseline query and expected result.
Inspect its plan, change one feature and recheck equivalence before measuring. Practise explaining why an index, predicate pushdown or rewritten join reduces work rather than merely naming the feature. Include maintenance costs and data distribution in the conclusion. Keep semantic and performance evidence in separate columns so a lower measured cost never obscures an altered row set, duplicate count or null treatment.
