Re: How to optimize large ORDER BY queries

On 20 Jul 2001 12:58:27 -0700, barry_frank_at_yahoo.com (Barry Frank) wrote:

>Can anyone offer a technique for the following scenario:
>
>I need to set up a "ticket" table that will serve as a queue. The
>query that gets tickets off the table needs to ORDER the records so
>that the tickets are processed FIFO. It is a composite ORDER BY
>clause, using CREATED_DAY, PRIORITY, and TICKET_ID.
>
>At times, records will backlog (to the tune of hundreds of thousands).
>When this happens, the dequeueing query takes too long, plus processor
>usage maxes out at 100%.
>
>My question is: can I somehow set it up so that the table's records
>are physically sorted, so I can simply use ROWNUM = 1 to pull the
>record I want? Or is there any technique I can use that specifically
>optimizes ordering? I need to use the index that affects the filter;
>i.e. the WHERE clause (on different fields). So an index on the ORDER
>BY fields is not helpful.
>
>Any help is appreciated.
>
>- Barry Frank

The rows will be returned in an order dependent on the index used to satsify the query, so it would not matter what you did with the table.

The sort could be speeded up by increasing sort_area_size e.g to 10M (alter session set sort_area_size=10485760).

If the where clause is an equality condition, then you could make a composite index on ( where_clause_columns, created_day, priority, ticket_id ) and order by where_clause_columns, created_day, priority, ticket_id which should use the index to avoid the need to sort - you may need to use an optimizer hint to force this.

If you are only interested in the next record to be processed and you delete it after processing, i.e. you have a set of dequeue processes that process one item at a time, it may be quicker to simply get all records using an index on created_day, priority, ticket_id and filter the resulting records in the dequeue process or possibly with a query of the form:

select ... from
( select ... from ticket order by created_day, priority, ticket_id ) orderedticket
where <filter criteria>

I've not tried this query to see if it works.

Be careful of optimiser statistics. One of the problems I've had in the past is generation of statistics when a table like this was empty which screws up the access path when you have a backlog.

PS how long is too long for this query? Received on Sat Jul 21 2001 - 04:16:37 CDT