Hi Raymond!

First, thanks for a good problem description. I'll cut heavily from
this reply not to make other peoples mail boxes flow over (especially
those who use 'daily digest'), but every bit of what you wrote was
useful.

> FROM
> Customer
> JOIN CustOrder on CustOrder.CUSTOMER = Customer.ID
> JOIN Address ON Customer.Address = Address.ID
> WHERE (CustOrder.InvType IN (1,2,4,5))
> AND Upper(CustOrder.PO) LIKE 'S%'
>
> plan used
> PLAN JOIN (CUSTOMER NATURAL,ADDRESS INDEX (PK_ADDRESS),CUSTORDER
> INDEX (FK_CUSTORDER_CUSTOMER,I_CUSTORDER_INVTYPE_ID_HS,
> I_CUSTORDER_INVTYPE_ID_HS,I_CUSTORDER_INVTYPE_ID_HS,
> I_CUSTORDER_INVTYPE_ID_HS))

The culprit is that the optimizer thinks this is a better plan than
all alternatives, including

PLAN JOIN (CUSTORDER INDEX (I_CUSTORDER_INVTYPE_ID_HS,
I_CUSTORDER_INVTYPE_ID_HS, I_CUSTORDER_INVTYPE_ID_HS,
I_CUSTORDER_INVTYPE_ID_HS),
CUSTOMER INDEX (PK_CUSTOMER),ADDRESS INDEX (PK_ADDRESS))

To make this happen, try to remove the possibility for using the
FK_CUSTORDER_CUSTOMER index, e.g. by doing this minor change:

JOIN CustOrder on CustOrder.CUSTOMER+0 = Customer.ID

I call this "optimizer hinting", and hopefully that hint will be good
enough for it to understand which plan to use.

HTH,
Set