Re: GROUP BY ...speed issue

I would try doing this in a 'for cursor loop'. Because of your query it should speed it up substantially.
My syntax might be a little off but you get the jist of it.

Hope this helps.

ie
  for r in (

               select rowid, c.qname, c.qvalue, count(*)
               from system.recipient R, freq_counter_1996 C
               WHERE r.qname=c.qname and
               r.response in (c.qindex, c.qvalue)
               group by c.qname, c.qvalue

  INSERT INTO TEMP_FREQ_COUNTER_1996
  (QNAME, QVALUE, COUNT)
  values (r.qname, r.qvalue, r.count)

  if MOD(r.rowid, 100) = 0 commit;

 end loop;

schaffner_at_pewter.chrr.ohio-state.edu wrote:

> The following is the SQL statements that use to run in about 20
> minutes. The database hasn't changed size...
>
> INSERT INTO TEMP_FREQ_COUNTER_1996
> (QNAME, Extention, QVALUE, COUNT)
> select c.qname, c.qvalue, count(*)
> from system.recipient R, freq_counter_1996 C
> WHERE r.qname=c.qname and
> r.response in (c.qindex, c.qvalue)
> group by c.qname, c.qvalue;
>
> Now it takes a day and half. I had the indicies rebuilt and still
> slow.
> The dba says that she didn't change anything. Any educated guess what
> may
> have changed to slow this down so much?
>
> -------------------==== Posted via Deja News
> ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
Received on Thu Oct 09 1997 - 00:00:00 CEST