-
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
Behind The Success And Failure Of A Man Is A Woman
-
Please post available indexes, cardinalities, execution plan.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Thanks...sorry, how do I run execution plan again
Behind The Success And Failure Of A Man Is A Woman
-
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)
-
Originally Posted by yxez
Thanks...sorry, how do I run execution plan again
http://www.google.com/search?hl=en&q...e+explain+plan
-
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
Thanks
Behind The Success And Failure Of A Man Is A Woman
-
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
Last edited by Bore; 08-23-2007 at 09:50 AM.
-
Thanks my friend
Behind The Success And Failure Of A Man Is A Woman
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|