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!