This is the query:
select s.staff_id as instructor_id,
acs_object.name(s.staff_id) as instructor_name,
lower(acs_object.name(s.staff_id)) as lower_instructor_name
from staff s, cc_users c
where s.staff_id = c.user_id
and c.member_state not in ('deleted', 'banned')
union
select -1 as instructor, ' none' as instructor_name,
' none' as lower_instructor_name from dual
order by lower_instructor_name

The query takes about 6 seconds to run on a production site but the 2 individual querys take about 0.2 seconds each. Removing the order by still takes about 5 or 6 seconds.

This is the explain plan:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=42 Bytes=74)
1 0 SORT (UNIQUE) (Cost=12 Card=42 Bytes=74)
2 1 UNION-ALL
3 2 NESTED LOOPS (Cost=3 Card=1 Bytes=74)
4 3 NESTED LOOPS (Cost=3 Card=1 Bytes=69)
5 4 NESTED LOOPS (Cost=3 Card=1 Bytes=64)
6 5 NESTED LOOPS (Cost=3 Card=1 Bytes=59)
7 6 NESTED LOOPS (Cost=2 Card=1 Bytes=46)
8 7 NESTED LOOPS (Cost=2 Card=2 Bytes=82)
9 8 INDEX (FAST FULL SCAN) OF 'GROUP_ELEMENT_INDEX_PK' (UNIQUE) (Cost=2 Card=2 Bytes=72)

10 8 INDEX (UNIQUE SCAN) OF 'USERS_PK' (UNIQUE)
11 7 INDEX (UNIQUE SCAN) OF 'STA_PK' (UNIQUE)
12 6 TABLE ACCESS (BY INDEX ROWID) OF 'MEMBERSHIP_RELS' (Cost=1 Card=115 Bytes=1495)
13 12 INDEX (UNIQUE SCAN) OF 'MEMBERSHIP_REL_REL_ID_PK' (UNIQUE)
14 5 INDEX (UNIQUE SCAN) OF 'PERSONS_PK' (UNIQUE)
15 4 INDEX (UNIQUE SCAN) OF 'PARTIES_PK' (UNIQUE)
16 3 INDEX (UNIQUE SCAN) OF 'ACS_OBJECTS_PK' (UNIQUE)
17 2 TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=41)


Can anybody explain this behaviour?

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!

not that this would do as much as having the optimizer do the right thing, but it should help a bit...