Re: group by without sorting

Are you saying I need to create an index on (id, day) or just (id)? The later didn't work for me. I believe the former should work, but it ammounts to the same sorting when the index is built.

"mel" <jmel_at_mailnews.com> wrote in message news:3aa64a28$0$15806$45beb828_at_newscene.com...
> you can create an index on those columns. Oracle will use th eindexes
 which
> are already sorted. the plan will say group by (nosort)
>
> In article <u748at8vsln4tfk0tofs7904g931aj9vg4_at_4ax.com>,
> postbus_at_sybrandb.demon-verwijderdit.nl wrote:
> >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 Fri Mar 09 2001 - 20:57:11 CST