Re: group by without sorting

On Mon, 5 Mar 2001 14:03:41 -0800, "Vadim Ogranovich" <vograno_at_arbitrade.com> wrote:

>Hi, I'm puzzled why a simple "group by" query needs to use a big sort area,
>and if it's possible to group by without sorting the table.
>
>Say I have a two column table, called 'daily', with columns 'id' and 'day'.
>The table is ~30M rows big, and there are ~20K distinct 'id'. Full scan on a
>table takes less than three minutes, there is no indexes on the table.
>
>I am using the following query to get the "life span" of each id (the query
>breaks after it has been running for six hours):
>
> select id, min(day), max(day)
> from daily
> group by id;
>
>And this is the query plan:
>
>Query Plan
>----------------------------------------------------------------------------
>----
>SELECT STATEMENT Cost =
> SORT GROUP BY
> TABLE ACCESS FULL DAILY
>
>Does this Query Plan mean that Oracle is going to sort the entire table?
>If yes, what can I do to avoid this? If not, do you have any idea why it
>takes so long and so much memory?
>
>Thanks, Vadim
>
>P.S. The Oracle version I use is:
>Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production
>With the Objects option
>PL/SQL Release 8.0.5.2.0 - Production
>
>

Question 1: Yes, of course it is going to sort the affected columns only, it is not stupid.
Question 2: Nothing
Question 3: You should tune your sort_area_size parameter. The default is 64k. I usually set it to 1 M minimum. This parameter can be ALTERed on session level (alter session set sort_area_size = <bytes>) so there is no problem in experimenting.
You should also tune your temporary tablespace. The default storage clause should start where the sort_area_size left off, so at 1 M initial 1M next pctincrease 0). The tablespace should be off type temporary, and in Oracle 8i it should be locally managed.

Hth,

Sybrand Bakker, Oracle DBA Received on Mon Mar 05 2001 - 16:24:35 CST