Helen Borrie wrote:

>
> 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

You're right Helen, it isn't surprising given InterBase's current state. But
since this is the arch-list, it's not unreasonable to ask that someday the
optimizer request the indexed NULL columns be selected for outer joins.

However, the best performance may depend on the table sizes. Larger tables may
benefit from the second optimization (the full sort) than from a navigational
walk of an index on a large table.

Regards,
Charlie