Re: Speeding ORDER BY output

Try setting the sort_area_size in init.ora higher. I use around 1Mb. The default is 64k. This is
used when sorting. If you set sort_area_retained_size to 64k then bu default all sessions will
get the default 64k but if needed can go upto 1Mb before having to dump to the temp tablespace.
Also ensure that the user has the temp tablespace set. Make the temp tablespace temporary and
that it's extents are a multiple of sort_area_size + db_block_size and pctincrease 0 i.e. reduce fragmentation.

init.ora
SORT_AREA_SIZE = 1048576
SORT_AREA_RETAINED_SIZE = 65536 restart database

in sql as sys or system user:

create tablespace my_temp_ts temporary
datafile 'your location here' size 100M (size upto you)
default storage ( initial 2052k next 2052k pctincrease 0 ) ;  assumes 4k block size)

alter user my_user temporary tablespace my_temp_ts;

hope this helps,
Dan

Franz Mueller <franz.mueller_at_orbis.de> wrote in message news:37bd1de5.10084170_at_news.salink.net...
> Hi,
>
> I have got the following prob:
>
> In an app the user can define the filters he wants to select rows from
> a database. As the filter can be very non-restrictive
> (WHERE NAME Like 'A%', which returns 20000 rows), we try to reduce the
> traffic and improve the performance in the way that we set
> MAXRECORDS-property in our programming environment. This has the
> effect that our programm cuts the result set after let's say 1000
> rows. The problem is that our SQL looks like this:
> SELECT .. FROM .. WHERE NAME Like '...' ORDER BY NAME
> Since the 20000 rows have to be sorted be fore they can be cutted to
> 1000 by the app I tried to use the /*+ FIRST_ROWS */ hint to speed the
> out put of the 1000 first rows. The problem is that this does not work
> well (of course there is an index on NAME). The funny thing is that if
> I remove the ORDER BY clause, the output is very fast AND ordered by
> the right column (since ORACLE uses the NAME index to access the
> table).
> Does anybody know why /*+ FIRST_ROWS */ does not work properly in this
> case ?
>
> Has anyone other ideas how to handle the prob a users that try to get
> a huge answer set? (ordered!) and then complain about the poor
> performance?
>
> Thanks
> Franz
Received on Sat Aug 21 1999 - 09:32:28 CDT