Q

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?

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close