I need to move records from table A to table B in Spring Boot + Oracle DB. Only a subset of columns from each record in A are moved to B.
For example, from A(a_id, user_id, name, email, phone, status, picked_state), I need to insert into B(b_id, user_id, name, status) where a_id, b_id are auto generated IDs for the corresponding tables.
I don’t prefer to use any CDC for this!
Currently, I am running a method every 10 seconds to check for records in table A with picked_state as null. Take and insert that records to table B.
Then I am setting the column ‘picked_state’ to PICKED in table A after I insert a record to B like:
UPDATE table_a SET picked_state = ‘PICKED’, processed_at = CURRENT_TIMESTAMP WHERE txn_ref_no IN (SELECT transaction_id FROM table_b);
Here, I am checking for the record was inserted to table B.
Every operations I mentioned like insert, update, select etc are done by executing native sql query itself from the application.
But in this query,
SELECT transaction_id FROM table_b
will be large as records are being inserted into table_b continuously. This does not seem to be an efficient approach. What are the possible ways to efficiently handle this?
Did you try EXISTS? Might perform better than IN.
UPDATE table_a a SET
picked_state = 'PICKED',
processed_at = CURRENT_TIMESTAMP
WHERE EXISTS (SELECT NULL
FROM table_b b
WHERE b.transaction_id = a.txn_ref_no
);
Another option might be to first fetch the last ID, and then update only rows that were recently added. That requires PL/SQL, though.
DECLARE
l_max_id NUMBER;
BEGIN
SELECT MAX (b.b_id)
INTO l_max_id
FROM table_b b;
INSERT INTO table_b (user_id, name, status)
SELECT user_id, name, status
FROM table_a a
WHERE a.picked_state <> 'PICKED';
UPDATE table_a a
SET a.picked_state = 'PICKED', a.processed_at = CURRENT_TIMESTAMP
WHERE EXISTS
(SELECT NULL
FROM table_b b
WHERE b.transaction_id = a.txn_ref_no
AND b.b_id > l_max_id);
END;
Do it all in a single PL/SQL statement:
DECLARE
TYPE rowid_table IS TABLE OF ROWID;
v_rowids rowid_table;
BEGIN
UPDATE a
SET picked_state = 'picked',
processed_at = CURRENT_TIMESTAMP
WHERE picked_state IS NULL
RETURNING rowid BULK COLLECT INTO v_rowids;
FORALL i IN 1 .. v_rowids.COUNT
INSERT INTO b (b_id, user_id, name, status)
SELECT a_id, user_id, name, status
FROM a
WHERE ROWID = v_rowids(i);
END;
fiddle