使用regexp_substr的SQL查询速度很慢，一旦在表中插入了更多记录，就会超时

问题描述

我有一张桌子

cbcm(REPORT_NAME varchar2(30), WHERE_CLAUSE varchar2(2000))

insert into  cbcm(REPORT_NAME,WHERE_CLAUSE)
values('SE_SUPP2','29786399,29271272,29815958,29821597,29821140,29821791,29850566')

此处WHERE_CLAUSE具有整数(id)值，并插入了以，"分隔的值.

Here WHERE_CLAUSE is having integer(id) values which are inserted with "," delimited values.

在第二张表中:

cust_bug_data(id integer, name varchar2(20))

表cbcm中的WHERE_CLAUSE具有值作为ID.

Which is having id as values from WHERE_CLAUSE in table cbcm.

insert into  cust_bug_data(29786399,'test')

现在，我想列出cust_bug_data中的记录，其中id来自cbcm表中的特定REPORT_NAME，如下所示:

Now I want to list records in cust_bug_data where id is from particular REPORT_NAME in cbcm table like as follows:

select *
from   cust_bug_data
where  id in(
  select regexp_substr(WHERE_CLAUSE,'[^,]+',1,level) WHERE_CLAUSE
  from   cbcm
  where  REPORT_NAME='SE_SUPP2'
  connect by regexp_substr(WHERE_CLAUSE,'[^,]+',1,level) is not null
)


Above query is taking 12 secs for column values `WHERE_CLAUSE` having 40 "," delimited values. Now when I inserted another record in `cbcm` having 90 "," delimited values, above query is getting timed out.

Could you please suggest a way for the same.

推荐答案

检查一个值是否是另一个值的子字符串:

Check if one value is a sub-string of the other:

select *
from   cust_bug_data cbd
where  EXISTS (
  SELECT 1
  FROM   cbcm
  WHERE  cbcm.REPORT_NAME='SE_SUPP2'
  AND    ',' || cbcm.WHERE_CLAUSE || ',' LIKE '%,' || cbd.id || ',%'
)

输出:

      ID | NAME
-------: | :---
29786399 | test

db<>小提琴此处

或者不要在数据库中存储定界字符串:

Or don't store delimited strings in your database:

CREATE TABLE cbcm(
  REPORT_NAME varchar2(30) PRIMARY KEY
);

CREATE TABLE cbcm_where(
  REPORT_NAME  varchar2(30) REFERENCES cbcm ( REPORT_NAME ),
  WHERE_CLAUSE integer,
  PRIMARY KEY ( REPORT_NAME, WHERE_CLAUSE )
);

CREATE TABLE cust_bug_data(id integer, name varchar2(20));

insert into  cbcm (REPORT_NAME ) values('SE_SUPP2');

insert into  cust_bug_data VALUES ( 29786399, 'test');

插入列表时将其拆分(这是一种使用较快的字符串函数而不是较慢的正则表达式的方法):

Split the list up when you insert it (here is a method that uses faster string functions rather than slow regular expressions):

insert into cbcm_where ( REPORT_NAME, WHERE_CLAUSE )
WITH list_to_insert ( report_name, list ) AS (
  SELECT 'SE_SUPP2', '29786399,29271272,29815958,29821597,29821140,29821791,29850566' FROM DUAL
),
bounds ( report_name, list, startidx, endidx ) AS (
  SELECT report_name,
         list,
         1,
         INSTR( list, ',', 1 )
  FROM   list_to_insert
UNION ALL
  SELECT report_name,
         list,
         endidx + 1,
         INSTR( list, ',', endidx + 1 )
  FROM   bounds
  WHERE  endidx > 0
)
SELECT report_name,
       TO_NUMBER(
         CASE
         WHEN endidx = 0
         THEN SUBSTR( list, startidx )
         ELSE SUBSTR( list, startidx, endidx - startidx )
         END
       )
FROM   bounds

然后查询您的数据:

select *
from   cust_bug_data cbd
where  EXISTS (
  SELECT 1
  FROM   cbcm_where cw
  WHERE  cw.REPORT_NAME='SE_SUPP2'
  AND    cw.WHERE_CLAUSE = cbd.id
)

      ID | NAME
-------: | :---
29786399 | test

db<>小提琴此处

这篇关于使用regexp_substr的SQL查询速度很慢，一旦在表中插入了更多记录，就会超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！