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.















Relevant Pages

  • Best practice question: Updating Aggregates
    ... Write a summary cursor which does the "group by" in the SQL ... Cons: Possible data integrity issues - what if the WHERE clause on the ... Aggregation occurs at 2 levels, plus several aggregate columns ...
    (comp.databases.oracle.misc)
  • Re: VFP and understanding repackaged cursors
    ... and add a NOFILTER or READWRITE from there. ... the indicies in the where clause and receiving back a 'filtered view' ... or 'repackaged cursor' of the table and not a real cursor. ...
    (microsoft.public.fox.programmer.exchange)
  • VFP and understanding repackaged cursors
    ... the indicies in the where clause and receiving back a 'filtered view' ... or 'repackaged cursor' of the table and not a real cursor. ... readwrite clause or a nofilter clause to force the cursor to be ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Select / Scan / Select / Endscan
    ... The first Select statement generates a cursor called ... "Query" because you don't have an Into Cursor/Table clause in your code. ... It occurs when the code hits ENDSCAN the first time. ...
    (microsoft.public.fox.helpwanted)
  • Re: vim question - comment multiple lines
    ... >> Place cursor at the beginning of the first line you want to change. ... the ':help' (sect 10.5 Visual Block Mode). ... Do you Yahoo!? ... easy-to-use web site design software -- redhat-list mailing list unsubscribe mailto:redhat-list-request@redhat.com?subject=unsubscribe ...
    (RedHat)