Re: GROUP BY ...speed issue

From: Kim Ng <kimmng_at_eskimo.com>
Date: 1997/10/09
Message-ID: <61irqa$hs8$1_at_eskinews.eskimo.com>#1/1


Hi,

First of all, hi Mike, are you the same Mike whom I worked with at US West NewVector? This is Kim Ng. If you are, how're you doing? Need any consultant ? ;)

Now, back to the question, have you checked the number of extents (tables, indexes and database)? Also, how full is your database and disks, how many indexes are there on the table you are inserting into and how many records are you inserting? Of course, I am assuming that you have tuned your "select" to its optimum either using TKPROF or EXPLAIN PLAN.

You may also want to find out how big are the rollback segments and which rollback segment the statement was using (if possible). If rollback segment is too small, you will have performance problems as it takes time to get extents! If you have to, create a rollback segment with a HUGE initial size to ensure that all the selected records fit in it and then use that rollback segment for the satement. I don't remember the syntax to use a particular rollback segment, though, so look it up in the manual (I think it is one of the ALTER command). I have personally seen an update that took more than 30 minutes dropped to 2 minutes when a huge rollback segment was used! Since you are doing a GROUP BY you may also want to check out your TEMP tablespace size. I believe ORACLE uses this for sorting.

By the way, I have also heard cases when "IN" is slower than "UNION". I have never encountered that myself, though.

Have fun.

Kim Ng
(kimmng_at_eskimo.com)

In article <876259096.25988_at_dejanews.com> you 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

Original text of this message