Re: WHY SQL-Statement without bind-prameters runs 50 times faster as with ??????

In article <390F8165.A745A66_at_ns.sympatico.ca>,   lori.pat_at_ns.sympatico.ca wrote:
> Yes, to_date is a function, and when you put a function on the left
 side of
> your where clause statements, Oracle does full table scans.
>
> There is a good book on how to tune SQL, SQL High Performance Tuning
 by Guy
> Harrison, he goes into a lot of detail.
>
> Bind variables are annoying because you can't use explain plan with
 them, you
> have to trace the session, see in the trace file what variables were
> substituted, and plug them into the statement. Even then there is no
 guarantee
> that Oracle will do what the query's explain plan shows with the
 values placed
> directly inside it.
>
> (I wish I could speak German!)
>
> Pat.
>

I do not understand your statement that "Bind variables are annoying because you can't use explain plan with them, you have to trace the session," since this is not true. You can explain a statement with bind variables in it, and the manner in which Oracle resolves a query with bind variables in it can be very different from the same query written with constants where the bind variables belong even when histograms do not exist. I agree with you that Guy Harrison's book Oracle SQL High-Performance Tuning is excellent.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.