Svein Erling Tysvær wrote:

>>>
>>>select p.name, d.DateOfDiagnosis
>>>from patient p
>>>join diagnosis d on d.patientid=p.patientid
>>>where d.diagnosis starting '206'
>>>
>>>To my surprise, the suggested plan was
>>>PLAN JOIN(P NATURAL, D INDEX(IDX_DIAGNOSIS, IDX_PATIENT_ID))
>>>
>>>rather than
>>>PLAN JOIN(D INDEX(IDX_DIAGNOSIS), P INDEX(IDX_PATIENT_PK))
>>>
>>Ann:
>>My guess is that the optimizer assigned a really bad selectivity to
>>the "d.diagnosis starting '206'" condition.
>

Set:

> But why prefer to use
> the index for the diagnosis table as the second table in the plan and
> use a table not at all referenced in the where clause as the first
> table of the plan. How can the optimizer believe the index to be
> better when used 1m times as support to a unique index, than when used
> once?

An index lookup that touches most records in a table is likely to be
more expensive than a natural walk, because it reads all pages and has
to read the index and build the bitmap. It may be that the optimizer
arbitrarily assigned a selectivity of .5 to your conditions.

>
> I did one further check today, and by changing from
> where d.diagnosis starting '206', to
> where d.diagnosis between '2060' and '2069', the plan changed to what
> I would have expected it to be in the first place.

That makes some sense... I guess. Between 2060 and 2069 sounds more
precise than starting with 206.




Regards,


Ann