At 05:25 PM 13-07-00 +0100, you wrote:

>You post got me looking through my code to see what I am doing in these
>'Lookup table situation. But I get the opposite results to you when using
>an ORDER BY, and I think it is the optimiser messing up..
>
> SELECT
> DATA_TBL.ID,
> LOOKUP_TBL.NAME,
> DATA_TBL.DATE
> FROM DATA_TBL
> JOIN LOOKUP_TBL ON DATA_TBL.LOOKUP_ID = LOOKUP_TBL.ID
> ORDER BY DATA_TBL.DATE DESC
>
>Uses this PLAN, as expected..
>
>PLAN JOIN (DATA_TBL ORDER IDX$DATA_TBL_DATE,LOOKUP_TBL INDEX
>(RDB$PRIMARY31))
>
>Adding the LEFT OUTER JOIN..
>
> SELECT
> DATA_TBL.ID,
> LOOKUP_TBL.NAME,
> DATA_TBL.DATE
> FROM DATA_TBL
> LEFT OUTER JOIN LOOKUP_TBL ON DATA_TBL.LOOKUP_ID = LOOKUP_TBL.ID
> ORDER BY DATA_TBL.DATE DESC
>
>Uses this PLAN...
>
>PLAN SORT (JOIN (DATA_TBL NATURAL,LOOKUP_TBL INDEX (RDB$PRIMARY31)))
>
>Which Takes about 4 times as long.
>
>
>Cheers
>
>Phil Shrimpton
>------------------------------

That's not surprising, as your left outer join removes the ability to use
the index on DATA_TBL - there will be rows there with NULL in the column
that would (otherwise) be the indexed column.

H.
http://www.interbase2000.org
___________________________________________________
"Ask not what your free, open-source database can do for you,
but what you can do for your free, open-source database."
(J.F.K.)