- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- plain sql or plsql?
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-05-2008 12:12 PM
тАО02-05-2008 12:12 PM
Consider this scenario.
I want to insert data (about 1 million records) of Table A into Table B in a database (locally).
i am thinking of 2 options:
1) Use only sql- insert into Table B (select * from table A);
2) Use cursor - cursor ins_cursor is select * from table A and then use the cursor to insert into Table B.
Which option do you think is good from performance point of view ?
First impression would be to use option 1,but what if i want to find out the number of records inserted ? Can this be done without pl-sql?
Regards,
Gyan
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-05-2008 12:29 PM
тАО02-05-2008 12:29 PM
Re: plain sql or plsql?
Option 2) should be slower - but - this will give you more control of the load and how it goes. It also gives you the ability to save rollback by doing a saving (by one of many methods) where you are in the list of work to do, doing a commit, releasing the cursors (and thus, finally releasing all of the rollback (undo). Then you can restart.
This method only works if you have a method to determine which records have been processed, and which have not. For this I usually make a temporary table of the unique primary keys of the table I'm moving, if that table has a pk that can be formed, or, at least a unique combination to every row. Otherwise, a commit across fetches does no good except update the redo logs, but you won't release rollback(undo) until you've released the cursor.
The easiest thing to do would be simply to do option 1). If it works without running out of undo(rollback) with a "Snapshot too old" error - then just count the number of rows in the new table after you're done to make sure you've got all the records you thought you should. HOWEVER, if this is process that needs to run routinely (not by hand) and unattended, and robustly - well option two is the best way to go.
The answer is "it depends" and that is based on what you're using this for, and what your expectations for the routine are.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-05-2008 12:54 PM
тАО02-05-2008 12:54 PM
Re: plain sql or plsql?
This will be running everyday and infact Table A is a staging table before loading into the main table (Table B).
Regards,
Gyan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-05-2008 01:15 PM
тАО02-05-2008 01:15 PM
Solution- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-05-2008 03:03 PM
тАО02-05-2008 03:03 PM
Re: plain sql or plsql?
~cheers