Another interesting fact is that this query is fast on both the development and the release databases - it's only slow on the live database. The main difference between these systems is that live has a much large CC_USERS with over 36000 records. This CC_USERS view is quite slow - just doing a SELECT COUNT(*) from it takes 5 seconds on live. The STAFF table has 247 records on live and 122 on the release database. When STAFF and CC_USERS are joined it correctly drives off the staff_id.
So, it's almost as though using the UNION causes a full table scan of CC_USERS (even though the explain plan didn't show that). I'm going to go check that explain plan again.
Curiouser and curiouser!
