CODEWITHSUNDEEP
oracle-database
Mike S
Mike S

Reputation: 11

Oracle Optimization - Update and Subquery

I have two very large tables (one with over 500 M records and the other with over 1B records)

I need to update status of the record in table one if one of the fields in table two contains more than 4 Char(10) characters in a row.

When I look for the records that meet that criteria it takes approximately 10 min - 

SELECT B.ID as main_id
FROM Table A, TABLE B
WHERE A.CREATED_DATE > TRUNC(SYSDATE -1)
AND   A.STATUS_CODE = 'IN PROGRESS'
AND   A.ID = B.ID
AND   (REGEXP_COUNT(B.TEXT, CHR(10) || '{4},1,'mn')) > 0

Now, I need to look up all of the records in table A and update status field to FAIL based on the query above. Basically I am suing above as a sub-query for the update. However, when I do that, my update statements run for a very long time where I would expect it to run slightly longer than 10 minutes (above query returns only 2 records back). Here is what I have -->

MERGE INTO TABLE A
USING (SELECT B.ID as main_id 
  FROM Table A, TABLE B
  WHERE A.CREATED_DATE > TRUNC(SYSDATE -1)
  AND   A.STATUS_CODE = 'IN PROGRESS'
  AND   A.ID = B.ID
  AND   (REGEXP_COUNT(B.TEXT, CHR(10) || '{4},1,'mn')) > 0) CHECK_ERRORS
ON (A.ID = CHECK_ERRORS.main_id)
WHEN MATCHED THEN UPDATE SET A.STATUS_CODE = 'FAILED'

What am I doing wrong here?

Thank you

Upvotes: 1

Views: 147

Answers (2)

user5683823
user5683823

Reputation:

What you are doing wrong is that you are unnecessarily joining the first table twice: once in the subquery (which already does the work needed in MERGE) and again in the ON clause of MERGE.

The MERGE statement should be something like this:

merge into table_a a
  using table_b b
   on ( a.id = b.id )
when matched then update 
  set a.status_code = 'FAILED'
  where a.created_date >    trunc(sysdate - 1)
    and a.status_code  =    'IN PROGRESS'
    and b.text         like '%' || chr(10) || chr(10) || chr(10) || chr(10) || '%'
;

It will also help speed up the processing if you had indexes on the id columns in both tables, and perhaps also on created_date, assuming only a small fraction of rows fall within the last day or so.

Upvotes: 1

kfinity
kfinity

Reputation: 9091

Hmm. Wouldn't it be faster to use B.TEXT LIKE '%'||CHR(10)||CHR(10)||CHR(10)||CHR(10)||'%' instead of REGEXP_COUNT?

Admittedly, I don't use MERGE very often, but if you're just doing updates, I would write the second statement like this:

UPDATE A
SET A.STATUS_CODE = 'FAILED'
WHERE A.CREATED_DATE > TRUNC(SYSDATE -1)
  AND A.STATUS_CODE = 'IN PROGRESS'
  AND A.ID IN (SELECT B.ID FROM B WHERE B.TEXT LIKE
    '%'||CHR(10)||CHR(10)||CHR(10)||CHR(10)||'%');

(I'm assuming based on your code that you meant the Table 2 field contains 4+ CHR10 characters, not "more than 4")

Upvotes: 0

Related Questions