How to Delete/Insert from different Tables and Views

Question

I've got a wicked problem. In Oracle 10 there are pairs of Views and Tables where something like that is done over and over again:

proc_log('DELETE 1');
DELETE FROM table_1;
proc_log('INSERT 1');
INSERT INTO table_1 SELECT * FROM view_1;
proc_log('FINISH 1');

And the same with View/Table 2 and 3 and 4 and 5... and 36.

I would like to do something like this:

PROCEDURE proc_import(p_table VARCHAR2) IS
BEGIN
    proc_log('DELETE ' || p_table);
    EXECUTE IMMEDIATE 'DELETE FROM table_' || p_table;
    proc_log('INSERT ' || p_table);
    EXECUTE IMMEDIATE 'INSERT INTO table_' || p_table || ' SELECT * FROM view_' || p_table;
    proc_log('FINISH || p_table);
    COMMIT;
END;

And then call the function for all 36 pairs.

Not surprisingly, this stuff is about 50% slower than the hard coded one.

My question: Has anyone an idea on how to make it faster. Or even better, how can I make this stuff different but similarly elegant?

EDIT

The whole stuff is built like this:

CREATE OR REPLACE PACKAGE PKG_IMPORT IS
  PROCEDURE proc_log IS BEGIN [funky not important stuff] END;
      
  PROCEDURE proc_import IS
  BEGIN
      proc_import_table('1', TRUE);
      proc_import_table('2');
      proc_import_table('3');
      proc_import_table('4', TRUE);
      proc_import_table('5');
      ...
      proc_import_table('36');
  END;

  PROCEDURE proc_import(p_table VARCHAR2, p_whole BOOLEAN DEFAULT FALSE) IS
  BEGIN
    proc_log('DELETE ' || p_table);
    IF p_whole THEN
      EXECUTE IMMEDIATE 'DELETE FROM table_' || p_table;
    ELSE
      EXECUTE IMMEDIATE 'DELETE FROM table_' || p_table || ' WHERE business_logic_applies';
    END IF;
    proc_log('INSERT ' || p_table);
    EXECUTE IMMEDIATE 'INSERT INTO table_' || p_table || ' SELECT * FROM view_' || p_table;
    proc_log('FINISH || p_table);
    COMMIT;
  END;
 END PKG_IMPORT;

The Procedure proc_import is called by a job once a night. The reason why all the proc_import_table calls are hard coded is that some of the tables need additional import information.

I'm afraid i can't copy/paste the original code in here because i don't know if i am allowed to do. Hope this helps...

Write it out in full. You still have to call `proc_import` 36 times. This isn't an answer to your question but it sounds as though you have a problem with the structure of your database. — Ben, Nov 08 '12 at 11:16
Something doesn't seem quite right here. If this is a nightly job, and truncate/append help performance, then I assume it takes more than a few seconds to run. But if it takes more than a few seconds to run, you should not notice any difference caused by the overhead of using dynamic SQL a few dozen times. Maybe you should run the test a few more times, perhaps some other factor caused the performance difference. — Jon Heller, Nov 11 '12 at 18:32

score 2 · Accepted Answer · answered Nov 08 '12 at 14:38

First of all, if you don't have any conditions on which rows to delete, you could just truncate the table.

TRUNCATE TABLE table_1;

TRUNCATE is a ddl operation and it does not phsically delete the rows, it only manipulates the high water mark, making the operations very fast. But also keep in mind that it cannot be rolled back, since it is DDL.

On the other hand, you can perform a direct path insert, instead of the regular one. Try this:

INSERT /*+ append */ into table_1 select * from view_1;

This will cause Oracle to write the data directly above high watermark, making the operation faster. It will also probably help if your tables are PARALLEL.

I normally prefer `TRUNCATE` too, but as you said, it's not "rollbackable". We don't want to have emtpy tables if something goes wrong. This direct path-thing looks very interesting - i'll try a bit research for what exactly it does. Thanks. — Joshua, Nov 08 '12 at 15:53

score 0 · Answer 2 · answered Nov 08 '12 at 11:47

0

The original code would execute quicker as Oracle would be able to precompile the queries, which it can't do with 'execute immediate'.

Your new code may look more concise but is actually far more difficult to read. At the very least, rather than pass in a table number you should give the full table name otherwise anybody else looking at your code will have to ficure out what the numbers mean.

That said, I think I would prefer to see 36 delete and 36 insert statements - far less cryptic and much tidier. You could place all of those statments into a proc of their own if it helped with shorter procs.

answered Nov 08 '12 at 11:47

Dave Richardson

4,880
7
32
47

Under normal circumstances i totally agree to your opinion. But in this case it's a bit more complicated. For example is the order of the "DELETE/INSERT" operations very important. By using short procedure calls it is a lot easier to change and track it. Furthermore i am also a Java Dev, so encapsulating functionality is my job ;) – Joshua Nov 08 '12 at 16:04
I'm a java dev too - and I wouldn't write it like you have! If ordering is important, write the statements in the correct order. The principles of good code design do not mean that you should condense everything down into a short space, readability/maintainability is equally important. It's your code, but if I had to maintain it I wouldn't be happy! – Dave Richardson Nov 08 '12 at 16:55

How to Delete/Insert from different Tables and Views

2 Answers2