Re: Oracle cursor help

Steve,

maybe this whole thing doesn't really belong here but anyway:

Steve Sapovits wrote:


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

How do you know that? According to a leading expert in the field of performant Oracle programming that would be the worst approach:
<quote>

*There is nothing good about row at a time processing, it is something to do ONLY when a set based operation isn't feasible*

</quote>
This is one statements of many in that thread:
http://asktom.oracle.com/pls/ask/f?p=4950:8:10872697057937345780::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6407993912330

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

So my impression is: do it with a single SQL operation (set based approach) or several set operations if you can divide the task into smaller units.
Maybe this discussion on LIOs (logical ios and how to minimize them) is also heplful:
http://asktom.oracle.com/pls/ask/f?p=4950:8:3182666695492247015::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6749454952894


As for the deletes: maybe it's worth to consider keeping the records you need and pushing them into a new table ("create table as select". Even parallelized and/or without logging; that depends). After that drop the old table and rename the new one.

Can you work with the parallel options (Oracle Enterprise Edition)?
I believe that before starting such big operations one should try to evaluate the many different options one luckily has with Oracle.

Good luck.


Regards,
  Frank.

--
Dr. Frank Ullrich, DBA Netzwerkadministration
Heise Zeitschriften Verlag GmbH & Co KG, Helstorfer Str. 7, D-30625 Hannover
E-Mail: frank.ullrich@xxxxxxxx
Phone: +49 511 5352 587; FAX: +49 511 5352 538

.

Relevant Pages

  • Re: Oracle scheme
    ... > rbscheer@my-deja.com (Robert Scheer) wrote in message ... > found that Oledb does not support Oracle cursors. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Oracle and PHP with Cursors
    ... we are using Oracle and PHP for a Web application. ... We are using ref cursors to return data from oracle ... every row is request with one call to oracle. ...
    (comp.databases.oracle.misc)
  • Oracle Client, stored procedures and Cursor release.
    ... Does anybody know if it possible to force Oracle to release cursors that ... ie My app makes a call that populates a field in a datagrid row ... so it does take more than a few grid refreshes before you hit ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Max Cursor error using Oracle
    ... adapter and increasing the OPEN_CURSORS variable to a higher number ... When too many cursors are opened, I receive the max cursor error. ... We are currently using .NET 1.1 and Oracle 9. ... Are you using the Oracle .Net driver, or the one built into VS? ...
    (microsoft.public.dotnet.languages.vb)
  • Oracle cursor help
    ... 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. ... 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 ...
    (perl.dbi.users)