VISIT_HIST
我在 Oracle 数据仓库中有一个表。该表包含每个月的客户访问次数,并且相当大(约 1.3 亿行)。
CREATE TABLE (
VALUE_MONTH DATE,
USER_ID NUMBER,
N_VISITS NUMBER
)
;
数据本身看起来像:
VALUE_MONTH | 用户身份 | N_VISITS次 |
---|---|---|
2022 年 12 月 31 日 | 43254 | 25 |
每天晚上,作业都会执行一个存储过程,该过程会更新该表。最近 3 个月的数据从表中删除。之后,该过程插入有关每个USER_ID
用户在过去 3 个月内的访问次数的新信息(3 个月内所有用户总共插入约 10-12 百万行)。存储过程如下所示:
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;
在更新表时,数据库用户无法使用最近 3 个月的数据。那么如何在更新过程中让表的用户可以使用最近 3 个月的旧版本数据呢?
我真的不想VISIT_HIST
每天在更新过程之前用另一个名字创建一个临时副本。很确定有一些更好的方法来解决我的问题。
任何帮助表示赞赏。
删除
COMMIT
, Oracle 的多版本读取并发处理其余部分。无论如何,您通常不应在存储过程中提交。
Browne 的回答是正确的,删除第一个 COMMIT。我想添加第二个答案不是因为我不同意,而是因为您可能会完全考虑另一种选择。
每天重复删除数百万条记录然后重新创建它们是一种相当低效的方法。在这个过程中,您会生成大量的撤消、重做、保持并发机制等……而且由于您的大部分数据实际上并没有每天发生变化,所以其中很多都是不必要的。
更好的解决方案是不进行删除和插入,而是进行一次合并:
优点:
您只修改需要修改的行。没有多余的删除和重新插入未更改的数据。大大减少了重做、撤消和执行时间。
单个 SQL 语句。保证事务隔离。在您提交之前,没有人会看到您在做什么,并且您不必为了执行此操作从一个语句到下一个语句保持事务打开。
您可以在 PDML(并行 dml)中执行此操作,如果您使用两个单独的语句并且需要保持事务打开,则不能执行此操作。同样,执行时节省了大量时间。
注意:对于其他评论者,请不要对我使用本机 Oracle 语法而不是 ANSI 胡说八道。这是一个有效的选择,并且运行良好。当然,使用 ANSI 样式的连接也可以完成同样的事情。这是风格偏好的问题。