1. Using Pro*C to open a cursor, fetch each row and insert it in another table.
2. Using SQL which will be like
Insert into table2 values( select * from table1)3. Using PL/SQL to fetch in to cursor, inserting by row in table2.
Which one is the fastest and most cost-effective solution, and why? Any help would be appreciated.
While not true in every single case, I follow this standard:
1. If it can be done in straight SQL, do it in straight SQL. Otherwise, go to #2.
2. If it can be done in PL/SQL, do it in PL/SQL. Otherwise, go to #3.
3. If all else fails, use another programming language connected to the database.
Straight SQL is the database's native language. So straight SQL statements tend to be faster (with rare exceptions). This is the language the database talks in and it speaks it pretty fluently. This is how interaction with the database is designed, so this is the interaction that performs the best.
PL/SQL is Oracle's procedural extension to SQL. If SQL won't handle your needs, use PL/SQL if at all possible. PL/SQL is designed to run in the database and enjoy a nice cohesive working relationship with the database.
If SQL and PL/SQL won't work for your needs, then you'll need to resort to another language (Java, C, Perl, etc.). But I use these only if the other two won't work.
For More Information
- What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.