Oracle 查询需要更多时间来拆分、与查找表进行比较和插入

我们有一个维度表，其中一列具有类似数组的多个值。
我必须获取所有这些单独的值，如果查找表中不存在该值，则应与查找表进行比较
然后，我必须将此值与id一起插入查找表中，这里的id应该是序列 (从查找表中选择max(id) 1)

昏暗的表:

创建表TEST0922 (代码VARCHAR2(2000));
插入到TEST0922值 ('[“拒绝”，“保留”，“保留”，“研究”，“拒绝”，“保留”，“保留”，“研究”，“拒绝”，“保留”，“分析”]');
插入到TEST0922值 ('[“拒绝”，“保留”，“保留”，“研究”，“拒绝”，“保留”，“保留”，“研究”，“拒绝”，“保留”，“TEST123”]');
提交;
查找表:

创建表TEST_LOOKUP0922 (id号，代码VARCHAR2(30));
插入到TEST_LOOKUP0922值 (1，“拒绝”);
插入到TEST_LOOKUP0922值 (2，'APPRVOE');
插入到TEST_LOOKUP0922值 (3，'保持');
提交;

我在一个过程中使用下面的查询来获取不在查找表中的值，然后插入这些值。
从TEST0922中选择代码;
T为
(选择替换 (REGEXP_SUBSTR (代码，'("[^" \]* \ ")'，1，级别)，'"'，') COL1
来自
(从TEST0922中选择代码
)
-按级别连接 <= REGEXP_COUNT (审计规则操作，'，') 1
通过级别连接 <= regexp_count (代码，'("[^" \]* \ ")')
) 从T中选择不同的COL1，其中COL1不在 (从TEST_LOOKUP0922中选择代码);


但是，此查询仅针对DIM表中的250行运行超过45分钟，但是我的实际生产环境每天将获得100到200个记录。

所以请尽早建议任何更好的查询/方法;

在将组件组合在一起之前，请始终测试组件-让我们首先从标签的分割开始

SQL> CREATE TABLE TEST0922(CODE VARCHAR2(2000));

Table created.

SQL> INSERT INTO TEST0922 VALUES('["REJECT","APPRVOE","HOLD","RESEARCH","REJECT","APPRVOE","HOLD","RESEARCH","REJECT","HOLD","ANALYSIS"]');

1 row created.

SQL> INSERT INTO TEST0922 VALUES('["REJECT","APPRVOE","HOLD","RESEARCH","REJECT","APPRVOE","HOLD","RESEARCH","REJECT","HOLD","TEST123"]');

1 row created.

SQL> COMMIT;

Commit complete.


SQL> SELECT REPLACE(REGEXP_SUBSTR(CODE,'("[^"\]*\"+)', 1, LEVEL), '"', '') COL1
  2  FROM TEST0922
  3  CONNECT BY level <=regexp_count(CODE,'("[^"\]*\"+)');

COL1
----------------------------------------------------------------------------------------
REJECT
APPRVOE
HOLD
RESEARCH
REJECT
APPRVOE
HOLD
RESEARCH
..
...
TEST123
HOLD
ANALYSIS
TEST123

4094 rows selected.
复制

我很确定你没有打算把4094排回来...这当然会引起各种各样的问题。所以我们会解决这个问题

SQL> SELECT tst.rowid r, tag
  2  from TEST0922 tst,
  3  lateral
  4    ( select REPLACE(REGEXP_SUBSTR(tst.CODE,'("[^"\]*\"+)', 1, LEVEL), '"', '') tag
  5      FROM  dual
  6      CONNECT BY level <=regexp_count(tst.CODE,'("[^"\]*\"+)')
  7     );

R                  TAG
------------------ ------------------------------
AAAlUQAAHAAAT6/AAA REJECT
AAAlUQAAHAAAT6/AAA APPRVOE
AAAlUQAAHAAAT6/AAA HOLD
AAAlUQAAHAAAT6/AAA RESEARCH
AAAlUQAAHAAAT6/AAA REJECT
AAAlUQAAHAAAT6/AAA APPRVOE
AAAlUQAAHAAAT6/AAA HOLD
AAAlUQAAHAAAT6/AAA RESEARCH
AAAlUQAAHAAAT6/AAA REJECT
AAAlUQAAHAAAT6/AAA HOLD
AAAlUQAAHAAAT6/AAA ANALYSIS
AAAlUQAAHAAAT6/AAB REJECT
AAAlUQAAHAAAT6/AAB APPRVOE
AAAlUQAAHAAAT6/AAB HOLD
AAAlUQAAHAAAT6/AAB RESEARCH
AAAlUQAAHAAAT6/AAB REJECT
AAAlUQAAHAAAT6/AAB APPRVOE
AAAlUQAAHAAAT6/AAB HOLD
AAAlUQAAHAAAT6/AAB RESEARCH
AAAlUQAAHAAAT6/AAB REJECT
AAAlUQAAHAAAT6/AAB HOLD
AAAlUQAAHAAAT6/AAB TEST123

22 rows selected.
复制

现在我们可以进行查找了，这样会运行得更好

SQL> CREATE TABLE TEST_LOOKUP0922(ID NUMBER,CODE VARCHAR2(30));

Table created.

SQL> INSERT INTO TEST_LOOKUP0922 VALUES(1,'REJECT');

1 row created.

SQL> INSERT INTO TEST_LOOKUP0922 VALUES(2,'APPRVOE');

1 row created.

SQL> INSERT INTO TEST_LOOKUP0922 VALUES(3,'HOLD');

1 row created.

SQL> commit;

Commit complete.

SQL> WITH T AS
  2  (
  3  SELECT tag
  4  from TEST0922 tst,
  5  lateral
  6    ( select REPLACE(REGEXP_SUBSTR(tst.CODE,'("[^"\]*\"+)', 1, LEVEL), '"', '') tag
  7      FROM  dual
  8      CONNECT BY level <=regexp_count(tst.CODE,'("[^"\]*\"+)')
  9     )
 10  )
 11  SELECT DISTINCT tag FROM T WHERE tag NOT IN (SELECT CODE FROM TEST_LOOKUP0922);

TAG
------------------------------
TEST123
ANALYSIS
RESEARCH

3 rows selected.

SQL>
复制