Re: Access Paths: Will these produce equivalent results?

At first glance, I thought "no f*cking way!" -- it can't be correct.

However, if you look at the query, since your ColA and ColB, they're both in the GROUP BY. Since they will end up in the resulting dataset, in this case it doesn't matter if they're restricted in the WHERE or in the HAVING clause. The WHERE is applied *before* the aggregation (GROUP BY) and the HAVING is applied *after* the aggregation. Sit down with a pen and paper and a few examples to convince yourself that, in this case, no matter what values you use, it comes out the same way whether you apply the restriction before or after. It doesn't seem right, but it is. Sorry, my set theory skills aren't up to showing a rigorous mathematical proof that it's correct, but a few examples worked out on paper should convince you.

I would think that the SQL Server plan would execute faster than the Oracle one. The Oracle plan's going to do a lot of unnecessary aggregates ( sums() ) and then filter the results. The SQL Server plan won't do the unnecessary sums() as they're filtered prior to the aggregation in the WHERE clause.

"techy lname" <techy789_at_yahoo.com> wrote in message news:3b990a48.0305091041.157ec3e8_at_posting.google.com...
> We are migrating an MS SQLServer database to Oracle, and have come
> across a query that runs significantly faster on SQLServer than
> Oracle. It is an MS Access generated sql that looks like:
>
> select colA, colB, sum(colC), sum(colD)
> from tab1
> where colE = 'somelit'
> group by colA, colB
> having colA = 'somelit' and colB = 'somelit'
>
> When comparing access plans between Oracle and SQLServer, Oracle
> first performs the roll up, then filters the results. However,
> SQLServer applies the "having " predicate to tab1 *first*, then
> performs the summations. In fact for SQLServer, the access path for
> the above query is identical for this one:
>
> select colA, colB, sum(colC), sum(colD)
> from tab1
> where colE = 'somelit' and colA = 'somelit' and colB = 'somelit'
> group by colA, colB
>
> My question is, is this correct behavior? Is SQLServer guaranteed
to
> produce the same results, no matter what's in the tables?
Received on Fri May 09 2003 - 21:56:59 CEST