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.

KK wrote:
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

KK wrote:
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

On Tue, 12 Sep 2006 06:44:58 -0700, DA Morgan <damorgan (AT) psoug (DOT) org>
wrote:

Wrong. The purpose of this exercise is YOU do his work, and he still
doesn't know anything about Oracle, after he got a valid response.

--
Sybrand Bakker, Senior Oracle DBA