CODEWITHSUNDEEP
sqldb2
GIN
GIN

Reputation: 145

Suggestion for rewriting query

I have a table(TABLEA) with 1 million of records and records are populated from multiple sources into this table(TABLEA).
once data is populated the records processed by downstream.

Whenever data is inserted the value of the column FLAG will be null and once its processed by down stream the value of the FLAG will be updated as Y.

So basically I have requirement to check before records getting processed by downstream.

Requirement is that for already processed records in TABLEA(FLAG='Y') and CODE='I', I need to check is there any other records with same value for COLB where the FLAG is null. If its there I want to change the CODE='D'(which means marked for delete) and FLAG=NULL

I write a merge for this and its taking like 3 hours to run. I tried with explain plan and it didnt show me any recommendation. So is there anwyay to rewrite this query.

MERGE INTO TABLEA SRC
using
(select COLB from TABLEA where FLAG IS NULL) TGT
on(SRC.COLB=TGT.COLB and SRC.FLAG is not null and SRC.CODE='I')
when matched then update set 
CODE='D',FLAG = NULL;
commit;

This is my input record and before executing the merge query query

COLA     COLB  CODE FLAG
 -------------- --------- ---- ----
 10001   3060   I    Y
 10002   3548   I    Y
 10003   3566   I    Y
 10004   5093   I    Y
 10005   3548   I    Y
 10008   3060   I    NULL

My output should look like something below. Here the COLB value is same for first record(already processed) and last record(yet to process), so after the merge query this is how the TABLEA looks like

COLA     COLB  CODE FLAG
 -------------- --------- ---- ----
 10001   3060   D    NULL
 10002   3548   I    Y
 10003   3566   I    Y
 10004   5093   I    Y
 10005   3548   I    Y
 10008   3060   I    NULL

Upvotes: 0

Views: 52

Answers (2)

Turo
Turo

Reputation: 4914

You could have a speed up when the indexes contains all the 3 fields, so db2 can make index scans for the search and needs to read/write on the tablespace only when an update is needed.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48770

You can use the following UPDATE statement:

update tablea a
set code = 'D', flag = null
where flag = 'Y' and code = 'I'
  and exists (
    select 1 from tablea b where b.colb = a.colb and b.flag is null
  );

In order to [substantially] improve the performance you can create the following indexes:

create index ix1 on tablea (flag, code);

create index ix2 on tablea (colb, flag);

Upvotes: 1

Related Questions