Access Paths: Will these produce equivalent results?
From: techy lname <techy789_at_yahoo.com>
Date: 9 May 2003 11:41:50 -0700
Message-ID: <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:
where colE = 'somelit' and colA = 'somelit' and colB = 'somelit' group by colA, colB
Date: 9 May 2003 11:41:50 -0700
Message-ID: <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)
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 - 20:41:50 CEST