Home » RDBMS Server » Performance Tuning » replacing with single update statement (Oracle 10g)
replacing with single update statement [message #334491] Wed, 16 July 2008 19:49 Go to next message
kumarbo7
Messages: 19
Registered: March 2008
Location: New Jersey
Junior Member
Hi,

Please help me out is there any modifications can we do to optimize and shorten the query.

The inner loop makes no point to keep. if we find that a error exists then we set the status to FAIL else we set it PASS.
Ideally this whole below query can be optimized to be one single update statement that runs through and updates accordingly.

even if we have to keep that inner loop can we keep the cursor has row num <=1


Quote:
BEGIN
-- set the line status as PASS or FAIL
FOR c_get1 IN c_get_lines (p_proc_id_i)
LOOP
UPDATE xcs_top_all_stg
SET inter_status = 'PASS'
WHERE p_id = c_get1.p_id
AND q_h_id = c_get1.q_h_id
AND q_l_id = c_get1.q_l_id;

FOR c_get_errs1 IN c_get_errors (c_get1.p_id,
c_get1.q_l_id
)
LOOP
UPDATE xcs_top_all_stg
SET inter_status = 'FAIL'
WHERE p_id = c_get1.p_id
AND q_h_id = c_get1.q_h_id
AND q_l_id = c_get1.q_l_id;
END LOOP; -- End of (FOR c_get_errs1)
END LOOP; -- End of (FOR c_get1)


-Kumar

Re: replacing with single update statement [message #334493 is a reply to message #334491] Wed, 16 July 2008 20:03 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated above

I guess you know what you are talking about, but I certainly don't.

>if we find that a error exists
based upon exactly which test or exception since none is posted.
Re: replacing with single update statement [message #334495 is a reply to message #334491] Wed, 16 July 2008 20:20 Go to previous messageGo to next message
kumarbo7
Messages: 19
Registered: March 2008
Location: New Jersey
Junior Member
Thanks anacedent

Version which am using : Oracle 10.2.0.3.0
Operating system : windows xp professional

Please help me out is there any modifications can we do to optimize and shorten the query.

The inner loop makes no point to keep. if we find that a error exists then we set the status to FAIL else we set it PASS.
Ideally this whole below query can be optimized to be one single update statement that runs through and updates accordingly.

even if we have to keep that inner loop can we keep the cursor has row num <=1

Quote:
BEGIN
-- set the line status as PASS or FAIL
FOR c_get1 IN c_get_lines (p_proc_id_i)
LOOP
UPDATE xcs_top_all_stg
SET inter_status = 'PASS'
WHERE p_id = c_get1.p_id
AND q_h_id = c_get1.q_h_id
AND q_l_id = c_get1.q_l_id;

FOR c_get_errs1 IN c_get_errors (c_get1.p_id,
c_get1.q_l_id
)
LOOP
UPDATE xcs_top_all_stg
SET inter_status = 'FAIL'
WHERE p_id = c_get1.p_id
AND q_h_id = c_get1.q_h_id
AND q_l_id = c_get1.q_l_id;
END LOOP; -- End of (FOR c_get_errs1)
END LOOP; -- End of (FOR c_get1)

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 200));



Please let me know if I miss anything or ignored the rules of forum.

-Kumar
Re: replacing with single update statement [message #334496 is a reply to message #334491] Wed, 16 July 2008 20:34 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Repeating the same words does not make them any more understandable.

>if we find that a error exists
Anywhere in this application?
In the inner loop?
In the outer loop?
In the exception handler?

The posting Guidelines stated you should provide DDL to create all table & DML (INSERT) to populate table(s) with sample data.

You are to clearly post expected/desired output.

What is "p_proc_id_i" & where is it defined?

What is the purpose/goal of the EXCEPTION handler code?
Re: replacing with single update statement [message #334505 is a reply to message #334491] Wed, 16 July 2008 21:23 Go to previous messageGo to next message
kumarbo7
Messages: 19
Registered: March 2008
Location: New Jersey
Junior Member
Version which am using : Oracle 10.2.0.3.0
Operating system : windows xp professional

Sorry,please ignore the old one here is the complete query,

I am not getting any error in my query,but I want to shorten the query by removing inner loop keeping only one update statement and one loop,trying to get the output same what previously the query works.

Quote:
PROCEDURE status1 (
p_id_i IN NUMBER,
p_module_i IN VARCHAR2,
p_err_code_o OUT NOCOPY VARCHAR2,
p_err_msg_o OUT NOCOPY VARCHAR2
)
IS
up_fd1 EXCEPTION;
l_dummy VARCHAR2 (1);

CURSOR c_g_errs1 (c_p_id_i IN NUMBER, c_q_l_id_i IN NUMBER)
IS
SELECT q_h_id, q_l_id, object_name, ERROR_TYPE,
error_severity, error_access, status
FROM cs_top_err_msg_logging_stg
WHERE 1 = 1
AND UPPER (active) = 'Y'
AND UPPER (error_severity) NOT IN
('WARNING', 'TOP', 'INFORM')
AND p_id = '204'
AND q_l_id = '%';

CURSOR c_g_lines1 (c_p_id_i IN NUMBER)
IS
SELECT q_h_id, q_l_id, p_id
FROM cs_top_l_all_stg
WHERE p_id = c_p_id_i
AND p_module_i = 'UPLOAD'
AND l_s_id IN
(cs_top_cons.g_id, --'Invalid',
cs_top_cons.g_b_id, --'Blank',
cs_top_cons.g_e_id, --'Entered',
cs_top_cons.g_n_id --'New'
)
UNION ALL
SELECT q_h_id, q_l_id, p_id
FROM cs_top_l_all_stg
WHERE p_id = c_p_id_i
AND p_module_i IN ('ORD_TOP', 'CON_GIVE')
AND l_s_id IN
(cs_top_cons.g_id, --'Invalid',
cs_top_cons.g_b_id, --'Blank',
cs_top_cons.g_e_id, --'Entered',
cs_top_cons.g_n_id, --'New',
cs_top_cons.g_v_id, --'Valid',
cs_top_cons.g_i_v_id --'Internally Validated'
);
BEGIN
-- After local validation , set the line status
-- as LOCAL_PASS or LOCAL_FAIL
FOR c_g_l1 IN c_g_l (p_id_i)
LOOP
UPDATE cs_top_l_all_stg
SET i_l_sta = 'PASS'
WHERE p_id = c_g_l1.p_id
AND q_h_id = c_g_l1.q_h_id
AND q_l_id = c_g_l1.q_l_id;

FOR c_g_errs1 IN c_g_errs (c_g_l1.p_id,
c_g_l1.q_l_id
)
LOOP
UPDATE cs_q_l_all_stg
SET i_l_sta = 'FAIL'
WHERE p_id = c_g_l1.p_id
AND q_h_id = c_g_l1.q_h_id
AND q_l_id = c_g_l1.q_l_id;
END LOOP; -- End of (FOR c_g_errs1)
END LOOP; -- End of (FOR c_g_l1)
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 216));
);
END status1;
Re: replacing with single update statement [message #334522 is a reply to message #334491] Wed, 16 July 2008 23:24 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
use merge statement,
or you can use sigle update without loop

[Updated on: Wed, 16 July 2008 23:29]

Report message to a moderator

Re: replacing with single update statement [message #334615 is a reply to message #334491] Thu, 17 July 2008 06:42 Go to previous messageGo to next message
kumarbo7
Messages: 19
Registered: March 2008
Location: New Jersey
Junior Member
Thanks kriptas

I used single update statement,keeping PASS and FAIL in one update statement taking one loop.but I don't know somewhere I did wrong,query shows incorrect.

if possible please guide me with syntax.

-Kumar

Re: replacing with single update statement [message #334762 is a reply to message #334491] Thu, 17 July 2008 19:15 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
something like that
UPDATE cs_top_l_all_stg
SET i_l_sta=CASE WHEN  p_module_i IN ('ORD_TOP', 'CON_GIVE') 
                 THEN 'PASS'
                 WHEN  p_module_i IN ('UPLOAD')
                       AND l_s_id IN
                      (cs_top_cons.g_id, --'Invalid',
                       cs_top_cons.g_b_id, --'Blank',
                       cs_top_cons.g_e_id, --'Entered',
                       cs_top_cons.g_n_id --'New'
                      )
                 THEN 'PASS'     
            end;          
WHERE  p_module_i IN ('ORD_TOP', 'CON_GIVE','UPLOAD')
      
and  l_s_id IN
(cs_top_cons.g_id, --'Invalid',
cs_top_cons.g_b_id, --'Blank',
cs_top_cons.g_e_id, --'Entered',
cs_top_cons.g_n_id, --'New',
cs_top_cons.g_v_id, --'Valid',
cs_top_cons.g_i_v_id --'Internally Validated'
);


but I think your provided script has errors, so better describe what you want to do and why.
ALL updates 'PASS' and 'FAIL'
can be done in one merge statemen
but, in your scrip there are lots of errors, so i cant understand it well, and can't understand the purpose of sucht selects ant loops.
you need to white down good select which select from boths tables identificators which rows needs to be updated

[Updated on: Thu, 17 July 2008 19:23]

Report message to a moderator

Re: replacing with single update statement [message #334767 is a reply to message #334762] Thu, 17 July 2008 22:40 Go to previous message
kumarbo7
Messages: 19
Registered: March 2008
Location: New Jersey
Junior Member
Thanks allot Kriptas for your reply

I do changes as you mentioned.

sorry for confusion i will error free the query,rerun the query with changes which you mentioned.

-Kumar
Previous Topic: Oracle runs SLOWER on XEON than Pentium Dual
Next Topic: Effect on physical standby database
Goto Forum:
  


Current Time: Fri Dec 09 15:23:14 CST 2016

Total time taken to generate the page: 0.09619 seconds