--- In firebird-support@yahoogroups.com, Woody <woody-tmw@...> wrote:

>
> neal.criscuolo wrote:
> > I am currently evaluating the process of upgrading from Firebird 1.54
> > to 2.03 and have hit a problem. Having saved a 1.54 database and
> > restored it to 2.03 everything so far seems to work well except the
> > following query:
> >
> > select J.JOB_ID,
> > J.JOB_NUMBER,
> > C.SHORT_NAME as CUSTOMER_NAME,
> > B.BRANCH_NAME,
> > S.DESCRIPTION as JOB_STATUS,
> > J.CUST_REF_NO
> > from JOB J,
> > CUSTOMER C,
> > BRANCH B,
> > JOBSTATUS S
> > where J.JOB_ID LIKE '544%'
> > and C.CUSTOMER_ID = J.CUSTOMER_ID
> > and B.BRANCH_ID = J.BRANCH_ID
> > and S.JOB_STATUS_CODE = J.JOB_STATUS_CODE
> > order by J.JOB_ID DESCENDING
> >
> >
> > Can anyone shed any light on why this might be happening?
> >
> Rewrite the query using newer join syntax:
> Choose the correct join type for your situation (left, right, etc.)
>
> select J.JOB_ID,
> J.JOB_NUMBER,
> C.SHORT_NAME as CUSTOMER_NAME,
> B.BRANCH_NAME,
> S.DESCRIPTION as JOB_STATUS,
> J.CUST_REF_NO
> from JOB J
> left join CUSTOMER C on (C.CUSTOMER_ID = J.CUSTOMER_ID)
> left join BRANCH B on (B.BRANCH_ID = J.BRANCH_ID)
> left join JOBSTATUS S on (S.JOB_STATUS_CODE = J.JOB_STATUS_CODE)
> where (J.JOB_ID LIKE '544%')
> order by J.JOB_ID DESCENDING

Except the equivalent would be an inner join rather than a left outer
join in this case.

The the OP,
Unfortunately the primary key and foreign keys are unnamed so have got
the automatically generated name. This makes reading the plan a bit
tricky because although you have told us which index you think should
be used, we can't tell what way the optimiser decided to go.

The reason for the difference is that the optimiser is now able to use
paths to solve the query under 2.x that it did not know under 1.5. For
whatever reason, it either thought that plan was better than it
actually is, or thought the original plan was worse than it actually
is. Of course, Garbage in, Garbage out. Setting the statistics of the
appropriate indices may help here.

If after setting the statistics the optimiser still makes a bad call,
it is time to start removing options it can consider. You can easily
do this by using the +0 trick.

For example

JOB J
join BRANCH B on (B.BRANCH_ID+0 = J.BRANCH_ID)

will prevent the index on Branch.Branch_ID from being used.

Note that the above is just an example. You will need to identify the
problematic indices yourself and using +0 eliminate the optimisers
ability to pick them.

Adam