Oracle表充当队列

Question

我们有一个非常高并发的应用程序，其中一些要处理的键会不断地写入Oracle 11g表中，并具有处理优先级。该表上有一个来自序列的主键（ID字段）。KEY字段上有一个UNIQUE约束。

ID    KEY        PRIORITY
-------------------------
1     ABC           0
2     XYZ           5
3     AZW           0
...
100   CNS           7

上面的表格以非常高的速度插入，比如说每分钟大约有一万条记录。我们还有大约一百个平行消费者，他们不断地汇集在上表中寻找工作。一个这样的消费者一次只需要一个密钥来处理，但至关重要的是，两个消费者一次不能将同一个密钥传给多个消费者处理应按照PRIORITY和ID的顺序进行。

为了满足这一点，消费者最终会调用下面这样的函数：

FUNCTION select_key RETURN VARCHAR2
IS
   v_key VARCHAR2(64) := NULL;
   CURSOR keys IS
   SELECT key
     FROM my_table
    ORDER BY priority, id
      FOR UPDATE SKIP LOCKED;
BEGIN   
    OPEN keys
    LOOP
        FETCH keys INTO v_key;
        EXIT WHEN keys%NOTFOUND;
        DELETE FROM my_table WHERE key = v_key;
        EXIT WHEN SQL%ROWCOUNT > 0;
    END LOOP;
    CLOSE keys;
    RETURN v_key;
END;

因为这个表被如此高的速率插入和删除，所以该表上的统计数据很快就过时了。上面SELECT的执行计划显示了一个完整的表扫描。这会导致密钥选择过程花费越来越长的时间，随着时间的推移，性能会显著下降。

除此之外，因为ORACLE锁定在数据块级别，而不是记录级别，所以我们经历的处理并没有真正按照PRIORITY和ID的顺序进行。这对我们来说并不是什么大问题，但我们仍然希望避免。

这种方法的另一个更大的问题是，每次对一个表运行一个完整的SQL，该表只需获得一个键就可以轻松地获得数万条记录。

我想到的第一个想法是为此使用一个真正的队列，并从中为我的并发消费者提供服务。然而，我最终在同步表和队列提要时遇到了各种问题，最终我放弃了这个想法。

任何关于我应该如何更好地处理这一问题的建议都将不胜感激。

提前谢谢。

Answer 1

在（PRIORITY，ID）上创建一个索引，然后查询可以使用INDEX FULL SCAN按顺序读取数据，而不是扫描整个表。

样本表和数据

drop table my_table;
create table my_table
(
    key varchar2(100) not null,
    id number not null,
    priority number not null,
    constraint my_table_pk primary key (key)
);
insert into my_table
select level, level, level
from dual connect by level <= 100000;
begin
    dbms_stats.gather_table_stats(user, 'MY_TABLE');
end;
/

使用完整表格扫描的正常解释计划

explain plan for
select key
from my_table
order by priority, id;
select * from table(dbms_xplan.display);

Plan hash value: 3656711297
---------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |   100K|  1562K|       |   637   (1)| 00:00:01 |
|   1 |  SORT ORDER BY     |          |   100K|  1562K|  2760K|   637   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| MY_TABLE |   100K|  1562K|       |   103   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

创建索引以获得更好的索引访问计划

成本一开始看起来并不好。但实际版本应该更快，因为它会很快停止处理。

create index my_table_idx on my_table(priority, id);
explain plan for
select key
from my_table
order by priority, id;
select * from table(dbms_xplan.display);
Plan hash value: 2209255802
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |   100K|  1562K|   577   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_TABLE     |   100K|  1562K|   577   (1)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | MY_TABLE_IDX |   100K|       |   292   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------