Hi,

> I think the problem is that FK_CCF_GRANTEEINVOICE is not useful here, as
> currently ccf.GRANTEE_INVOICE is always null, as nothing has been
> invoiced yet using our new system. It would also be nice if it would
> use the index on POSTED.
> So how do I get the optimizer it to stop using FK_CCF_GRANTEEINVOICE, or
> at least to use PK_CLAIMSCALCFEES first?

This will avoid using the index on GRANTEE_INVOICE (adding zero or concatenating empty string if
it's a character field)

WHERE
ccf.GRANTEE_INVOICE + 0 IS NULL AND
ccf.POSTED >= ? AND
ccf.POSTED <= ?

Beside that also take attention to your LEFT JOIN, because the table "CLAIMAUDIT ca" doesn't use any
indexes.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
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 Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.8.4 - Release Date: 27-3-2005