On Saying Enough Already in SQL

1
On Saying Enough Already!in SQL

• Michael J. Carey / Donald Kossmann

2
Processing STOP AFTER Queries

• Scan-Stop

• outputs the first N tuples of its input stream
• cost estimate

assumptions
3
Processing STOP AFTER Queries

• Sort-Stop

• outputs the sorted N top or buttom tuples
• cost estimate

Cost of generating the whole input stream for the
Sort-Stop operator
Cost of testing (ALL-N) tuples against heaps
membership bounds
Cost of inserting i qualifying tuples into a heap
with at most N elements
assumption
4
Conservative STOP Placement

• Principle

Never insert a Stop operator at a point in a plan
where its presence can cause tuples to be
discarded that may be required to compose the
requested N tuples of the query result.

• Example

Stop(10)
SELECT FROM Emp e, Dept d WHERE e.works_in
d.dno AND d.function research ORDER BY
e.salary DESC STOP AFTER 10
Join
Dept
Stop(10)
Emp
5
Agressive STOP Placement

• Principle

Insert Stop operators in query plans wherever
they can provide a beneficial cardinality
reduction.

• Cardinality for Stop operator

STOP should reduce the cardinality of the tuple
stream in proportion to the optimizers estimate
of the overall cardinality
But How do we determine a new cardinality in
case of a restart?
paper predetermined factor (e.g. a factor of 2)
though their cost will be lower since much
of the relevant data will still be in the buffer
pool.
each one (of the restarts) requires
re-scanning the Emp table.
6
Discussion

• What makes a SQL Query cheaper when using
  Stop-Statements

• Running time and optimality What is better, what
  is still expensive

• How would a SQL query look like, if we wanted to
  apply an algorithm we discussed before?

• Where in a query plan could we apply the FA, TA,
  or MPro

• What is the papers contribution compared to the
  papers we read before? What does the paper leave
  out?