Transferring data from four tables to another four tables

I have four tables, each contain a lakh of records. I need to transfer the records from those four tables to another four tables.

The first table is the primary table. The second and fourth tables are its secondary tables. The third table is a secondary table to the second table. Should I do it using a PL/SQL procedure?

I used four cursors for each and populate the data, but it was very slow. I want to increase the performance. How to do it?

    Requires Free Membership to View

    Login

I have one simple mantra that really helps when it comes to performance of database operations. It goes as follows:

  1. If it can be done with straight SQL statements, do it with SQL.
  2. If it can't be done with straight SQL, then use PL/SQL.
  3. If it can't be done with PL/SQL, then use Java in the database.
  4. If it can't be done with Java in the database, then use a C++ external procedure or something outside of the database.
As with any rules of thumb, there are exceptions. But in your case, rule number 1 definitely applies! Why use PL/SQL when simple SQL commands will do the job? Assume you want to copy data from tables TABLE1, TABLE2, TABLE3, and TABLE4 to new tables NEW_TABLE1, NEW_TABLE2, NEW_TABLE3, and NEW_TABLE4. Then, the following commands will help you: INSERT INTO new_table1 SELECT * FROM table1; INSERT INTO new_table2 SELECT * FROM table2; INSERT INTO new_table3 SELECT * FROM table3; INSERT INTO new_table4 SELECT * FROM table4; COMMIT; If you are worried about running out of undo space, then put a COMMIT between each of the INSERT statements. Then, since your case indicates that there are referential integrity constraints, add identical integrity constraints to the new tables after you have them populated.

For More Information


This was first published in March 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top