值得使用临时表来复制行的开销吗？

Question

当面临复制数据库中的记录并仅修改少数值的任务时，我倾向于使用临时表来避免写出所有未更改的列。有谁知道这会如何影响大型系统的性能？

一个简单的例子（这也说明了为什么我更喜欢临时表方法）：

假​​设我有一个包含 50 列 col1...col50 的表 mytbl代码>.我想插入一条新记录，该记录与 col5 = 'Some Value' 的行完全相同，但 col45 将设置为 'Some other value '。

方法 1

CREATE GLOBAL TEMPORARY TABLE tmptbl AS
  SELECT * FROM myschema.mytbl;

INSERT INTO tmptbl
  (SELECT *
   FROM myschema.mytbl
   WHERE mytbl.col5 = 'Some Value');

UPDATE tmptbl
SET col45 = 'Some Other Value';

INSERT INTO myschema.mytbl
  (SELECT * FROM tmptbl);

DROP TABLE tmptbl;

方法 2

INSERT INTO myschema.mytbl (col1,
                            col2,
                            col3,
                            col4,
                            col5,
                            col6,
                            col7,
                            col8,
                            col9,
                            col10,
                            col11,
                            col12,
                            col13,
                            col14,
                            col15,
                            col16,
                            col17,
                            col18,
                            col19,
                            col20,
                            col21,
                            col22,
                            col23,
                            col24,
                            col25,
                            col26,
                            col27,
                            col28,
                            col29,
                            col30,
                            col31,
                            col32,
                            col33,
                            col34,
                            col35,
                            col36,
                            col37,
                            col38,
                            col39,
                            col40,
                            col41,
                            col42,
                            col43,
                            col44,
                            col45,
                            col46,
                            col47,
                            col48,
                            col49,
                            col50)
SELECT col1,
       col2,
       col3,
       col4,
       col5,
       col6,
       col7,
       col8,
       col9,
       col10,
       col11,
       col12,
       col13,
       col14,
       col15,
       col16,
       col17,
       col81,
       col19,
       col20,
       col21,
       col22,
       col23,
       col24,
       col25,
       col26,
       col27,
       col28,
       col29,
       col30,
       col31,
       col32,
       col33,
       col34,
       col35,
       col36,
       col37,
       col38,
       col39,
       col40,
       col41,
       col42,
       col43,
       col44,
       'Some Other Value',
       col46,
       col47,
       col48,
       col49,
       col50
FROM myschema.mytbl
WHERE col5 = 'Some Value';

创建/删除临时表会带来多少开销？例如，如果这是生产规模系统上日常流程的一部分，那么额外的开销会很明显吗？我意识到这在很大程度上取决于系统的具体情况，但总体思路会很棒。

Answer 1

首先，这不是临时表在 Oracle 中的工作方式。全局临时表是永久结构，只是其中的数据是临时的。因此不需要为每个操作创建和删除表。这只会增加开销。

其次，临时表合理的情况实际上很少见。在大多数情况下，一个简单的变量就足够了。此语法（插入单行）从 9iR2 开始有效：

declare
    lrec emp%rowtype;
begin
    select *
    into lrec
    from emp
    where empno = 1234;

    lrec.empno = 9999;
    lrec.sal = 5000;

    insert into emp values lrec;
end;

请注意，此公式中不需要括号。

Answer 2

我不明白使用临时表如何可能更快。

即使不考虑临时表创建（和销毁）的开销（两项操作），您也将执行三个单独的数据库操作，而不是一个组合操作。此外，您正在执行两个涉及对非临时表进行单独锁定的操作，这会导致比单个组合操作更少的潜在并发性。诚然，这在某种程度上是特定于获取实际锁的数据库的。

最终，我认为方法 1 的开销比方法 2 多得多，甚至没有考虑临时表创建/销毁的不可忽略的成本。

Answer 3

我担心这可能是过早优化的情况。看来这种方法“解决”了一个可能存在也可能不存在的问题，但肯定会增加系统相当程度的复杂性。我的建议是

1. 做可能有效的最简单的事情（在本例中，可能是 INSERT-SELECT 组合）。
2. 测量结果。
3. 如果解决方案 #1 有效，那就好。您做了最简单的事情，它解决了您的问题。
4. 如果解决方案 #1 不起作用（或者效果不够好），也可以 - 转向下一个最简单可能起作用的方法。
5. 不断迭代，直到问题得到充分“解决”或时间或金钱预算耗尽。

分享并享受。

Answer 4

我假设您的方法的主要目标是避免必须将所有 50 列写出两次，我认为这有一个缺点，如果将另一列添加到表中，您必须进行更改。其他人对源表结构、临时表开销和过早优化等发表了评论。这实际上不是你的问题，但基于这个假设，我可以看到第三种可能有效的方法：

declare
    type tmptbl_type is table of mytbl%rowtype;
    cursor c is
        select * from mytbl where col5 = 'Some Value';
    tmptbl tmptbl_type;
begin
    open c;
    loop
        fetch c bulk collect into tmptbl limit 1000;
        for i in 1..tmptbl.count loop
            tmptbl(i).col45 := 'Some Other Value';
        end loop;
        forall i in 1..tmptbl.count
            insert into mytbl values tmptbl(i);
        exit when c%notfound;
    end loop;
    close c;
end;
/

Answer 5

你可以这样做。

插入 myschema.mytbl
(SELECT * FROM myschema.mytbl WHERE mytbl.col5 = '某些值');

更新 myschema.mytbl
SET col45 = '其他一些值'
WHERE col5 = '某个值' AND rownum<=1;

这甚至比使用临时表更短，并且没有临时表的缺点。当然，如果您的实际问题比您所展示的更复杂，则这可能不起作用。

Answer 6

如果您的过程将处理少于 250 行，您可能需要考虑使用表变量而不是临时表，因为表变量使用内存而不是物理写入到 tempdb。