Oracle中实现按优先级排序并批量锁行的单查询优化方案问询

我现在碰到一个Oracle查询的优化问题，想请教大家怎么解决。场景是这样的：我需要从TXINCH表中获取关联到TXINCH_FILE表的新消息，并且按照RTPE_CNB_FILES表的M_PRIORITY字段排序返回。这几个表通过文件ID关联：RTPE_CNB_FILES和TXINCH_FILE存的是导入的批量文件，TXINCH则是每个文件里的单独交易，每个文件可能有几千条记录。

最开始我写的查询是这样的，每次只取一个最高优先级的文件，然后从中拿最多750条记录：

WITH next_file_id AS (
    SELECT * FROM (
        SELECT f.TXFILEID
        FROM TXINCH_FILE f
        LEFT OUTER JOIN RTPE_CNB_FILES cnbf ON f.TXFILEID = cnbf.M_FILE_REF
        WHERE f.TXSTATE = 'NW'
        ORDER BY cnbf.M_PRIORITY ASC
    )
    WHERE ROWNUM <= 1
)
SELECT *
FROM TXINCH i
WHERE i.TXSTATE = 'NW'
  AND i.TXFILEID = (SELECT TXFILEID FROM next_file_id WHERE ROWNUM <= 1)
  AND ROWNUM <= 750
FOR UPDATE SKIP LOCKED

这个查询在每个文件有超过750条交易的时候没问题，但如果遇到大量只有1条交易的小文件，框架每次查询要等15秒，效率特别低。我想改成一个单查询，能按优先级（HIGH优先，然后是LOW，最后是null）排序获取记录，同时用FOR UPDATE SKIP LOCKED锁定正在处理的行。

我试过直接加ORDER BY，但碰到了ORA-02014错误：cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.。我的需求是：如果有HIGH优先级的记录就优先取，没有的话再取LOW或者空优先级的；可以去掉最后的ROWNUM <=750限制，或者按优先级顺序尽可能多取，同时保证锁行逻辑正常。

为了方便大家理解，我把表结构和测试数据贴出来：

表结构 #

CREATE TABLE RTPE_CNB_FILES (
    M_FILE_REF VARCHAR2(32) NOT NULL,
    M_PRIORITY VARCHAR2(8) NOT NULL
);

CREATE TABLE TXINCH_FILE (
    TXFILEID VARCHAR2(32) NOT NULL,
    TXSTATE VARCHAR2(8)
);

CREATE TABLE TXINCH (
    TXREF VARCHAR2(32) NOT NULL,
    TXFILEID VARCHAR2(32) NOT NULL,
    TXSTATE VARCHAR2(8)
);

测试数据 #

INSERT INTO RTPE_CNB_FILES (M_FILE_REF, M_PRIORITY) VALUES('0001','HIGH');
INSERT INTO RTPE_CNB_FILES (M_FILE_REF, M_PRIORITY) VALUES('0002','HIGH');
INSERT INTO RTPE_CNB_FILES (M_FILE_REF, M_PRIORITY) VALUES('0003','HIGH');
INSERT INTO RTPE_CNB_FILES (M_FILE_REF, M_PRIORITY) VALUES('0004','LOW');
INSERT INTO RTPE_CNB_FILES (M_FILE_REF, M_PRIORITY) VALUES('0005','LOW');
INSERT INTO RTPE_CNB_FILES (M_FILE_REF, M_PRIORITY) VALUES('0006','LOW');

INSERT INTO TXINCH_FILE (TXFILEID, TXSTATE) VALUES('0001', 'NW');
INSERT INTO TXINCH_FILE (TXFILEID, TXSTATE) VALUES('0002', 'NW');
INSERT INTO TXINCH_FILE (TXFILEID, TXSTATE) VALUES('0003', 'NW');
INSERT INTO TXINCH_FILE (TXFILEID, TXSTATE) VALUES('0004', 'NW');
INSERT INTO TXINCH_FILE (TXFILEID, TXSTATE) VALUES('0005', 'NW');
INSERT INTO TXINCH_FILE (TXFILEID, TXSTATE) VALUES('0006', 'NW');

INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES('1','0001', 'NW');
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES('2','0001', 'NW');
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES('3','0001', 'NW');
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES('4','0002', 'NW');
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES('5','0002', 'NW');
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES('6','0003', 'NW');
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES('7','0003', 'NW');
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES('8','0003', 'NW');
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES('9','0003', 'NW');
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES('10','0004', 'NW');
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES('11','0004', 'NW');
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES('12','0004', 'NW');
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES('13','0004', 'NW');
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES('14','0005', 'NW');
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES('15','0005', 'NW');
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES('16','0006', 'NW');

现在用原来的查询只能返回0001文件的3条记录，但我期望能一次性返回所有HIGH优先级文件的所有记录（也就是0001、0002、0003对应的所有交易），而不是每次只取一个文件的记录。

内容来源于stack exchange