Re: Optimization: strange behavior with :1

From: Willy Klotz <willyk_at_kbigate.stgt.sub.org>
Date: 1995/07/23
Message-ID: <806494341snx_at_kbigate.stgt.sub.org>#1/1


boj_at_cti.ulaval.ca writes in article <boj.35.0010034A_at_cti.ulaval.ca>:
>
> 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..

Assuming you are using the rule-based optimizer....

The optimization decision is not made for every execution of the statement. Rather the access path is chosen before the required data is actually available.

So, your host-variable at runtime could contain the string '%abc%'; and what else than a full table-scan sould get you the correct result ?

>
> Thank you very much
>
> Jean-Marc Boivin
> System analyst
> Universite Laval
> Ste-Foy, Quebec
> Canada
>
>
>
>

 Willy Klotz
>


Willys Mail     FidoNet              2:2474/117  2:2474/118    
      willyk_at_kbigate.stgt.sub.org      VFC        ISDN/X.75
   -> No Request from 06.00 to 08.00 <-
Received on Sun Jul 23 1995 - 00:00:00 CEST

Original text of this message