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.

I can and I can't. Using trim() effectively disables any indexes you have on the source tables, resulting in a full table scan. Unless you have function-based indexes on those columns, and then you'd need query_rewrite_enabled=true. I can't say much more because you haven't posted the loop this select is in, so there is no indication of how this loop runs. Is there a specific reason you're using trim() on every record in abc, especially since l_name, db, c_id, a_id, g_id, e_date and t_date are all varchar2 columns? And I'm particularly curious as to why you'd make date columns (e_date, t_date) of the varchar2 type; this is a recipe for disaster as Oracle can't determine that string value '03/01/2006' immediately follows '02/28/2006' so the CBO presumes there are far more entries between those two values and can, and will, skew the cost calculations most likely making your situation worse instead of better. Had those been dates Oracle would realise what you'd stored and calculate selectivity and cardinality accordingly and your performance would likely improve.

Also, I wonder how 'real' this example is; it looks more like an oversimplified case which, most often, doesn't help you much as many of the pertinent details from the actual query are missing.

However, to recap:

Using trim() on virtually every column in your query effectively disables any standard indexes created on those columns.

Storing dates as varchar2 strings is a mistake creating a performance hit.

Post the actual code, or at least the actual loop being executed, and there might be more information available.

David Fitzjarrell Received on Tue Sep 12 2006 - 08:21:49 CDT