Hi again !,

First of all, thanks to everybody for the help, you're nice people.
Indeed Set, only that one index. I've made mistake, I
mentioned "Unique index", but SU_NR, SU_SUB is the primary key for
SUBPROJ. Here is the code from IBExpert :

/*********************************************************************
*********/
/**** Primary
Keys ****/
/*********************************************************************
*********/

ALTER TABLE SUBPROJ ADD CONSTRAINT SUBPROJPRIMARYKEY1 PRIMARY KEY
(SU_NR, SU_SUB);

Here are the statistics from SUBPROJ :

SUBPROJ (222)
Primary pointer page: 2741, Index root page: 2742
Data pages: 94, data page slots: 103, average fill: 57%
Fill distribution:
0 - 19% = 19
20 - 39% = 15
40 - 59% = 0
60 - 79% = 37
80 - 99% = 23

Index RDB$PRIMARY75 (0)
Depth: 2, leaf buckets: 8, nodes: 562
Average data length: 3.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 5
60 - 79% = 0
80 - 99% = 3

Should the value of "leaf buckets" be high or low ? Because I found
high values in gstat.

Thanks again,
Nico


--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

>
> --- In firebird-support@yahoogroups.com, "Nico Callewaert" wrote:
> > Hi Set,
> >
> > This is also frustrating sometimes. There are quite a lot of rows
> > in SUBPROJ. I have a unique index defined on the fields SU_NR

and

> > SU_SUB. But it seems firebird is refusing to use that index. I
> > don't like really so many left joins, but the customer wants to

see

> > al the related information in the grid.
>
> Hi Nico!
>
> Are you sure that index is active and that you have no other index

or

> key starting with SU_NR and SU_SUB as the first two fields (Firebird
> at least used to be confused if having two identical indexes to

choose

> from)? The other parts of your query seems fine, so if you don't

find

> anything, post statistics from the SUBPROJ table (including all
> indexes and keys).
>
> Set
>
> > > > FROM CSFHPX FH
> > > > LEFT OUTER JOIN KLPX KL ON KL.KL_COD = FH.FH_KLNR
> > > > LEFT OUTER JOIN PROJPX PR ON PR.PR_NR = FH.FH_PROJ
> > > > LEFT OUTER JOIN SUBPROJ SU ON (SU.SU_NR = FH.FH_PROJ)
> > > > AND (SU.SU_SUB = FH.FH_SUBPROJ)
> > > > LEFT OUTER JOIN VERTEW VE ON VE.VE_COD = FH.FH_AGT
> > > > LEFT OUTER JOIN TRANSPORTEUR TR ON
> > > > TR.TR_CODE = FH.FH_TRANSPORTEUR
> > > > LEFT OUTER JOIN WEDERVERKOPERS WE ON
> > > > WE.WE_COD = FH.FH_WEDERVERKOPER
> > > > WHERE (FH.FH_CODE = 'B')
> > > >
> > > > Plan
> > > > PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (
> > > > FH INDEX (FH_CODE_INDEX),KL INDEX (RDB$PRIMARY36)),
> > > > PR INDEX (RDB$PRIMARY55)),SU NATURAL),
> > > > VE INDEX (RDB$PRIMARY76)),TR INDEX (RDB$PRIMARY167)),
> > > > WE INDEX (RDB$PRIMARY292))
>