HELP - are use of cursors discouraged in large queries ?

[Advocate]

hello again ... tried this in Oracle 7 forum but no replies ...

currently testing update of 3 fields in approx 45k records in table of 430k records on oracle 7.x

pl/sql package is VERY slow updating; took about 21 hours to update about 12k records yesterday; took about 2 hours this morning to update first 1k records and another 2 hours to update next 1k records

talking with colleagues and considering options; database tuning is not an option due to lack of suitable client's resources - also fiddling with indexes etc not allowed

read back through forums on slow processing and one article caught my eye from this time last year; prosenjit commented on 30 nov 2000 that "Though cursors are discouraged ..."

does this apply to any particular type of work ? any advice please ?

also, past experience has shown that inserting required records into temporary tables instead of using cursors can speed things up - any comments ?

trying to read up on optimisation - anyone have any tutorial links please ? how can i check which method is currently being used (rule-based or cost-based ?)

thank you in anticipation ...
Best of Irish Luck, David.
djwilkes@hotmail.com

 

[sem]

Everything depends on your table structure and pl/sql code.

If your table has a lot of foreign key constraints the productivity may suffer. Another reason is waiting for blocked resources. In this case you may try to use NOWAIT to delay processing (process immediately only available rows). In any case try to analyze locks.

BTW, is your case so complex that you can not use pure sql?
You may write to sam@eximb.com, I'll try to help you.