Re: Questions on hard coded vs bind variable performance

On Mon, 04 Apr 2005 13:02:43 -0700, dave_93_98 wrote:

> I am currently using Oracle 8.1.7 on a sun sparc computer. I have a query
> that runs extremely fast with hard coded variables in the where clause for
> date parameters (which are actually char(18)). When I change this query to
> be in an anonymous sql block (or a procedure which takes start and end
> times) it suddenly takes 30-40 times as long.
>
> Select trn.lot, MAX(txn_id) As txn_id into nLot, nID
> From transaction trn, history his
> Where trn.his_ID = his.his_ID
> -- And txntime between cStartTime and cEndTime
> And txntime between '20040904 000000000' and '20040905 000000000' Group
> by trn.lot

The reason for this is, that with hardcoded parameters the optimizer can see that your condition is rather selective, while with bind variables it assumes it is not.

By the way, do versions above 8.1.7 have a hint to tell the optimizer the selectivity of a condition? Would be something to look forward for.

Sincerely,
Joachim

-- 
Warnung: \" kann Augenkrebs verursachen.