RE: [sqr-users] Insert -SQR

[Alan.Ljungberg@cnpl.enbridge.com]

Be careful using partitioned tables if this is a PeopleSoft environment.
Partitioning a table will force it into a SQLs into a COST based optimizer.
PeopleSoft reacts upredictably (usually bad) to COST based SQL
optimization.  My recommendation is to stay away from partitioned tables in
PeopleSoft unless someone has done extensive impact analysis on it.

-Alan.



                                                                                
                                      
                    Greg Rabinovich                                             
                                      
                    <odecca25@yahoo        To:     sqr-users@sqrug.org          
                                      
                    .com>                  cc:                                  
                                      
                    Sent by:               Subject:     RE: [sqr-users] Insert 
-SQR                                   
                    sqr-users-admin     Expire On:                              
                                      
                    @sqrug.org                                                  
                                      
                                                                                
                                      
                                                                                
                                      
                    10/31/2002                                                  
                                      
                    01:09 PM                                                    
                                      
                    Please respond                                              
                                      
                    to sqr-users                                                
                                      
                                                                                
                                      
                                                                                
                                      




Another thing you may want to try is to partition this
table.  If your 8 million record table is broken down
and partitions reside in different tablespaces, this
may dramatically improve performance.

Greg.


--- "Knapp, Richard" <KnappR@umsystem.edu> wrote:
>
> Hi Lisa,
>
> I agree with your consultant on dropping and
> rebuilding the indexes.  Also, make sure you use
> indexed fields for your joins and use as many of
> them as possible in both main and sub queries.
>
> I have had some success speeding things up in
> loading values from small tables used for lookup
> into arrays.  You just have to try this to see if it
> will work for you.  Put a timer routine in your
> program and call it from key points to see just
> where the slow up is.
>
> Richard Knapp
> Database Programmer/Analyst
> Institutional Research and Planning
> University of Missouri System
> 573-882-8856
> knappr@umsystem.edu
>
>
> -----Original Message-----
> From: Smyth Lisa G Contr AEDC/ACS
> [mailto:Lisa.Smyth@arnold.af.mil]
> Sent: Thursday, October 31, 2002 1:31 PM
> To: sqr-users@sqrug.org
> Subject: [sqr-users] Insert -SQR
>
>
> 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


__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/

_______________________________________________
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