group by without sorting

From: Vadim Ogranovich <vograno_at_arbitrade.com>
Date: Mon, 5 Mar 2001 14:03:41 -0800
Message-ID: <9812fv$93n$1@taliesin.netcom.net.uk>

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 Received on Mon Mar 05 2001 - 16:03:41 CST