Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
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
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

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
FROM source_table, ...
WHERE ...;

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

Tony Andrews

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

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.