Hi,

> ID is Colins primary key. Surely an index on (<PK>, <DuplicateField>)
> is never desireable?

I'll try to explain it.

When we have this query:

SELECT
*
FROM
TableA a
JOIN TableB b ON (b.ID = a.ID)
WHERE
b.Status = 8

If used plan would be (two single indices):
PLAN JOIN(A NATURAL, B INDEX (IDX_TableB_ID, IDX_TableB_STATUS))

Then egine retrieves for every record in A all record-numbers from both
indices that meet given status and id. This means with many duplicates on
status many record-numbers will be returned. Finally it compose a bitmap
where a "and" between those two returned sets is done. This bitmap is
finally used to retrieve the (in our example only 1) a record from B.


If used plan would be (1 compound index):
PLAN JOIN(A NATURAL, B INDEX (IDX_TableB_ID_STATUS))

Then egine retrieves for every record in A all record-numbers from the index
that meet given id, status. This means maximum only 1 record-number is
returned if ID is a primary key. This record-number is used to retrieve the
a record from B.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird links :
http://www.firebirdsql.com
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/


Nederlandse firebird nieuwsgroep :
news://80.126.130.81