Is it worth the overhead to use a temporary table to copy a row?

Question

When you are faced with the task of copying a record in a database and changing only a few values, I tend to use a temporary table to avoid writing all immutable columns. Does anyone know how this will affect performance in a large-scale system?

A quick example (which also shows why I prefer the temporary table method):

Let's say I have a table mytblwith 50 columns col1... col50. I want to insert a new record, which is an exact duplicate of the line where col5 = 'Some Value', except for what col45will be set to 'Some other value'.

Method 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;

Method 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

, .

( ) ( ) , . , , , concurrency, . , , , .

, , 1 , 2, , , / .

Answer 3

, . , "" , , , , .

• , ( , , INSERT-SELECT).
• .
• # 1 , . , , .
• # 1 ( ), - , .
• , "", .

.

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

.

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

UPDATE myschema.mytbl  SET col45 = ' '  WHERE col5 = ' ' AND rownum <= 1;

This is even shorter than using a temporary table and does not have any of the disadvantages of temporary tables. Of course, if your actual problem is more complicated than that shown by you, this may not work.

Answer 6

If your procedure will work with less than 250 rows, you may need to use a table variable instead of a temporary table, since the table variable uses memory instead of the physical write to tempdb.