"One technique that has been shown to improve performance significantly in SQR data conversion programs, is to replace the driving cursor with the use of an array loaded with the ROWIDs that would have been selected by the driving cursor. Then, the subsequent updates are performed using the ROWIDs in the array. NOTE: This also eliminated "Snapshot too old " problems since there was no SELECT statement open for a long period of time."

Tom do you support the above approach. In what situations we should consider the above approach , apart from the one mentioned above.

Tom, you are a strong proponent of doing everything in SQL, but what about the i/o traffic generated by these nested subqueries. What do you have to say about the below

"
SQL statements with nested subqueries can create an enormous amount of i/o traffic. Listing 1 is a correlated subquery where the outer query is executed one time for each row returned by the inner query. Listing 2 produces the same result but at a fraction of the i/o as it queries the look up table (the subquery of Listing 3) once, not once per each row.
update EMP
set sal = sal * 10
where exists
(select ‘x’ from DEPT
where DEPT.deptno = EMP.deptno)

Correlated SQL statement
Listing 3

DECLARE
cursor c1 is select deptno from dept;
work_deptno number;
BEGIN
open c1;
loop
fetch c1 into work_deptno;
EXIT when c1%NOTFOUND
update emp
set sal = sal * 10
where deptno = work_deptno;
end loop;
END;
"

Tom, in what units can we measure the i/o trffic, is there a way we say that the i/o traffic was units 100 etc.

Thank you

for an UTTERLY long discussion on IN vs EXISTS and NOT IN vs NOT EXISTS.  

You would use tkprof to analyze IO for a statement.  See

Distinguish between network io and logical io. What is the difference between them.

Hi Tom,

In your latest follow-up above, you recommend considering the single statement update, i.e. update emp set sal = sal*10 where deptno in ( select deptno from dept );

In other postings on your website, you recommend using single statement DML where possible, e.g. "insert into... select * from...", or "delete from... where col1 in...", and not involve PL/SQL at all.

In my experience however, users, managers and even some developers like to know which record, if any, caused an error/failure when the statement fails, which necessitates using either the bulk collect/forall method, or the row-by-row method, where a problematic record can be easily captured. And even though the single statement update/insert/delete is by far much more quicker and elegant, in the event of a failure, it does not provide any information about the offending record that caused the failure, which is critical to prevent an excessive amount of time being spent on troubleshooting and figuring out the erroneous record. Further, in many instances I have seen the need for such offending records to be included in a report of some kind, that can then be sent to a user/manager, who can then make the necessary changes to allow the failing record to succeed in the next run.

My questions to you are, why has Oracle not provided for this need to capture offending records in the very statements that are purported to be efficient and elegant i.e. the single statements? Secondly, if I wanted to argue in favor of these single statements versus the row-by-row methods, what arguments can I make to my managers/users? Performance is obviously one thing, but the need to know which record caused a failure frequently outweighs any gains in performance around here. Though I understand their point-of-view as well. Any pointers there? Finally, is using the bulk collect/forall method the next best thing to single statement DML? Thanks.

I mentioned your latest follow-up above, but it's actually the response you gave to Omer's last posting. Sorry for the confusion.

the link to tkprof.html is broken

Between your site and metalink, I have found everything I could need for self study, its been great. (Great stragg example without having to use a cursor!)

My question is what is a "best practice" to hide schema information from application code? I recently have been creating ASP.NET pages and had been using views until I hit a wall when updating views with a JOIN, so now I am looking into stored procedures.

Could you also paste or reference a basic 1-2-3 primer on creating a stored procedure to accomplish your example a few posts back? This would cover the gambit I am dealing with and get me by.
Here is the reference I am talking about::
" delete from t where x = ?;
update t2 set c = c*1.5 where y = ?;
insert into t3 values ( ?, ?, ? );
commit;

you should write a procedure that does the four and just call:

begin proc( ?, ?, ?, ?, ? ); end;

from your application."

Hi Tom..

In ORACLE Forms, I need to read a text file from the client Machine and then looping through each record in the text file i need to do one insert and two updates in the database.

Now my question is, Should we put the whole logic in ORACLE FORMS program unit OR Should we create a procedure in the database for insert and updates and call that procedure inside the loop for every record in the file.

Can you please suggest me that which one is best option and why ?

Thanks & Regards
Ravi Kumar

Can you please give me some idea how to do this, or can you please show me some example.

I have a text file with 20 columns and thousands of rows in it. How can I pass it all to database in a single go.

Thanks & Regards
Ravi Kumar

Other option is to use Global Temporary tables. You can read & insert records into global Temporary tables and then manipulate using stored procedure

Is there any difference between using a function and a procedure other than the basic difference?

1. Function can be used in SQL statements but procedure can.
2. Function must return a value while procedures don¿t.

Say you are writing a package and you want to put a set of code together which one you will go for, a Procedure or a Function and how will you make that decision.

Regards,

Karthick.
http://karthickarp.blogspot.com/