Tuning sql

Hi Friends,

How can I improve performance of the ff program which runs slow.
Thanks:)

============
set serveroutput on
spool telnum.lst

DECLARE
v_customer_id NUMBER := 0;
v_phone VARCHAR2(35):=NULL;
v_fax VARCHAR2(35):=NULL;
v_email VARCHAR2(50):=NULL;
v_process VARCHAR2(40):=NULL;

cursor c_customer is
SELECT cca.customer_id
FROM cax_customers_all cca;

BEGIN

select to_char(sysdate,'DD-MON-RR HH24:MI:SS') into v_process from
dual;
dbms_output.put_line('update phone ==>'||v_process);

OPEN c_customer;

LOOP

FETCH c_customer
INTO v_customer_id;

EXIT WHEN c_customer%NOTFOUND;

-- dbms_output.put_line('fetched customer
==>'||v_customer_id);

BEGIN

SELECT
decode(rp.area_code,null,'','(')||rp.area_code||
decode(rp.area_code,null,'',')
')||rp.phone_number
INTO v_phone
FROM ar_phones_v rp
WHERE rp.status = 'A'
AND rp.phone_type = 'GEN'
AND rp.primary_flag = 'Y'
AND ((rp.owner_table_id = v_customer_id
and rp.owner_table_name = 'HZ_PARTIES')
or (rp.owner_table_id in (select
ad.address_id

from ar_addresses_v ad

where ad.customer_id = v_customer_id)
and rp.owner_table_name =
'HZ_PARTY_SITES')
or (rp.owner_table_id in (select
ac.rel_party_id

from ar_contacts_v ac

where ac.customer_id = v_customer_id

and ac.status = 'A')
and rp.owner_table_name =
'HZ_PARTIES'))
AND rownum < 2
;

-- dbms_output.put_line('update customer
==>'||v_customer_id);

UPDATE CAX_CUSTOMERS_ALL
SET PHONE = nvl(v_phone,' ')
WHERE customer_id = v_customer_id
;

EXCEPTION
WHEN NO_DATA_FOUND then null;

END;

END LOOP;

CLOSE c_customer;

select to_char(sysdate,'DD-MON-RR HH24:MI:SS') into
v_process from dual;
dbms_output.put_line('end ==>'||v_process);

END
;
/
spool off

Please post available indexes, cardinalities, execution plan.

Thanks...sorry, how do I run execution plan again:o

why do you need a LOOP?

If you would include your SELECT in the UPDATE (or MERGE) it would probably be much faster.

An example out of the Oracle doc:

Code:

---

UPDATE employees a
    SET department_id =
        (SELECT department_id
            FROM departments
            WHERE location_id = '2100'),
        (salary, commission_pct) =
        (SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)
          FROM employees b
          WHERE a.department_id = b.department_id)
    WHERE department_id IN
        (SELECT department_id
          FROM departments
          WHERE location_id = 2900
              OR location_id = 2700)

---

Quote:

---

Originally Posted by yxez

Thanks...sorry, how do I run execution plan again:o

---

http://www.google.com/search?hl=en&q...e+explain+plan :rolleyes:

Thats what I think of also :) I have proven thats sqlplus updates faster
than pl/sql but "they" said here that u get much "cotrol" over the program if
you do it in pl/sql like error handling:confused:

Thanks

SQL Plus is not faster than PL/SQL. It is about time to understand that SQL and SQL Plus are different things. :-P
The update-select is faster since it is called bulk operation. No context switches between PL/SQL engine and SQL engine occurs.
If you make that update-select into a PL/SQL procedure it will not perform significantly worse than the single update-select statement.
About the control, yes that's true. If you want to commit on each 1000 rows, or if you want to see exactly update of which row fails and why, then probably that will help, although 10g has some nice features in the bulk operations which helps a lot here BUT without any commit into the loop all you have with the loop is that you will print out the exact row that failed
Cheers

Thanks my friend:)