Optimization: strange behavior with :1

HI!,
 We have a performance problem with LOV in f4.5. So we analyse the situation and find this VERY!!! strange situation:

Suppose a simple table like that

VEHICULE (IMMATRICULATION VARCHAR(7)) /* simple */ Suppose there is 400000 rows and a index on IMMATRICULATION.

If I execute the following statement:

select * from vehicule
where immatriculation like 'RHC%'

You thing that the explain plan would be:

  TABLE ACCESS BY ROWID VEHICULE
        INDEX RANGE SCAN I_IMMA and your right!

But if I execute the following statement:

select * from vehicule
where immatriculation like :1 /* that's what Forms45 does with record group */

The explain plan is

TABLE ACCESS FULL VEHICULE ?????????????????????????????????

Why does Oracle perform a full table scan when using host variable with LIKE???? I can't figure any good reason for that so if anybody can help me..

Thank you very much

Jean-Marc Boivin
System analyst
Universite Laval
Ste-Foy, Quebec
Canada Received on Wed Jul 19 1995 - 00:00:00 CEST