Re: large delete

From: joel garry <joel-garry_at_home.com>
Date: 21 Jun 2006 16:34:25 -0700
Message-ID: <1150932865.736761.111070@i40g2000cwc.googlegroups.com>

Robert Klemme wrote:
> Sybrand Bakker wrote:
> > On Tue, 20 Jun 2006 16:21:27 -0700, Akimeu <Nospam_at_Email.com> wrote:
> >
> >> hello,
> >>
> >> i have done some looking around for this, but haven't really found a
> >> solid answer. we have an sp, to which we're passing in a table name,
> >> after which we execute immediate a delete statement. is there any way
> >> of committing within this particular delete?
> >>
> >> any insights/suggestions are appreciated. thanks.
> >>
> >> this is what i was thinking of doing:
> >>
> >> loop
> >> vsql:='delete from '||vschem||'.'||vtablename||
> >> ' where '||vcol||'='''||vid||''' and rownum <= 10000';
> >> execute immediate vsql;
> >> exit when sql%rowcount = 0;
> >> commit;
> >> end loop;
> >
> > do NOT commit every n records. You will be hitting 'snapshot too old'
> > because of this.

>

> Could you elaborate this a bit? Your answer isn't clear to me. It
> seems, that Tom suggests to do exactly this (i.e. working only on a
> subset of the data) to *avoid* ORA-01555 "Snapshot too old":

>

> "3. Run the processing against a range of data rather than the whole
> table. (Same reason as 1)."

>

> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:275215756923
>

In addition to what Sybrand points out, if you poke around on asktom and find where people ask about deleting millions of rows, he suggests creating another table with the records you want to keep, deleting and renaming the original. Or something like that. He also emphasizes the general superiority of doing things in sql rather than loops when possible, and especially don't commit in loops.

jg

--
@home.com is bogus.
Makes me happy as a little girl!
http://www.signonsandiego.com/uniontrib/20060621/news_1b21myspace.html

Received on Wed Jun 21 2006 - 18:34:25 CDT