order by / sorting performance issue

From: James <thanatic_at_telstra.com>
Date: 29 Jul 2002 04:41:35 -0700
Message-ID: <7377f06c.0207290341.53008811@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 - 06:41:35 CDT