I'm confused. Maybe I haven't drank enough tonight :-D

>Now with SET OPTIM OFF the single query slows down remarkably but the UNION is still blazingly fast!
>Gets better doesn't it :-)
>
>
>>I see. Hmmm.....that is strange. Try running with OPTIMIZE set OFF and see what happens.
>>
>>>No, the UNION is much much faster! That's what I'm surprised at.
>>>
>>>>I don't think UNIONS are fully optimizable, Dave. That might explain it. Try both SQLs with SET OPTIMIZE OFF and see what the differences are.
>>>>
>>>>
>>>>>Address is a table with 400,000 records indexed on Addrnum
>>>>>Cons is a table of 120,000 contacts indexed on CCust
>>>>>Both have tags on DELETED()
>>>>>
>>>>>The query...
>>>>>

SELECT A.* FROM Address A, Cons C ;
>>>>>  WHERE C.CCust = "GREATLA2" ;
>>>>>  AND C.PAddrnum = A.Addrnum ;
>>>>>UNION SELECT A.* FROM Address A, Cons C ;
>>>>>  WHERE C.CCust = "GREATLA2" ;
>>>>>  AND C.Addrnum = A.Addrnum
>>>>>

>>>>>runs about 50 times faster than...
>>>>>

SELECT A.* FROM Address A, Cons C ;
>>>>>   WHERE C.CCust = "GREATLA2" ;
>>>>>   AND (C.Addrnum = A.Addrnum OR C.PAddrnum = A.Addrnum)
>>>>>

>>>>>Both queries show as fully optimized with SYS(3054,1), both return the same result set.
>>>>>Strange uh!