Re: Oracle cursor help

Well, I have to echo what others have said. The only reason I can think of for using loops to do your delete is if you really want to save a chunk of rollback segment. Even then, I would consider batching the rows up into chunks that you could manage with predicates like BETWEEN or IN. Switching context from PL/SQL for the loop to SQL for the delete takes time (only a little, but it adds up). And if you're using bitmap indexes, batch deletes are much kinder than single row ones.

Martin (apologies if this is OT)

Vergara, Michael (TEM) wrote:

Steve:

I have a PL/SQL process that I use to purge rows from a table
that grows by about 14M rows/month.  Even though it's PL/SQL,
the process should be (might be?) transportable to Perl.

Basically, it looks like this:

Define SQL statement to gather the rows (PL/SQL cursor);
ctr = 0;
For each row:
  delete from table where recid = cursor.recid;
  ctr += 1;
  if ctr > 10000 {
     commit;
     ctr = 0;
  }
next

I don't know enough about the DBI to know if the 'recid'
field from PL/SQL can be used in Perl, but if you get it
to work I'd be interested in hearing about it.

Cheers,
Mike


-----Original Message-----
From: Steve Sapovits [mailto:steves06@xxxxxxxxxxx] Sent: Wednesday, June 22, 2005 11:18 PM
To: dbi-users@xxxxxxxx
Subject: Oracle cursor help


I have a few Oracle tables that have gotten way too big.  Normal
DELETE/WHERE clauses consume too many resources.  I know enough
to know that cursors would be more efficient here but I don't
know enough to actually start using them.  Does anyone have an
example or two (or three) to bootstrap me?  What I'd essentially
want to do is loop through all rows, look at a few column settings,
and delete those that fall into certain categories (e.g., certain
dates being so old and certain status fields not being set to a
set value, etc.).  I can also limit the row set up front with a
pretty simple WHERE clause -- whatever is more efficient is what
I'm aiming for (it wasn't clear to me if limiting up front with
WHERE clauses defeated the efficiency of cursors ...)





.

Relevant Pages

  • Re: Oracle cursor help
    ... If a "select * from myTable where " takes a long time, then so will a delete with the same predicate and no PL/SQL trick is going to speed it up. ... ctr = 0; ... DELETE/WHERE clauses consume too many resources. ... WHERE clauses defeated the efficiency of cursors ...) ...
    (perl.dbi.users)
  • Re: Using a String value as a RadioButton object reference
    ... for each ctr in me.controls ... > In order to try to reduce the amount of code, I created Strings inside ... > each loop which generate radio buttons names. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: LOOP blows!
    ... Your LOOP is obviously broken. ... So X will get initialized to 'at the begining of each iteration. ... clauses are executed in the loop ... repeated until a clause terminates the loop or until a return, go, or ...
    (comp.lang.lisp)
  • Re: Cursor Replacement
    ... This is a question for the database theory newsgroup. ... of cursors, I find only a few situations: ... loop to build a temp table of keys, then loop inside a loop thru a ... second table to delete rows with those keys. ...
    (microsoft.public.sqlserver.programming)
  • Re: Misusing LOOP correctly?
    ... |> consider them in light of CLHS 6.1.1.6 Order of Execution ... Does the LOOP spec allow me to rely on this behavior? ... The iteration control clauses FOR, AS, and REPEAT must precede ...
    (comp.lang.lisp)