Re: Oracle cursor help

ditto....and I'd add that if your delete is taking forever to run that you may want to consider getting some indexes on the columns you are using to filter for your delete. If a "select * from myTable where <insert your predicate here>" takes a long time, then so will a delete with the same predicate and no PL/SQL trick is going to speed it up. You may be able to thow in a hint, depending on how the table is structured/indexed, and it's also possible that running stats on the table wil have a positive effect on the speed.

Martin Hall wrote:

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
    ... Switching context from PL/SQL for the loop to SQL for the delete takes time ... ctr = 0; ... DELETE/WHERE clauses consume too many resources. ... WHERE clauses defeated the efficiency of cursors ...) ...
    (perl.dbi.users)
  • Slight "I have some string, how lng it it, BTW, its blue" question
    ... I appreciate that with PL/SQL being rather more effective than, ... compare like with like. ... Now any any other RMDBS, at least I've worked with (SQLServer, SQLAnywhere, ... Sorry it this isn't clear - the reasoning behind some of the use of cursors ...
    (comp.databases.oracle.misc)
  • Re: Slight "I have some string, how lng it it, BTW, its blue" question
    ... can you expand on what you mean by 'Using cursors to do your join'? ... As I stated before....if you can do it in SQL, choose that approach over PL/SQL. ... So if you have a view call a view, and your write SQL to reference the first view, Oracle will merge all the views into your SQL statement and then execute the entire thing as one large SQL statement. ...
    (comp.databases.oracle.misc)
  • Re: Slight "I have some string, how lng it it, BTW, its blue" question
    ... the rule of thumb I use is if it can be done with SQL ... If not, then use PL/SQL. ... can you expand on what you mean by 'Using cursors to do your join'? ... flexible that a COBOL programmer can write COBOL in PL/SQL. ...
    (comp.databases.oracle.misc)
  • RE: Oracle cursor help
    ... I have a PL/SQL process that I use to purge rows from a table ... Even though it's PL/SQL, ... Define SQL statement to gather the rows (PL/SQL cursor); ... DELETE/WHERE clauses consume too many resources. ...
    (perl.dbi.users)