| PERFORMANCE HIT DUE TO NVL FUNCTION [message #517905] |
Fri, 29 July 2011 07:11  |
 |
Fasy
Messages: 1
Registered: July 2011
|
Junior Member |
|
|
Hi,
I am from dev project team,we are facing a performance hit due to NVL() function,pls give a solution to resolve this issue.
the below is my function which i created to calculate some efforts.
create or replace function check_function(
v_deal_detail ,
v_tower ,
v_subtower,
v_location ,
v_client_role ,
v_emp_category ,
v_year ,
v_state )
return number
is
effort1 number(30, default 0;
begin
if v_state =1
or v_state is null
then
begin
select nvl(sum(decode (d.loc_type_id,
4,
s.trans_efforts,
0)),0)
into effort1
from effort_table s, effort2_table d
where s.deal_detail_id= d.deal_detail_id
and s.tower_id = d.tower_id
and s.location_id = d.location_id
and s.deal_detail_id = v_deal_detail
and s.client_role_id = nvl(v_client_role,s.client_role_id)
and s.emp_category_id = nvl(v_emp_category,s.emp_category_id)
and s.tower_id = v_tower
and s.subtower_id = nvl(v_subtower,s.subtower_id)
and s.location_id = nvl(v_location,s.location_id)
and s.year_no = v_year;
exception
when no_data_found
then
effort1 := 0;
end;
end if;
if v_state = 1
then
return effort1;
end if;
end;
pls give me a solution.
Regards,
shinu
|
|
|
|
| Re: PERFORMANCE HIT DUE TO NVL FUNCTION [message #517912 is a reply to message #517905] |
Fri, 29 July 2011 07:34  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:I am from dev project team,we are facing a performance hit due to NVL() function
What make you think it is the NVL function?
For any peformances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
In addition, this question has been addressed twice in the last 2 weeks so, please, search before posting.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
