Data Retrieving very slow!

Hi all,

Could anybody please help me out with this???

I am actually trying to retrieve data by using all the possible filter fields but then my query performance is very poor..
Its actually taking a second or so and thats a part of a function and when i run the main query which calls the function then the entire data is fetched in 26 seconds which is quite long...

When i run the query which is a part of the function ,using a filter field which is the basis for my data to be classified the query returns the data in 1 second and when i remove that filter field then it takes 30ms. But as i told you that filter field is very important and the basis for my classification...

So please suggest some remedy and also faster as this is very urgent.

Thanks.

The best advice I can give you is to make easy for others to understand your problem, this can be accomplished by posting the relevant DDL and DML statements.
Also note that these is a free forum where people come on a volunteer basis so there is nothing like "very urgent".

CREATE OR REPLACE function COUNT_ISSUES_Test(NAME IN VARCHAR2,TEMP1 IN NUMBER,TEMP2 IN NUMBER)
return NUMBER as
temp_count number;
begin
select count(*) into temp_count
from Kcrt_requests_v
where request_type_name='Visionplus Application Issues' and batch_number=1 and visible_parameter27='VisionPlus Prod Support - UK'
and substr(parameter4,1,10)<=to_char(sysdate-TEMP1,'yyyy-mm-dd')
and substr(parameter4,1,10)>=to_char(sysdate-TEMP2,'yyyy-mm-dd')
and status_code='IN_PROGRESS' and status_name=NAME;
return temp_count;
end;
/


Well this is the query that i was talking about.

Here the most important filter field is status_name which i am passing to the function.
And as i have already mentioned... when i dont use this filter field the query returns the count in 30ms but the same query takes 1 sec along with the 'status_name' filter field.

Also the view which i have used 'kcrt_requests_v' has at present 1500 records only.