Hi all,

I have some questions about the Firebird 1.03 execution plan.

In a query like this:

SELECT A.SOME_COLUMN FROM
A INNER JOIN B ON B.SEQ_1 = A.SEQ_1 AND B.SEQ_2 = A.SEQ_2
INNER JOIN C ON C.SEQ_1 = A.SEQ_1 AND C.SEQ_2 = A.SEQ_2
WHERE A.COL_1 = 10 OR A.COL_1 = 30

* A PK is (SEQ_1, SEQ_2, SEQ_X), B PK is (SEQ_1, SEQ_2), C PK is (SEQ_1, SEQ_2, SEQ_Y) and there's an indice for A.COL_1.

The IB Expert's plan analyzer indicates a table scan (natural) on table C.

My question is: why doesn't it look for the registers in table A wich attends the indice on COL_1 and join them with the table B and then (INNER) join the result with table C by it's PK indice? Is it because C's PK has 3 fields and the join only use two of them? Anyway, if i create a indice for table C (SEQ_1,SEQ_2) the table scan goes to table B.

Second question: is it a problem if i create many indices for a table?

And the last question: how does i know (or choose) the kind of indice i'm creating? I have a large table in wich any indice i create has statistics of 0,2 and destroy the system's performance.

Thanks in advance,

Filipe Knoedt

[Non-text portions of this message have been removed]