From: "Alexander V.Nevsky" <ded@...>
To: <firebird-support@yahoogroups.com>
Sent: Thursday, April 29, 2004 2:57 PM
Subject: [firebird-support] Re: Possible BUG in PLAN Optimizer ? FB 1.5.4290

> <helmut.hartl@f...> wrote:
> > A big hello to all experienced users !
>
> Helmut, should unexperienced users modestly go to another room? :)

Of course not :-) / I just wanted to indicated that this problem not seems
to be quite trivial to me,
and i wanted to save time for me in reading trivial answers, and for others
in writing trivial
answers - which was of course only a kind wish :-))

> BTW, when anwsering is something bug in some version or not and what
> about future version, it is very convenient to know version which you
> use :)

Maybe reading the Subject could have helped:-))
->> Possible BUG in PLAN Optimizer ? FB 1.5.4290 <<-

That is the Release Version of FB 1.5. I tested it on Linux with RC7 CS and
under
Windows with embedded FB 1.5.4290.

> I have'nt at hand just now FB1.5 release build, only experimental
> ones LI-V1.5.0.4075 Firebird 1.5 RC7 (already outdated) and
> LI-V1.5.1.4354 (in field test stage). On both using your metadata
>
> select a.f1,a.f2,b.f1,b.f2,c.f1,c.f2 from t1 a, t2 b, t3 c
> where a.id=b.t1id and b.id =c.t2id
> and a.f1 is null and a.f2 is not null
>
> on empty tables reports plan
>
> PLAN JOIN (A INDEX (T1_IDX1),B INDEX (FK_T2),C INDEX (FK_T3))
>
> Perhaps you use ancient engine or long ago did'nt refreshed indices
> statistics.

No ancient version, and polished indices :-)

Try this one and you should see what i mean:

INSERT INTO T1 (ID,F1,F2) VALUES (1,NULL,NULL);

-> PLAN: PLAN JOIN (B NATURAL,A INDEX (T1_IDX1,PK_T1),C INDEX (FK_T3))

I think you can imagine what this means if it was used with much more
records.
I used for my testing (3 Days by now including many different SQL
Variations)
a Test Data generator and created 5000-80000 Records per
Table including all Values from -MAXINT to +MAXINT including NULL.

> > Or does anyone now how to get the PLAN for a given Select ?
>
> Using isc_dsql_sql_info API call on prepared statement. If you use
> Delphi for application development, you can find example of getting
> plan in IBSQL unit source.
>
> Best regards,
> Alexander.
>

Thanks, this was a realy good Tip! So i can experiment a bit.

maybe, you have another good idea on the initial Problem.

thanks & best regards

Helmut