Re: order by / sorting performance issue

From: Stephan Bressler <stephan.bressler_at_siemens.com>
Date: Mon, 29 Jul 2002 15:46:50 +0200
Message-ID: <ai3h05$qgq$1@news.mch.sbs.de>

Hi James,

there are several reasons I can think of. First, in case of the "order by" the entire result set need to be retrieved, sorted and the displayed. For sorting a big sort_area_size will help (btw, what is your setting?). Next, the CBO might choose a full scan in case of "order by" (up to a possibly large HWM) opposed to an index access.

What is the difference between "quickly" (unsorted) and "very slow" (order by).

Please add your sort settings and execution plans if you need more help.

Regards
Stephan

"James" <thanatic_at_telstra.com> wrote in message news:7377f06c.0207290341.53008811_at_posting.google.com...
> Hello world,
>
> James here.
>
> Has anyone got any tips in sorting a wide table?
> I am very dissatisfied with the present performance and I am looking
> for some answers.
>
> The base table is only about 40000 records and a where clause drives
> it down to 6000. An unsorted qry returns the result very quickly but
> an order by (on an unindexed varchar2) is very slow.
>

snip Received on Mon Jul 29 2002 - 08:46:50 CDT