RE: Oracle cursor help

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 ...)

--
Steve Sapovits steves06@xxxxxxxxxxx
.

Relevant Pages

  • Re: add a new range partition in a existing table
    ... i want add a new range partition in a existing table? ... CURSOR context_date_cur IS ... Where in any Oracle doc did you ever see double quotes like this? ... And what about this requires PL/SQL or a CURSOR LOOP? ...
    (comp.databases.oracle.misc)
  • Re: DBD::Oracle - closing cursors returned from PL/SQL
    ... and demonstrates that a cursor got from PL/SQL ... Give a hobbit a ring and he eats fish for an age. ...
    (perl.dbi.users)
  • Re: oracle leaving open cursors...
    ... > We have a database driven servlet/jsp application with Oracle as backend. ... > What exactly is a cursor after I hav used it? ... > when I close the connection, or do I always have to close the cursor ... & "returnacursor" is a pl/sql subroutine that returns a cursor,then you ...
    (comp.lang.java.programmer)
  • PL/SQL
    ... Ich habe mich in den letzten Tagen in PL/SQL eingelesen, ... Jede SELECT Abfrage wird intern als temporäre Tabelle ... Wann genau brauche ich einen Cursor, bzw. wann brauche ich ihn nicht? ... Ohne Cursor muss man ein SELECT schreiben, dass nur genau eine Zeile zurückgibt. ...
    (de.comp.datenbanken.misc)
  • 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)