Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 18th, 2003, 06:45 AM
Registered User
 
Join Date: Jun 2003
Location: Mumbai, MS, India.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Processing of 10 Million Records

We are in a process to design a batch, which will read around 10 Millions records from various tables and then gather their associated details from other related tables. Then process the data and finally insert the data into a separate table.
Steps in broad prospective will be as follows

1. Read the 10 Millions data from base tables and then insert the data into a temp table, from which rest of the processing will be done. Temporary table is used since we can’t modify the original table
2. Temporary table data will be updated for other related information from related tables, we expect minimum 6 different updates will be needed to capture all required information.
3. Once Temporary table is having all the necessary data, records will be taken into a cursor and all 10 million records will be processed one by one. After each record is processed, data will be inserted into a table.
4. Finally summarization will be done and summarized data will be inserted into different tables.

I need your suggestion about the following points considering the following HW specs
Oracle 8.1.7 on HP UNIX-11
2 CPUs
1 GB Ram

1. What should be the optimum SGA and Rollback Segment Size considering single record size around 500 bytes
2. Regarding step no 1, is it possible to accomplish this task with a single insert statement such as INSERT INTO table1 VALUES SELECT x,y,z FROM table2 WHERE conditions
3. Regarding step no 1, whether SQL*LOADER will be a better option to accomplish the task?
4. Regarding step no 2, is it possible to update all the 10 million records in a single update statement. If possible then what would be the optimal DB setting such as
a. Rollback Segments
b. Sort area
c. SGA
d. data block buffers
e. Sort_area_size.
5. Regarding step no 3, how should we take care that rollback segment is not filled up during the insert.
6. Regarding step no 3, will Commit after optimum interval help to reduce the chance of Rollback segment problem. Optimum Commit frequency will also apply to step 2 if single update is not feasible.
7. If we Partition the final destination table as in step no 3, will that improve the insert performance?
8. How parallel processing can help to speed up the processing considering we have 2 CPUs
9. Is there any other suggestion to speed up the process
  #2 (permalink)  
Old July 9th, 2003, 07:50 AM
Registered User
 
Join Date: Jul 2003
Location: London, , United Kingdom.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm not sure I like the approach, though of course without knowing the full details I may be wrong.

What is the purpose of the temporary table? Why not just select the data from the original base tables, join to other tables for additional info, and insert straight into the target tables?

Anyway to address your specific points (where I can):
2. yes
3. no, SQL Loader is for loading from FILES not tables.
4. yes - in fact you could probably get all the required information in the first SELECT statement.
6. no, periodic commits are generally a bad idea
9. avoid using temporary tables, cursor loops, opening/fetching/closing several cursors within cursor loops.

The ideal solution would be:

INSERT INTO target_table
SELECT ...
FROM source_table, ...
WHERE ...;

... with all of the lookups and processing done in the SELECT statement.



Tony Andrews
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Search a binary file of million records DineshGirij008 C# 18 July 17th, 2008 09:38 AM
Query to retrieve the first 10 records inserted anup_daware Oracle ASP 0 August 6th, 2007 01:50 AM
problem in updating records & finding records naveed77 VB How-To 1 January 16th, 2007 12:10 PM
Query on million of rows wkm1925 SQL Server 2000 6 October 23rd, 2006 09:30 AM
select out last 10 records markhardiman SQL Language 4 September 30th, 2004 04:45 AM



All times are GMT -4. The time now is 02:19 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.