At 02:49 PM 11/07/2005 +1200, you wrote:

>Hi Adam
>
>Thanks for your suggestions. Please note my second email where I note
>that the Character T / F seems to massively impact the performance.
>
>Here is the real SQL
>
>SELECT *
>FROM SupplyLine sl
>JOIN SupplyOrder so ON so.SupOrdrRef = sl.SupOrdrRef
>WHERE so.EntityRef = 4
>AND sl.SupTranRef IS NULL
>AND sl.PackSlpRef IS NULL
>AND sl.CancelFlag <> 'T'
>
>
>PLAN JOIN (SO INDEX (SUPPLYORDER_ENTITYREF),SL INDEX
>(SUPPLYLINE_SUPTRANREF,SUPPLYLINE_PACKSLPREF,SUPPLYLINE_SUPORDRREF))
>
>Returns 208 results in 1.359s
>
>If I change the last line to
>
>AND sl.CancelFlag <> 'F'
>
>The plan remains the same and the result is 334 records in 0.078s
>
>Seems like something else besides an optimization problem is going on
>here.

Try this instead:

SELECT sl.* /* notice the identifier! */
FROM SupplyLine sl
JOIN SupplyOrder so ON so.SupOrdrRef = sl.SupOrdrRef
WHERE so.EntityRef = 4
AND sl.SupTranRef IS NULL
AND sl.PackSlpRef IS NULL
AND (sl.CancelFlag = 'F' or sl.CancelFlag is null)

>Also note that removing
>
>AND sl.SupTranRef IS NULL
>AND sl.PackSlpRef IS NULL
>
>Changes the plan but not the performance in either case. This is weird !

Not too weird. The logic of NOT searches makes them slow, even when the
searched column isn't indexed or the plan avoids an index. If your boolean
domain is constrained to be not null, so much the better, so you can avoid
the IS NULL test and go straight for the equality search.

./hb