Help: Break record on PL/SQL

From: gees <gees_at_hkstar.com>
Date: 1997/07/11
Message-ID: <01bc8e17$cc006060$713652ca@hkstar.com>#1/1

Hi all,

I am a newbie in Oracle arena. I hope someone out there could give me some good suggestions on improving my PL/SQL procedures.

The scenario:

Everyday, we need to extract data from an event table to produce a flat file. The event table will have at least 20000 records/day. Under the flat file, we need to produce nine different message lines mapping to from the transaction code on event table for each different countries.

Now, the persudeo code is:

Declare
-- message A cursor

        Cursor A from temporary table where ...;
-- message B cursor

	Cursor B ...;
	...
	Cursor J ...;

Begin

Select all unposted message to a temporary table;

For each different country

	For each different message (i.e. message A to J)
		Open Cursor for the corresponding country and message
		Write to flat file
		Update event table to indicate that message has been posted
	end for

end for
End;

Right now, we write our PL/SQL programs in Oracle Forms45 under Win95. It takes about 3 minutes to process about 2000 records. In production, we expect that there will be 20000 records on each run. We would like to cut it down to less than 15 minutes. We suspect that the most time consuming is the mapping process by using the cursor to map the transcation in event table to the corresponding cursor message.

If u have any suggestions to improve the performance, we greatly appreciate to hear from u.

'o' GT
/O\ Received on Fri Jul 11 1997 - 00:00:00 CDT