> > I'm having some trouble with the query below. Both tables contains
> > alot off records. I don't understand why when using a left join the
> > UC_TEST_DETAIL_CODE_VNR can't be used for ordering, which results in
> > slow performance.

Actually, try a "fetch all" and see which one is slower.

My guess is that the one that's using the index for ordering is slightly
slower
than the other one.

However, if you're doing a "which one returns rows first", then yes, the one
with the ordered index wins.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> > SELECT
> > A.*,
> > B.DESCRIPTION
> > FROM
> > TEST_DETAIL A
> > LEFT JOIN
> > TEST B
> > ON
> > B.ID = A.FID
> > ORDER BY
> > A.CODE,A.VNR
> >
> > plan on executtion using a left join
> > PLAN SORT (JOIN (A NATURAL,B INDEX (PK_TEST_ID)))
> >
> > plan on executtion using an inner join
> > PLAN JOIN (A ORDER UC_TEST_DETAIL_CODE_VNR,B INDEX (PK_TEST_ID))
> >
> > When i add the inner join plan to the query when using a left join, i
> > get :
> > index cannot be used in the specified plan.
> > index UC_TEST_DETAIL_CODE_VNR cannot be used in the specified plan.