Re: Is SQL procedural or non-procedural?

"J M Davitt" <jdavitt_at_aeneas.net> wrote in message news:mLTig.56798$mh.25385_at_tornado.ohiordc.rr.com...
> kumar.vaibhav.jain_at_gmail.com wrote:
> > Hi,
> >
> > I am confused on this issue that wether SQL is procedural or non
> > procedural. What are your views on this issue?
> >
> >
> > Thanks,
> >
> > VJ
> >
>
> The unhelpful reply is, "Neither."
>
> SQL is intended to be declarative; outside of syntactic
> requirements, the expressions used to describe a given
> set of rows and the order of those expressions should not
> matter. For the most part, that's true.
>
> A notable (and troublesome) exception is found in the
> CASE construction: one has to be careful that the
> conditions to be evaluated are presented in the "correct"
> order.
>
> Although not a language detail, one often hears that
> "performance can be improved" by writing SQL "differently."
> These recommendations usually amount to, "Get all the /x/
> rows before joining to the /y/s and /z/s; it's faster."
> The whole exercise seems procedural. But fact that
> equivalent statements "perform" differently indicates a
> poor (or biased) physical design, poor administration, or
> a poor implementation.

It can also indicate a blind spot in the optimizer.

Back in version 7, Oracle had a "rule based optimizer" that was a primitive attempt at optimizing,
based on the SQL expression itself, and not on the cost estimate of the selected strategy.

During that time, all sorts of habits grew up in the community of Oracle programmers, dedicated to
getting the Oracle engine to discover a faster access path to the desired result. This somewhat defeated the prupose of having a language like SQL.

By contrast, some other products had cost based optimization, and the programmers could focus on what data they wanted, instead of how to access it. Received on Sun Jun 11 2006 - 14:36:51 CEST