Oracle performance questions

I just inherited some programs that are used to create datasets. I have some performance related questions and would appreciate your response.

Two report datasets are being created using 2 tables (Oracle 8i, UNIX on HP V series).

Table 1 - has unformatted data (e.g. SSN is in the format ********* instead of ***-**-****). This is the larger table with about 600,000 records for each month.

Table 2 - lookup table. About 2500 records.

This is how the PL/SQL program works:

Step 1:

Create a cursor 'ValidLookup' as (select 'Lookup ID' from table 2 where lookup flag = '1').

Step 2:

For each 'Lookup ID 'in cursor 'Validlookup', select records from Table 1 and build a flat file.

Format some fields as part of the process (SSN, Phone Number) for each record selected usinf decode.

My questions:

1. What is the overhead in using the'decode' function within PL/SQL? There are 4 fields being decoded for every record being created.
2. Will performance be better if instead of creating the cursor in step 1, we just join table 2 to table 1 as part of process in step 2 and eliminate step 1 altogether?

The above reports are run as 2 versions. One uses a subset of the data as the other. Both select almost the same set of columns as cursors and do similar processes upto a point but format the outputs differently before creating the report datasets. The datasets end up being around 400 MB each.

Is it better to create a dataset first using the common cursor code and use that dataset to create these reports or is it better to go against the database and create the cursors for each report?

thanks
Raj

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 26 2000 - 19:10:37 CDT