Large Amounts of Data

[David Anderson <DANDERS1@SMTPGW.IS.HFH.EDU>]

The original problem was that we were trying to load 80K rows from flat
file into PS_JOB (conversion).  For the conversion I had to ensure
(insure?) the employee was not holding a concurrent job and the row
was not duplicating a date.  To do this I had to read what was already in
JOB.  The catch... the flat file did not contain the employee's emplid thus
eliminating the easy select from PS_JOB.  The initial workaround was
joining JOB to PERSONAL_DATA via EMPLID and utilizing
PERSONAL_DATA.SSN as the search (after indexing SSN on
PERSONAL_DATA, of course).  Things were looking good, but alas the
job ran for 20 hours and this was not good.

Don't despair!  Using SQR's LOAD LOOKUP TABLE allows quick access
to data.  LOAD LOOKUP creates a two column array once (yes, once)
which is indexed on the first column.  In this case I load
PERSONAL_DATA.SSN as the index and EMPLID as the return value.
Voila!  I now have cross reference of SSN and EMPLID without any
Oracle/SQL hits to the DB.  This eliminated the need for the join.  Result...
cue the music... the job ran in 35 minutes.  NICE performance boost.

Thanks to everyone who responded...
Dave