Re: order by / sorting performance issue

James

Most likely it's due to a gotcha in some versions of Oracle (can't remember which ones), the hint for an index that you want to Oracle to use to alleviate a sort only works if that column is mentioned in a where clause (mock this up to be a non criteria - my_text_col >= '', my_positive_int_col
>= 0 etc.).

If this isn't the solution can you post the SQL and the explain plan - the group might be able to suggest solutions with more certainty.

Unless you're machine is underpowered or using slow disk or your DB is badly configured (note Richards post about sort area size), you really shouldn't see Oracle stuggle with sorting 6000 rows and the approach to get

Andy

"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.
>
> Now I have added a constraint to this field of "not null" and I have
> indexed it hoping that the sort operation is not required. But the
> explain plan still says that it is.
>
> I tried to provide a hint but this didn't seem to work either.
>
> Is this the way to go in improving my performance? Maybe I have simply
> stuffed up the hint and I should persevere with this idea.
>
> Any ideas about speeding up sorts?
>
> Regards,
> James Crockart
> Carsales.com.au
Received on Mon Jul 29 2002 - 10:39:38 CDT