How to update table keeping data available to users (Oracle)?

Question

I have a table VISIT_HIST in an Oracle data warehouse. This table contains number of customer visits during every month and is rather big (~130 mn rows).

CREATE TABLE (
VALUE_MONTH DATE,
USER_ID NUMBER,
N_VISITS NUMBER
)
;

The data itself looks like:

VALUE_MONTH	USER_ID	N_VISITS
31.12.2022	43254	25

Every night a job executes a stored procedure, which updates this table. The data for last 3 months is deleted from table. After that, the procedure inserts new information about number of visits for each USER_ID user during last 3 months (~10-12 mn rows inserted in total for all users for 3 months). The stored procedure looks like:

CREATE OR REPLACE PROCEDURE EXAMPLE_PROC IS
BEGIN

-- 1. delete old data for the last 3 months
DELETE FROM VISIT_HIST 
WHERE VALUE_MONTH >= ADD_MONTHS(TRUNC(SYSDATE, 'dd.mm.yyyy'), -3)
;

COMMIT;

-- 2. insert new data
INSERT INTO VISIT_HIST

SELECT /*+ parallel(8)*/
LAST_DAY(VALUE_DAY) VALUE_MONTH,
USER_ID,
COUNT(DISTINCT VISIT_ID) N_VISITS
FROM ...
WHERE VALUE_DAY >= ADD_MONTHS(TRUNC(SYSDATE, 'dd.mm.yyyy'), -3)
GROUP BY 
LAST_DAY(VALUE_DAY),
USER_ID
;

COMMIT;

END;

The data for the last 3 months is unavailable to the database users when the table is being updated. So how can I keep the older version of the data for the last 3 months available to the users of the table during update process?

I really don't want to create a temporary copy of VISIT_HIST with another name every day before update process. Pretty sure there is some better way to solve my problem.

Any help is appreciated.

Answer 1

So how can I keep the older version of the data for the last 3 months available to the users of the table during update process?

Remove the COMMIT, and Oracle's Multi-Version Read Concurrency take care of the rest.

You should generally not COMMIT in stored procedures anyway.

Answer 2

Browne's answer is correct, remove the first COMMIT. I want to add a second answer not because I disagree, but because you might consider another option altogether.

Repeatedly deleting millions of records and then recreating them every day is a rather inefficient method. You generate tons of undo, redo, hold concurrency mechanisms during the process, etc... and as much of your data doesn't actually change from day-to-day, much of that is unnecessary.

A better solution is not to delete and insert, but to do a single merge:

EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

MERGE /*+ parallel(8) */ INTO VISIT_HIST tgt
USING (SELECT n.*,
              o.ROWID row_id
         FROM (SELECT LAST_DAY(VALUE_DAY) VALUE_MONTH,
                      USER_ID,
                      COUNT(DISTINCT VISIT_ID) N_VISITS
                 FROM ...
                WHERE VALUE_DAY >= ADD_MONTHS(TRUNC(SYSDATE, 'dd.mm.yyyy'), -3)
             GROUP BY LAST_DAY(VALUE_DAY),
                      USER_ID) n
              visit_hist o
        WHERE n.value_month = o.value_month(+)
          AND n.user_id = o.user_id(+)
          AND (o.user_id IS NULL OR o.n_visits <> n.n_visits)) src
   ON (src.row_id = tgt.ROWID)
 WHEN MATCHED THEN UPDATE SET tgt.n_visits = src.n_visits
 WHEN NOT MATCHED THEN INSERT (value_month,
                               user_id,
                               n_visits)
                       VALUES (src.value_month,
                               src.user_id,
                               src.n_visits);
                               
COMMIT;

Advantages:

1. you only modify rows that need to be modified. No redundant deleting and reinserting data that hasn't changed. Vastly reduced redo, undo, and execution time.

2. A single SQL statement. Transaction isolation is guaranteed. Nobody will see what you're doing until you commit, and you don't have to hold a transaction open from one statement to the next to do so.

3. You can do this in PDML (parallel dml), which you can't do if you use two separate statements and need to hold the transaction open. Again, lots of time saved on execution.

Note: to other commentors, please no guff about my using native Oracle syntax and not ANSI. It's a valid choice and works perfectly well. The same thing can be done with an ANSI-style join of course. It's a matter of stylistic preference.