Re: pl/sql tables and cursors

On 1 Dec 1997 07:51:36 GMT, GreMa_at_t-online.de (Matthias Gresz) wrote:

>John Strange wrote:
>>
>> I had to load 3.6 million rows.
>> Used a loop with a cursor and had to do a
>> commit every 20,000 records.
>> Picked 20,000 based on the rollback segment sizes.
>>
>> This was requried becuase I would fill up the rollback logs.
>> You seem to have a slight varient of the same problem.
>> Your database hang is a space problem on some system/data/log file.
>>
>> A. Davidson (dradave_at_ionet.net) wrote:
>> : I am attempting to load 15000 + records from a temporary table
>> : declared as 25 columns of varchar2(60) into a pl/sql table for later
>> : processing into production tables.
 

>> : I'm creating a cursor with select * from the temporary table to
>> : populate the pl/sql table using a cursor for loop.
 

>> : I do this twice, once with a cursor in a select statement using where
>> : exists against the temporary table and production table to populate an
>> : update pl/sql table and a select where not exists to populate a add
>> : new record pl/sql table.
 

>> : This method seems to work fairly well using small amounts of test
>> : data...2000 records or less. But when I do a full scale test using
>> : all 15000 plus records, it hangs the DB and I have to shut it down
>> : then restart it. Sometimes it will work properly on the add routine
>> : and sometimes on the update routine but never both. I need to do the
>> : update this way to make sure I don't update an existing value with a
>> : blank from the update file, so I have to perform null checks on the
>> : incoming data. This is essentially a reload every day so all 15000 +
>> : records are updated daily. (I can't drop and reload).
 

>> : I'm using the default setup for the database when it was installed.

 With large objects like that, I would no recommend using the  default database settings. Set up tablespaces for the rollback  segments, temporary objects and the user data. Ussually I will  create a 100 mb temp tablespace and 100 mb for the rollback  segments create a few rollback segments with a large initial extent  storage (initial 10m next 10m pctincrease 0) that way you don't end  up with a lot of fragmentation in your rollback tablespace.

 If the object you are loading is big ( greater than 10 mb ) put it  into its own tablespace. And if there are a few indexes put them  into their own tablespace.

  Also if you are still using the default init.ora, you should  probably adjust it. The are examples of settings in the default  init.ora file for a small, medium and large database. It will make  a huge difference in the performance of the database. The  Server Administrator's guide covers the init.ora settings.

>>
>> : I guess my question is:
 

>> : 1. How many rows can a cursor hold?
>> : 2. How many rows can a pl/sql table hold?
 

>> : I know this also depends on number of columns and their datatypes, I
>> : can't seem to find this information in the documentation.
 

>> : Thanx for any info supplied...
 

>> : A. Davidson
>> : dradave_at_ionet.net
>>
>> --
>> This posting represents the personal opinions of the author. It is not the
>> official opinion or policy of the author's employer. Warranty expired when you
>> opened this article and I will not be responsible for its contents or use.
>
>Hi,
>
>why don't you use a psecial adequate sized rollback segemnt for your
>job?
>
>Then set transction to use this segment.
>
>
>SET TRANSACTION USE ROLLBACK SEGMENT <segment name>;
>--
>Regards
>
>Matthias Gresz :-)

• ========= ========= ========= ========= Noah Monsey noah_at_azlink.com
• ========= ========= ========= ========= The only dumb question is the one that you don't ask.
• ========= ========= ========= ========= Oracle Master Database Administration April 1992 Oracle Master Application Development April 1992
• ========= ========= ========= =========