At 09:06 PM 17/08/2005 +0000, you wrote:

>For some reason, I can't get rid of the table scan on the Element
>Names table even though the Element Names ID is it's primary key. Any
>ideas? Selectivity of this index is very good since all values are unique.
>
>SELECT
> EVers.ID, Elems.nodeID, Elems.ID, EAttribs.MD5
>FROM Vers
> JOIN Elems ON EVers.elementId = Elems.ID
> JOIN ENames ON Elems.nameId = ENames.ID
> JOIN EAttribs ON EVers.attribsID = EAttribs.ID
>WHERE
>(
> Elems.lastVersionID = EVers.ID AND
> Elems.nodeID = -9223372036853775797
>)
>
>I get this:
>
>PLAN SORT (JOIN (ENAMES NATURAL,ELEMS INDEX (ELEMS_13_ASC),VERS INDEX
>(RDB$PRIMARY20,VERS_1_ASC),ATTRIBS INDEX (RDB$PRIMARY18)))
>
>Any idea why this would be doing a natural on ENames? If I put in the
>primary key index for Enames into the plan, I get an invalid index for
>query exception.
>
>Btw, this is 1.0.3. We are unable to upgrade to 1.5 at this point.

Apart from your typo (Vers? Evers?), the situation here is that eNames is
doing nothing in this query except acting as a retrospective selector for
the other tables - that is, the query is not interested in any other data
in the eNames table except the primary key. Since these are all inner
joins, what would you lose by inverting this query so that the leftmost set
is the Elems rows that are selected by the join with ENames? In this case,
the primary key index of ENames will be used.

Also, you have pushed a JOIN criterion into the WHERE clause (see
Elems.lastVersionID = EVers.ID). Though it's sometimes useful with certain
outer joins to push a WHERE criterion into the JOIN clause, it's never
appropriate to relegate a JOIN condition into the WHERE clause.

I'll restructure this query using aliases to make it easier to see what is
doing what to what:

SELECT
v.ID, e.nodeID, e.ID, a.MD5
FROM Elems e
JOIN ENames n ON n.ID = e.nameID
JOIN EVers v
ON v.elementId = e.ID AND v.ID = e.lastVersionID
JOIN EAttribs a ON a.ID = v.attribsID
WHERE
e.nodeID = ? /* -9223372036853775797 */

That relationship between EVers and Elems looks suspiciously circular to
me, as well...it looks as if it needs resolving with an intersection table
or, perhaps, for this query, a re-entrant join on EVers..are you actually
getting the expected output?

./heLen