|Subject||Re: [firebird-support] Re: Query plans and joins|
> > I'm having some trouble with the query below. Both tables containsActually, try a "fetch all" and see which one is slower.
> > 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.
My guess is that the one that's using the index for ordering is slightly
than the other one.
However, if you're doing a "which one returns rows first", then yes, the one
with the ordered index wins.
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Database development questions? Check the forum!
> > 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.