Slow performance selecting next message from custom queue

I have a simple table based queue system. In its simplest form, it consist of an id, a queue name, and a status. When reading the next message from a given queue, we need to ensure FIFO (first in first out), i.e. the lowest id from the given queue with the given status. This all works fine with some thousand rows, but when we reach 1M+ rows it does not go well anymore.

We can't use rownum = 1 as this is done prior to sorting, the sorting is done based on the id column solely (asc). If I make a cursor and sort by id 1000 times this takes around 100ms all together which is a good performance (0.1 ms / loop). If I include the status and queue name in the query (which I need, as I need the lowest id of an unread message for a specific queue) it takes around 1300ms for 10 loo开发者_运维百科ps (130ms / loop), which is far from ok.

I have tried having an index on each of the three columns, also a combined index on id, queue, status and finally a combination with an index on id, and a combined index on queue and status. The id column is also the primary key. All combination has been tried in a rule based setup also (using the rule hint).

Best regards, Michael Ringholm Sundgaard - iHedge A/S www.ihedge.dk www.ibrain.dk

---

One thing that I didn't see mentioned in the indexes which you tried would be an index on (queue, status, id). If you put the id at the start of your index it mostly destroys the use of the index since you're looking for the "lowest one", which is meaningless until the other criteria is applied.

The ordering of the columns in an index can often be just as important as the actual columns themselves.

---

The general idea is:

select id from
(select id
   from queue_table
   where queue_name = 'nameOfQueue'
   and processed = 'NO'
   order by id
)
where rownum = 1

Have you considered using Oracle AQ for this instead of rolling your own?

---

I would guess that your index isn't getting used because the stats aren't yet gathered for the index.

Check out this SO question. You can provide a hint in the query to force the use of the index(es) you created. If this helps, then running DBMS_STATS.gather_table_stats package for your table should force the update of stats, eliminating the need for the hint. Eventually the database will gather the stats on it's own (see Justin Cave's answer).

---

You haven't shared us the query. Sorting few thousands is easy compared to sorting 1M rows. There could be lot of other reasons you need to check for performance? Check the following:

• Are your tables analyzed? Are DBMS_STATS.gather_table_stats or gather_index_stats used?
• Have you checked EXPLAIN PLAN? Do they show INDEXES used?
• What version is your Oracle?

You should try Oracle Advanced Queuing as suggested.

---

Some ugly/clever hacks that just might work or could be just overkills.

1) You can create a nice small function based index like this (syntax might be a bit off, don't have acces to Oracle right now).

CREATE INDEX my_small_queue_index 
ON queue_table ( decode(is_processed,'YES',null,queue_name)
                ,decode(is_processed,'YES',null,id)
               );

An then you can select like this:

  SELECT --+ index_asc(q my_small_queue_index)
     decode(is_processed,'YES',null,id) AS id
  FROM queue_table q
  WHERE decode(is_processed,'YES',null,queue_name) = 'some queue name'
    AND rownum = 1;

Should work nice if there is a huge percentage of processed rows and only few unprocessed (10^9 against a few hundred). Should be no more than a few gets in any case.

2) You can create a partition for every queue if the queue names are fixed and there is not a lot of them.

---

The receommendation to use an index hint (without an order by), ie

SELECT --+ index_asc(q my_small_queue_index) decode(is_processed,'YES',null,id) AS id FROM queue_table q WHERE decode(is_processed,'YES',null,queue_name) = 'some queue name' AND rownum = 1;

is an extremely dangerous one. If that index is dropped, renamed, set to unusable, or the optimizer chooses a fast-full-scan, then you'll get no error, you'll still get 1 row back, but there's no guarantee its the right row. Using an index is fine - but you STILL must have that order-by clause to guarantee correct results.