>Did you notice that neither of the criteria in the WHERE clause is involved
>in the joins?

Yes.

>Did you notice the low selectivity of the index you suggested?

Nope, I assumed CODE was fairly selective for both CLIENT and CLNT_SUB.
Isn't it the case?

Ahh, and no, I didn't notice that POS_ID had only 4 possible values. Then
we should ask him to drop all such indexes (which you already did) and ask
if SHIFT_NUM is more selective and a potential candidate for indexing.

>You did notice the fact that there were composite indexes stepping on one
>another but you didn't notice the low selectivity of all of them.
>
>You didn't make any recommendations about indexing for the joins as far as
>I can tell. But maybe you posted something that I missed...

>Assuming CODE is fairly selective, the following two indexes should help you:
>
>CREATE INDEX IDX_CLNT_SUB_CODE ON CLNT_SUB(CODE); //Here I should have

added SUB_CODE as well, but didn't notice.

>CREATE INDEX IDX_CLIENT ON CLIENT(CODE);

Set