Re: rollback chocking up ... why? cursor to blame??

A copy of this was sent to c.undieseastwood_at_gu.edu.au (Chris Eastwood) (if that email address didn't require changing) On Tue, 22 Jun 1999 07:12:03 GMT, you wrote:

>HiYa
>
>I am trying to insert into database X from database Y across sqlnet. I am
>wanting to get records from table AAA on X and insert these into AAA on Y, the
>definitions of the tables are the same.
>
>As I have dropped the indexes to make the process faster I wanted to ensure
>that I didn't bring in any that would preclude a unique index on 4 of the
>columns, I chose a corelated subquery.
>
>I have the process driven by a cursor, and run it on the Y instance:
>

[Quoted] You don't have the error message so I'll guess that it is 1555 snapshot too old. [Quoted] If so, you are causing it yourself by committing. Also, some of your assumptions above are incorrect.

Your assumption that "i wanted to ensure that i didn't bring in any that would preclude a unique index..., i chose a correlated subquery" is incorrect. The multi-versioning and read consistency model we use makes it so that the instant you opened the cursor:

>cursor CUR_INSERT is
>select *
>from AAA_at_X a
>where not exists (select 'x'
> from AAA b
> where b.IND_1 = a.IND_1
> and b.IND_2 = a.IND_2
> and b.IND_3 = a.IND_3
> and b.IND_4 = a.IND_4);
>

the contents of the AAA table was fixed at that exact point in time. It does not matter how many records you put into AAA, that cursor will *not* see them since they did not exist when the query was opened.

When you commit, you are allowing the system to reuse the rollback you used. the proble is that we need that rollback in order to present your initial query with the read consistent view of AAA. As you go through this process, you are causing the 1555.

Remove the 'where not exists', if AAA is empty to begin with, it will appear that way to the cursor for the duration of the cursor. Also, consider using the sqlplus copy command with a commit count. it'll be heaps faster then doing this with plsql. Lastly, create the unique constraint with an exceptions into after the fact to find the duplication rows and get rid of them...

>then there is a loop on the cursor, with a commit every 5000 rows
>
>IE
>
>for c_ins_rec in CUR_INSERT loop
>
>LINE_CNT := LINE_CNT + 1 ;
>
>insert into AAA
>values (
>
>c_ins_rec.blah
>);
>
> if LINE_CNT = 5000 then
> LINE_CNT := 0;
> commit;
> end if;
>
> end loop;
> commit;
>END;
>/
>
>I would have thought that this would _NOT_ clogg up the rollback space, and
>would move along fine.
>
>Is this correlation back into the table being inserted into going to effect
>the operation of the cursor???
>
>
>See Ya
>(when bandwidth gets better ;-)
>
>Chris Eastwood
>Photographer, Programmer email ua.ude.ug.xobliam_at_doowtsae.c
>Motorcyclist and dingbat
>
>please remove undies for reply
>************************************************************************
>Ohhh ... you work all day, slave over a hot stove all night
> yet you *still* have time for sadomasochism ... how do you do it?
>A little man hurts me.
><ding>
> Oh Mister Mean ... Oh Mister Mean
> 15 Minutes with him and you'll be blue and green
> Studded belts and leather whips
> Bondage trousers on his hips
> Wack and punish as you dust with Mister Mean
> Ohhhh he whips me around the house in minutes ... lets get cracking ...

-- 
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation