🚀 go-pugleaf

InterBase 2007 SP2 specifically addressed the optimizer, so plans with 
it may be different. Hopefully, they are usually better.

Peter Batten wrote:
> I have code which looks like
> 
> Select * from PERSONAL p , LINKPERSADDR lpa, ADDRESS a , LINKADDRTELE lat,
> TELEPHONY t
> where  t.UPPERPACKEDNUMBER Starting With "0385316661"  and
> p.PERSONALAUTONO = lpa.PERSONALAUTONO and
> lpa.ADDRESSAUTONO = a.ADDRESSAUTONO and
> lat.ADDRESSAUTONO = a.ADDRESSAUTONO and
> t.TELEPHONYAUTONO = lat.TELEPHONYAUTONO
> order by p.UPPERNAME
> 
> On one machine running IB 8.1.0.257 this returns a plan of
> 
> PLAN SORT (JOIN (A NATURAL,LPA INDEX (LINKADDRPERS),P INDEX
> (RDB$PRIMARY5),LAT INDEX (RDB$PRIMARY24),T INDEX
> (UPPERTELEPACKEDNUMBER,RDB$PRIMARY6)))
> which is using Natural on ADDRESS A and is terribly slow
> 
> But on a different machine running IB 8.0.0.123
> version
> I get a plan of
> PLAN SORT (JOIN (T INDEX (UPPERTELEPACKEDNUMBER),LAT INDEX (LINKTELEADDR),A
> INDEX (RDB$PRIMARY1),LPA INDEX (LINKADDRPERS),P INDEX (RDB$PRIMARY5)))
> and very fast.
> 
> All indexes exist on each machine.
> 
> Why are these two different plans generated. I don't particularly want to
> add a plan statement to the statement as this is generated on the fly and
> would need many conditionals to build the correct plan each time.
> 
> Any help appreciated
> 
> Regards
> 
> Peter
> 
> 
> 
> 
>