RE: [sqr-users] suggestions for best practicewith insertsinto temptable

["Kevin Laroche" <bydesign@magma.ca>]

Thanks George
The reports that we have rollback problems with run 15-20 minutes and do 
10-12000 inserts.
The elderly database is being upgraded but we dont want to have capacity expand 
to fill
the space available.
I will try the /*+APPEND */ and batch commits within the SQR.  A global table 
sounded 
good until the DBA pointed out that 1 or 2 users running the large reports at 
the same 
time could cause problems for the other 100 users who are not putting any sort 
of load on
the database.
Are there functional limits on the size of an array that SQR can hold?
Is an array of 10-15,000 records with 5 columns held in a client memory PC 
going to make 
their PC as slow as molasses?

Does anyone else work with large in memory arrays in SQR?

Thanks

Kevin LaRoche
Oracle Forms Developer

On Sep 30, "George Jansen" <GJANSEN@aflcio.org> wrote:
> 
> Rollback problems depend on the rate of database change and on the
> (time) length of the query. Either you generated so much rollback that
> you filled the segment, or your query lasted umpteen hours and Oracle no
> longer has the rollback blocks to reconstruct what the tables looked
> when your query started. If you are filling up the segment, then either
> you need larger segments or you need to use a strategy, e.g. INSERT /*+
> APPEND */ that generates less undo. If your query is running too long,
> then maybe you need to rethink how it runs.
> 
> In general you'll get better performance with Oracle doing
> set-at-a-time processing:
> 
> Begin-SQL
>    INSERT INTO blah
>          (x, y, z)
>    SELECT a + b, c*d, e
>    FROM w
> End-SQL
> 
> If you must do row-at-a time processing, then perhaps you could use a
> GLOBAL TEMPORARY TABLE to hold the rows, then do a big INSERT /*+ APPEND
> */ at the end. 
> 
> Need I mention that you can't use the GLOBAL TEMPORARY TABLE for the
> next report? Even if created with ON COMMIT PRESERVE ROWS, the contents
> disappear with your session.
> 
> 

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