| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL not as easy as it looks.
The problem is your rollback space.
Create a rollback segment in an tablespace with enough disk space as:
create rollback segment rbbig tablespace XXXXXXX storage(initial 10M next 10M
minextents 2 maxextents 200);
alter rollback segment rbbig online;
The rollback segment will grow up to 200 MB.
the first statement in your pl-sql must be
set transaction use rollback segment rbbig;
or put an commit in the fetch loop, which commits every transaction immediately.
Kevin O'Gorman wrote:
> I'm trying my first PL/SQL program, to recode an update to a summary
> table that was originally three SQL commands. The result is a program
> that looks right, and works on very small inputs, but runs very
> slowly and crashes before finishing on real inputs (it fails in extending
> a rollback segment).
>
> I could sure use some pointers.
>
> I've got a modest sized (10K rows) delta table I'm using to update a
> large (6M rows) summary table. The SQL had an UPDATE, an INSERT and
> a DELETE command, and had cache behavior I thought I could improve by
> making the update/insert/delete decision on a row-by-row basis.
>
> Here's the code, modified to remove substitution variables which just
> allow me to use different table names. I use two cursors, the outer
> one (c1) scans the delta. The inner one (cview) looks up
> the corresponding record in the summary (if one exists) using the
> fields of its primary key. There will be at most one such record, so
> that part is not a loop. The code looks clean to my newbie eye, and
> I really don't know what I did wrong, but it must be something.
>
> The rollback segments are large enough to accomodate a copy of the
> entire summary table, so I have no clue what I'm doing that would
> exhaust that resource.
>
> HELP???
>
> -----------------------------------------------
>
> declare
> deltarec DELTA%ROWTYPE;
> cursor c1 is
> select * from DELTA;
> updates integer;
> deletes integer;
> inserts integer;
> begin
> updates := 0;
> deletes := 0;
> inserts := 0;
> for deltarec in c1 loop
> declare
> viewrec SUMMARY%ROWTYPE;
> cursor cview is
> select /*+ FIRST_ROWS */ * from SUMMARY w
> where
> w.w0_supp_nation = deltarec.w0_supp_nation and
> w.w0_cust_nation = deltarec.w0_cust_nation and
> w.w0_shipmonth = deltarec.w0_shipmonth and
> w.w0_ordermonth = deltarec.w0_ordermonth and
> w.w0_partkey = deltarec.w0_partkey and
> w.w0_suppkey = deltarec.w0_suppkey;
> begin
> open cview;
> fetch cview into viewrec;
> if cview%FOUND then
> if viewrec.w0_count = - deltarec.w0_count then
> deletes := deletes + 1;
> delete from SUMMARY w where
> w.w0_supp_nation = deltarec.w0_supp_nation and
> w.w0_cust_nation = deltarec.w0_cust_nation and
> w.w0_shipmonth = deltarec.w0_shipmonth and
> w.w0_ordermonth = deltarec.w0_ordermonth and
> w.w0_partkey = deltarec.w0_partkey and
> w.w0_suppkey = deltarec.w0_suppkey;
> else
> updates := updates + 1;
> update SUMMARY wu
> set
> wu.w0_quantity = wu.w0_quantity + deltarec.w0_quantity,
> wu.w0_volume = wu.w0_volume + deltarec.w0_volume,
> wu.w0_cost = wu.w0_cost + deltarec.w0_cost,
> wu.w0_count = wu.w0_count + deltarec.w0_count;
> end if;
> else
> inserts := inserts + 1;
> insert into SUMMARY
> (
> w0_supp_nation,
> w0_cust_nation,
> w0_shipmonth,
> w0_ordermonth,
> w0_partkey,
> w0_suppkey,
> w0_quantity,
> w0_volume,
> w0_cost,
> w0_count
> ) values (
> deltarec.w0_supp_nation,
> deltarec.w0_cust_nation,
> deltarec.w0_shipmonth,
> deltarec.w0_ordermonth,
> deltarec.w0_partkey,
> deltarec.w0_suppkey,
> deltarec.w0_quantity,
> deltarec.w0_volume,
> deltarec.w0_cost,
> deltarec.w0_count
> );
> end if;
> close cview;
> end; -- inner block
> end loop; -- c1 loop
> delete from counts;
> insert into counts
> values(inserts, deletes, updates);
> end; -- outer block
> /
>
>
> --
> Kevin O'Gorman (805) 650-6274 kogorman_at_pacbell.net
> At school: kogorman_at_cs.ucsb.edu
> Permanent e-mail forwarder: Kevin.O'Gorman.64_at_Alum.Dartmouth.org
--
 |
 |