Comment by lukaseder | Hacker News Reader

Comment by lukaseder | Hacker News Reader

Parent Full thread

Me again - I really like the idea. I wish I had something like that at a previous job, where queries ran against some 10-level nested views, involving tons of unnecessary joins, projections, unions, sorts, distincts, and what not.

I'm pretty sure that Oracle figured it out all correctly, so the excessive SQL wasn't causing performance issues, but from a maintainability perspective, it would have been nice to have a tool that was able to reduce arbitrary SQL to a more minimal, semantically equivalent alternative.

Have you thought about this?

Exactly, SQL minimization is a real problem. While there is no canonical form of all SQL in general, it is still possible to reduce Query.

There is some existing work. You might want to look at this one: https://pdfs.semanticscholar.org/c243/25d76c3ba91388e16085c1...

One problem is that bag semantic chase is very complicated to implement. We are actually working on a new chase algorithm on our SQL formalization right now.

I'm sure bags cause some trouble, but I had something much "simpler" in mind. For instance, JOIN elimination is a powerful tool implemented by most modern RDBMS' optimisers.

Take this query:

    SELECT c.*
    FROM customer c
    JOIN address a
    ON c.address_id = a.address_id

With a foreign key on customer(address_id), the above JOIN can easily be proven useless, and thus eliminated. Furthermore, take this query:

    SELECT c.*
    FROM customer c
    LEFT JOIN address a
    ON c.address_id = a.address_id

In this case, we don't even need the foreign key as long as there is a unique constraint on address(address_id).

Reply on news.ycombinator.com