Re: SQL help pls

KK wrote:
> Can anybody tell me why the following query is taking time to execute
> when I use in side the loop in stored procedure
>
> select count(*) into abcfrom r_output where
> trim(both from l_name) = cRec.l_name and trim(
> both from db) = cRec.db and trim(both from f_id) = cRec.f_id and
> trim(both from c_id) = cRec.c_id and trim(both
> from a_id) = cRec.a_id and trim(both from g_id) = cRec.g_id and
> trim(both from e_date) = cRec.e_date and
> trim(both from t_date) = cRec.t_date and f_key = inFKey and s_f_key =
> inSFKey;
>
> all variables are of type varchar2 except f_key, s_f_key which are of
> integer type
>
> Thanks.

Your Oracle installation doesn't have a version number and, apparently, EXPLAIN PLAN is not working. ;-)

Seriously ... we would need to see/know

1. Oracle version
2. Explain Plan (created using DBMS_XPLAN)
3. What indexes exist (the DDL please)
4. Are optimizer statistics current and created using DBMS_STATS

-- 
Daniel Morgan
Puget Sound Oracle Users Group