CODEWITHSUNDEEP
sqloracle-databaseregexp-substr
Subhash Tiwari
Subhash Tiwari

Reputation: 53

Sql query with regexp_substr is slow and timing out once more records are inserted in table

I have a table 

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')

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

In second table: 

cust_bug_data(id integer, name varchar2(20))

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

insert into  cust_bug_data(29786399,'test')

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.

Upvotes: 0

Views: 1267

Answers (2)

Popeye
Popeye

Reputation: 35900

Here, your query is taking time because IN clause is evaluated for each row of cust_bug_data and IN clause is executing connect by query which takes time.

You can use direct join using regexp_like as following:

select *
from cust_bug_data cbd
Join cbcm on regexp_like(','|| cbcm.where_clause ||',' ,','|| cbd.id ||',') 
WHERE ..

Cheers!!

Upvotes: 0

MT0
MT0

Reputation: 167982

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 || ',%'
)

Outputs:

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

db<>fiddle here

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

Then query your data:

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<>fiddle here

Upvotes: 2

Related Questions