Databases Reference
In-Depth Information
The execution plan of this query is shown in the following screenshot:
We can see that there is a
SORT
GROUP
BY
operation after the full table scan of the
CUSTOMERS
table.
The query in step 9 executes the same request, but this time the predicate is in the
WHERE
condition and not in the
HAVING
clause. This is correct, because we want to filter the values
before grouping. The
HAVING
clause is used when we want to impose a condition on the
aggregated values.
If the
HAVING
clause is misused, the database engine has to elaborate the
GROUP
BY
operation on a wider data set. We have seen that a
SORT
GROUP
BY
operation is required,
and the less the data is elaborated by the sort the better the performance will be.
In the following screenshot, we can see the execution plan for this query, which endorses
this reasoning—we have only 32 consistent gets (instead of 1459) and we can use the
bitmap index instead of a full table scan of the
CUSTOMERS
table.
