Re: about 7.0 LIMIT optimization

Hi,

> I'm not sure about your comment about referential constraints. If you
> are doing analysis of restriction clauses to prove that a particular
> stage doesn't require reading as many rows as it otherwise would, then
> you've done more than I have.

Yes, that's what we do. Here is a clarifying example:

-----
"Retrieve name, salary and Dept name of the 10 most paid employees"

SELECT Emp.name, Emp.salary, Dep.name
FROM Emp, Dep
WHERE Emp.dno=Dept.dno
STOP AFTER 10
RANK BY Emp.salary DESC;
-----

Suppose you have a referential constraint like:
Emp->dno --> Dep.dno (foreign --> primary)

In this case we can do :

join (Emp.dno = Dep.dno)
Stop 10
Scan Emp
Scan Dept

since we are sure that every employee works in a departement (because of the constraints), so the 10 most paid employees will survive after the join. In this way you can reduce the cardinality of one of the input stream of the join, obtaining the same final results.

Note that this is a very simple case. In many plans involving a larger number of joins you can place a Stop operator in a deep position, reducing the work of all the following joins.

We have formalized a set of rules which allow us to determine wheter or not a position in the plan for the Stop operator is safe and then we have developed a fast algorithm able to take the right decision.

Regards

R. Cornacchia
A. Ghidini
Dr. P. Ciaccia

===========================================================

VIRGILIO MAIL - Il tuo indirizzo E-mail gratis e per sempre
http://mail.virgilio.it/

VIRGILIO - La guida italiana a Internet
http://www.virgilio.it/