Re: group by without sorting

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 Wed Mar 07 2001 - 08:51:03 CST