Slow operation when selecting the next message from the user queue

Question

I have a simple table based system. In its simplest form, it consists of an identifier, a queue name, and a state. When reading the next message from a given queue, we need to provide FIFO (first in the first), i.e. The lowest id from this queue with the given status. All this works fine with a few thousand lines, but when we reach the 1M + lines, it does not improve.

We cannot use rownum = 1, since this is done before sorting, sorting is based on the id column exclusively (asc). If I make a cursor and sort by ID 1000 times, it will take about 100 ms together, which is good performance (0.1 ms / cycle). If I specify the status and queue name in the request (I need it, since I need the lowest unread message identifier for a particular queue), it takes about 1300 ms for 10 cycles (130 ms / cycle), which is far from okay.

I tried to have an index for each of the three columns, as well as a combined index in id, queue, status, and finally a combination with an index on id and a combined index in the queue and state. The id column is also the primary key. The entire combination has also been tested in a rule-based setting (using the rule hint).

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

Answer 1

, , , (queue, status, id). , , " ", , .

, .

Answer 2

:

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

Oracle AQ , ?

Answer 3

, , .

SO. , (). , DBMS_STATS.gather_table_stats , . (. ).

Answer 4

. 1M . , ? :

• ? DBMS_STATS.gather_table_stats gather_index_stats?
• EXPLAIN PLAN? ?
• Oracle?

Oracle Advanced Queuing, .

Answer 5

/ , .

1) , , ( , Oracle ).

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

:

  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;

, (10 ^ 9 ). .

2) , .

Answer 6

( ), ..

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

. , , , , 1 , . , STILL , .