Re: order by / sorting performance issue

Thank you for the marvelous replies.

I should of said that stats showed that less then 1% of sorts occured on disk. (Sun-420R, D1000, Oracle 8.1.6) So we have plenty of disks (raid 0) and I will review the junk that we have on the disks that contain the temp tablespaces.

We played with sort_area_size and found that we couldn't make it arbitrarily large without mucking the server.

Back to my ineptitute concerning hints I tried the following:

--non indexed / null

select * from carsales_view where make_id=555 order by make_name;

| Operation                  |


------------------------------

| SELECT STATEMENT           |
|  SORT ORDER BY             |
|   NESTED LOOPS             |
|    TABLE ACCESS FULL       |
|    TABLE ACCESS BY INDEX RO|
|     INDEX RANGE SCAN       |


------------------------------

--indexed / not null with hint

select /*+ index(carsales_view.carsales_vehicles_mv test_make_name) */ *
from carsales_view where make_id=555 order by make_name;

| Operation                  |


------------------------------

| SELECT STATEMENT           |
|  NESTED LOOPS              |
|   TABLE ACCESS BY INDEX ROW|
|    INDEX FULL SCAN         |
|   TABLE ACCESS BY INDEX ROW|
|    INDEX UNIQUE SCAN       |


------------------------------

So this time with the confidence knowing that this should work, I got the hint, index and constraint correct.

I also extended this to order by multiple fields by having a compound index and to do descending order by with the index_desc hint.

In the end I add 2 indexes to the web-application and the response time went from not acceptable to acceptable.

Thanks,
James Crockart
http://carsales.com.au/ Received on Mon Jul 29 2002 - 21:02:49 CDT