Re: [sqr-users] Insert -SQR

["George Jansen" <GJANSEN@aflcio.org>]

Are you inserting 8M records or are you inserting fewer records into a table 
alreading having 8M?

In general, you want to avoid, where possible, record-at-a-time 
select/process/insert work. Is it possible to move the lookups into a join and 
the validations into a "where", i.e

INSERT INTO TARGET (....)
SELECT SOURCE.*, LOOKUP1.VALUE1, LOOKUP2.VALUE2
FROM SOURCE, LOOKUP1, LOOKUP2, VALIDATIONS 
WHERE SOURCE.CODE1 = LOOKUP1.CODE1
    AND SOURCE.CODE2 = LOOKUP2.CODE2
    AND SOURCE.V1 = VALIDATIONS.V2

This could be a lot more efficient.


>>> Lisa.Smyth@arnold.af.mil 10/31/02 02:30PM >>>
I inherited this program.  I have been asked to improve performance because
it is taking a very long time. 


I have an SQR program that inserts a record into a table with over 8 million
records, needless to say this is very costly to the database(Oracle) as
noted in the EXPLAIN PLAN..   

The process reads a record from the source table and does some validations
and lookups of additional values and then inserts the record into the target
table then repeats the process by reading a record from the source table and
so on. 

Someone suggested disabling the index while inserting and then rebuilding it
after it is finished inserting.   

Any suggestions would be great or where to go lookup some additional info.
I have read some of the Oracle Database Performance Guide and Reference
material. 

Thanks, 

Lisa 


Lisa Smyth
PeopleSoft Financials
931.454.4861
Lisa.Smyth@arnold.af.mil 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users