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  |
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 #334495 is a reply to message #334491] |
Wed, 16 July 2008 20:20  |
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 #334505 is a reply to message #334491] |
Wed, 16 July 2008 21:23 |
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 #334615 is a reply to message #334491] |
Thu, 17 July 2008 06:42 |
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 |
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 |
|
|
|
|
|
Goto Forum:
Current Time: Thu Jun 27 19:55:01 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
