Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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) loop
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 CDT