Hi Hans,

> All joins on PK's (not NULL) .
> Index selectivities about the same, if present.

> FB1.5 .. without the two left outer joins

> PLAN SORT (SORT (JOIN (M NATURAL,T INDEX
> (TICKETS_REF_TICKET_NO,TICKETS_MUD_UNIQUENUM),S INDEX
> (RDB$PRIMARY12,RDB$PRIMARY12),W INDEX (RDB$PRIMARY21),O INDEX
> (RDB$PRIMARY5))))

> FB2.0 .. without the two left outer joins
>
> PLAN SORT (SORT (JOIN (JOIN (W NATURAL, S INDEX (SITES_HOUSE_LSD_CUST), O
> INDEX (RDB$PRIMARY5)), JOIN (T INDEX (TICKETS_REF_TICKET_NO), M INDEX
> (RDB$PRIMARY5)))))

It seems tickets is more selective then our optimizer calculates.
Can you give some information (fields, statistics) on index TICKETS_REF_TICKET_NO and
TICKETS_MUD_UNIQUENUM?

> FB1.5 .. with the two left outer joins
>
> PLAN SORT (SORT (JOIN (JOIN (JOIN (JOIN (T INDEX (TICKETS_REF_TICKET_NO),S
> INDEX (RDB$PRIMARY12,RDB$PRIMARY12)),O INDEX (RDB$PRIMARY5)),W INDEX
> (RDB$PRIMARY21)),M INDEX (RDB$PRIMARY5))))

> FB2.0 .. with the two left outer joins
>
> PLAN SORT (SORT (JOIN (JOIN (JOIN (JOIN (S NATURAL, T INDEX
> (TICKETS_REF_TICKET_NO)), O INDEX (RDB$PRIMARY5)), W INDEX (RDB$PRIMARY21)),
> M INDEX (RDB$PRIMARY5))))

I guess same goes on here for the filter on the tickets table.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database development support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Firebird and Interbase users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info

--------------------------------------------------------------------------------
Mijn Postvak In wordt beschermd door SPAMfighter
1078 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis SPAMfighter vandaag nog!