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 alway 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?

Grzegorz Modlinski




[Non-text portions of this message have been removed]