Hi, Let us take a query like this

[somnath74]

Hi,
Let us take a query like this

1).select * from tab
group by col1,col2
having
and clause 1
and caluse 2
and clause 3
and clause4=MAX(value)

now take this query

2).select * from tab

where
clause 1
and caluse 2
and clause 3

group by col1,col2

having
and clause 1
and caluse 2
and clause 3
and clause4=MAX(value)

Although the results of both the query are the same but the cost of the 2 nd query is lesser.If we check the query plan
what we see is:-
For query 2 the STEP 1 on table tab is a table scan
Now can anyone please throw some light on how the where caluse is basically causing a modification in this query plan?

Thanks in advance

Somnath

 

[mikewoodhouse]

If there are no indexed columns referenced then there will, pretty much by definition, have to be a table scan.

I'm a little doubftul about the validity of the first example and elements of the second (but we all know that Sybase doesn't really care if your SQL is slightly invalid). I've always understood that HAVING is essentially a WHERE clause for aggregate expressions. So that your clauses 1 to 3 in the Having should have the same effect in results terms as those in the Where clause.

But I rather suspect that adding a where clause will cause rows to be filtered out in the initial table scan whereas when the filter conditions are in the Having clause, the whole table has to be evaluated completely before exclusions are made. There's probably a temporary table being used which will be smaller and reduce IOs.

But I'm trying to be logical and this is Sybase so it wouldn't surprise me if I was completely off the mark!

Mike