David,

You don't say if cons has indexes on Addrnum and PAddrnum. Also anytime you add an OR to a where clause it slows it down terribly. And you can get better performance from two individual selects.

>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!