Hi Tom,

I need ideas to tune codes.

How to avoid calling pl/sql function for each row in select / update statement for example:-

select empno, get_name(empno) from employees;

and get_name is a function that will retrieve the names of the employee "just an example forget about normalization"

or update employees set name = get_name(empno) where condition;

This is an example but in my company all the developers is using same technique and complaining about slowness.

I was thinking about bulk process but I will end up with select statements for each row in the collection and same problem happen again.

in oracle documentation it tell me to change the SQL statement it self "join the base table with the lookup tables" to avoid the select inside the loop but there is a lot of lookup tables

for example select empno, get_name(empno), get_address(address_id), get_email(email_id) from employees;

so for every column they have to bring the data by calling a procedure

we are dealing with millions of rows in the select or update statements.

Regards,
Khalid.