The following sql execution is too slow, how should it be optimized?
UPDATE AA A SET COL1 = (SELECT B.COL2 FROM BB B WHERE B.BH = A.BH AND B.YEAR = '2016-2017' ) WHERE A.YEAR = '2017-2018';
Among them, the AA table and the BB table are the same and are one table
I didn’t quite understand it,
Since it’s the same step, just
update the table set column 1 = column 2 where condition
and that’s it
Create a new table based on the new value, then merge the two tables, and finally delete the temporary table;
Create a transaction, write all updates into it, and finally commit;
What I don’t understand is whether AA and BB are one table or two tables.
If it is one table, @prolifes’ method can be used.
If it is two tables,
update AA a, BB b set a.col1 = b. col2 where a.bh = b.bh and a.year='2017-2018' and b.year='2016-2017',
Also, the slowness is related to the size of your data and the index, I just give A general method, how effective it is, you have to try it yourself
Thank you for your suggestions. In the end, I used the one on the Internet, which is slightly more efficient:
Oracle has two methods:
Inline view update