Re: Oracle cursor help - retry

sorry for the earlier aborted message.

yahoo mail mysteriously sends messages while I am
still typing sometimes. some magic key I hit on my
laptop submits the form.

I was including a sample of "where current of"

but before that:

1. Size Rollback for non-incremental commits
2. Don't do Manual parallelism, use parallel DML and
let Oracle do it automatically. The docs highlight
the advantages of Oracle Parallel DML vs. Manual
Parallelism.

As a side note: For a large delete, DIY parallelism
may result in index scans over the entire table
(because you broke it up into enough pieces to make it
think that index scan was the way to go), but
collectively, it would have been faster to full scan
since you index-scanned such a large portion in total.

This is especially true if you are deleting any
sizable portion of the table.

3. consider partitioning for easy data roll-off/purge,
if the criteria you purge on can be part of a
partioning key this would be an almost instaneous
purge.

4. If you really want to see the cursor approach here
is a function you could call from Perl I copied from
somewhere online.

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

CURSOR c1
IS
SELECT course_number, instructor
from courses_tbl
where course_name = name_in
FOR UPDATE of instructor;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;

else
DELETE FROM courses_tbl
WHERE CURRENT OF c1;

COMMIT;

end if;

close c1;

RETURN cnumber;
END;

---------------------

Job





____________________________________________________
Yahoo! Sports
Rekindle the Rivalries. Sign up for Fantasy Football
http://football.fantasysports.yahoo.com
.