Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: GROUP BY ...speed issue
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
FREQ_COUNTER_1996 is clearly a continuously-increasing table.
The problem is that you are selecting from and inserting into it in the
same statement and the version you are selecting from is being
continuously updated by the insert.
As with all such "reflexive" statements, each row inserted requires the
creation of a rollback segment entry and the re-reading of all rollback
segments created previously during the operation (the price you pay for
read consistency).
Very roughly, the time taken will increase as the square of the number
of rows in FREQ_COUNTER_1996.
In addition, you are in danger of encountering the dreaded "snapshot too
old" message.
Recommendation:
Insert the new values into a temporary table and then use this table to
insert into FREQ_COUNTER_1996 after you have closed the cursor.
-- Chrysalis FABRICATI DIEM, PVNC ('To Protect and to Serve') Terry Pratchett : "Guards Guards"Received on Wed Oct 08 1997 - 00:00:00 CDT