Re: Oracle cursor help

Steve,

Delete from table where ....

will be much much more efficient than anything involving a cursor

and a "where current of" clause to delete the row you are iterating over the cursor.

think about what delete does. it identifies only the rows that need to be deleted, and deletes them in bulk, you are talking about breaking that into two steps. a select to identify the rows (potentially more than you need to delete) and than in procedural code deleting one at a time based on some conditional test.


Job

Steve Sapovits <steves06@xxxxxxxxxxx> 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
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


---------------------------------
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.