SQL Oracle - better way to to write update and delete query-CodePudding

I have around 270 million rows indexed on month||id_nr and below update/delete query takes around 4 hours to complete. I was wondering if there is any other way to do update/delete which will be faster.

Update Query:-

update table_A
set STATUS='Y'
where
month||id_nr in (select distinct month||id_nr from table_A where STATUS='Y');

Delete Query:-

Delete from table_B
where
month||id_nr in (select distinct month||id_nr from table_A where STATUS='Y');

CodePudding user response：

Why the string concatenation? And never try to force the DBMS to make rows distinct in an IN clause. Let the DBMS decide what it considers the best approach to look up the data.

So, just:

where (month, id_nr) in (select month, id_nr from table_A where status = 'Y');

I suppose that id_nr is not a unique ID in table_b, for otherwise you wouln't have to look at it combined with the month. An appropriate index would hence be:

create index idx_b on table_b (id_nr, month);

Or maybe, if you work a lot with the month, it may be a good idea to even partition the table by month. This could speed up queries, updates, and deletes immensely.

For table_a I suggest

create index idx_a on table_a (status, id_nr, month);

which is a covering index. The first column will help find the desired rows quickly; the other two columns will be available without having to read the table row.