Re: "GROUP BY" really slowing down query.

David Hiltz (dhiltz_at_whsun1.whoi.edu) wrote:
: I'm doing a select statement between two tables:
 

: TAB_A = 60,000 rows
: TAB_B = 400,000 rows
 

: And whenever I add the "group by" clause, that you see
: below, it slows the query down to nothing. Without the
: "group by" clause the answer comes back in a few seconds.

: select TAB_A.year, TAB_A.lat, TAB_A.lon,
: TO_CHAR (sysdate,'fmdd-Mon-yy') Today
: from TAB_B,TAB_A
: where TAB_A.area = 513 or TAB_A.area = 514
: and TAB_B.month = TAB_A.month
: and TAB_B.docn = TAB_A.docn
: group by TAB_A.year, TAB_A.lat, TAB_A.lon;

: All columns involved in the query are indexed. I moved things around
: in the query, but nothing seems to work.

Firstly, I noted that you only select columns from TAB_A and that you have no group expression (SUM, AVG, etc.) in the SELECT clause. That is fine but I think SELECT DISTINCT ... would be nicer instead of the GROUP BY.

However, your performance problem stems from the OR in the WHERE clause. When executing your statement Oracle will interpret it as

   "where TAB_A.area = 513 or (TAB_A.area = 514 and ...)", but you really intended

   "where (TAB_A.area = 513 or TAB_A.area = 514) and ..."

Therefore, add paranthesis to the first line of the WHERE clause. You will see a big improvement in speed and you will get the correct result as well!

--
Michael P. Alstrup
mpa_at_avalon.qdot.qld.gov.au