--- In firebird-support@yahoogroups.com, "Grzegorz Modlinski" wrote:

> Hi,
>
> I read the discussion of 'Outer join full scan' but to be honest it
> does not give a clue what's wrong with following queries.
>
> Query A - causes full scan of zza_tasktypes and zza_tasks (more than
> 100000 records)
> Query B - is neat and fast - there are only few reads of
> zza_tasktypes and zza_tasks
>
> The only difference is a dummy 'where' condition which is always
> true. Plans for both queries are totally different. Plan for B uses
> only primary indices, and plan for A uses some set of foreign keys.
>
> Query A:
> select h.*, c.cli_name, u.usr_name, tz.tza_name
> from zza_taskhistory h
> join zza_tasks z on h.hza_tsk_id=z.tsk_id
> join zza_users u on h.hza_usr_id=u.usr_id
> join zza_tasktypes tz on tz.tza_id=z.zad_tza_id
> join zza_clients c on z.zad_cli_id=c.cli_id
> where h.hza_date=:date
>
>
> Query B:
> select h.*, c.cli_name, u.usr_name, tz.tza_name
> from zza_taskhistory h
> join zza_tasks z on h.hza_tsk_id=z.tsk_id
> join zza_users u on h.hza_usr_id=u.usr_id
> join zza_tasktypes tz on tz.tza_id=z.zad_tza_id
> join zza_clients c on z.zad_cli_id=c.cli_id
> where h.hza_id>0 and h.hza_date=:date
>
> Hmmm,
> thats all?
> How this could be written better, to avoid loooong full scan?

Hi Grzegorz!

I think your query B seems pretty good. What you are doing, is simply
fooling the optimizer into believing that starting the plan with
zza_taskhistory is the best it can do. Query A may use an index on
zza_taskhistory.hza_date, but the optimizer (falsely) believes it can
do better with another plan (either that or you do not have any index
on hza_date). Maybe you could make query A choose a better plan if you
modified (or created if you do not have this index) your index on
hza_date to (hza_date, hza_id). For any further advice, you would have
to show us the generated plans as well as tell us a bit about the
tables and the indexes used.

HTH,
Set