Hi,

> /* Indices definition */
> ***A LOT OF REPEATED*** //But helped me on other queries
> CREATE INDEX IDX_TB_INSPECT_STATUS ON TB_INSPECT (STATUS);

As already answered in another post the STATUS is the problem here.
The question is if you really need the index.

> ***A LOT OF NULLS*** //
> CREATE UNIQUE INDEX IDX_TB_INSPECT_WUP_DET ON TB_INSPECT
> (WRITE_UP_DET_ID);

This one has also many duplicates (75%) and when there are many duplicates
you should really ask the question to yourself if you need the index.

> Again, I think the optimizer should go to statistics and determine
> when to use an index and when not.

Yes and it does on estimated values.
I ask you before, did you run "SET STATISTICS INDEX <index-name>" ? and is
this on FB1.5 or FB1.5.1.
There are thresholds for when a index will be used or not, so i'm curious
about your index-selectivity.

Run this query and post the results :

SELECT
CAST(i.RDB$RELATION_NAME AS CHAR(25)) AS RELATION_NAME,
CAST(i.RDB$INDEX_NAME AS CHAR(25)) AS INDEX_NAME,
CAST(s.RDB$FIELD_POSITION AS CHAR(3)) AS SPOS,
CAST(s.RDB$FIELD_NAME AS CHAR(15)) AS FIELD_NAME,
CAST(rs.RDB$FIELD_ID AS CHAR(3)) AS FID,
i.RDB$STATISTICS AS STATS
FROM
RDB$INDICES i
JOIN RDB$INDEX_SEGMENTS s ON (s.RDB$INDEX_NAME = i.RDB$INDEX_NAME)
JOIN RDB$RELATION_FIELDS rs ON (rs.RDB$FIELD_NAME = s.RDB$FIELD_NAME and
rs.RDB$RELATION_NAME = i.RDB$RELATION_NAME)
WHERE
i.RDB$RELATION_NAME IN ('TB_INSPECT')
ORDER BY
i.RDB$RELATION_NAME, i.RDB$STATISTICS, i.RDB$INDEX_NAME,
s.RDB$FIELD_POSITION

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