Forum OpenACS Q&A: Re: Strange query performance when using <b>UNION</b>

Collapse
Posted by Brian Fenton on
select staff_id as instructor_id,
acs_object.name(staff_id) as instructor_name,
lower(acs_object.name(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')

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=296 Card=8 Bytes=584)
1 0 NESTED LOOPS (Cost=296 Card=8 Bytes=584)
2 1 NESTED LOOPS (Cost=288 Card=8 Bytes=544)
3 2 NESTED LOOPS (Cost=280 Card=8 Bytes=504)
4 3 NESTED LOOPS (Cost=272 Card=8 Bytes=464)
5 4 NESTED LOOPS (Cost=264 Card=8 Bytes=360)
6 5 NESTED LOOPS (Cost=264 Card=1098 Bytes=43920)
7 6 INDEX (FAST FULL SCAN) OF 'GROUP_ELEMENT_INDEX_PK' (UNIQUE) (Cost=264 Card=1098 Bytes=38430)
8 6 INDEX (UNIQUE SCAN) OF 'USERS_PK' (UNIQUE)
9 5 INDEX (UNIQUE SCAN) OF 'STA_PK' (UNIQUE)
10 4 TABLE ACCESS (BY INDEX ROWID) OF 'MEMBERSHIP_RELS' (Cost=1 Card=18273 Bytes=237549)
11 10 INDEX (UNIQUE SCAN) OF 'MEMBERSHIP_REL_REL_ID_PK' (UNIQUE)
12 3 INDEX (UNIQUE SCAN) OF 'PERSONS_PK' (UNIQUE) (Cost=1 Card=149050 Bytes=745250)
13 2 INDEX (UNIQUE SCAN) OF 'PARTIES_PK' (UNIQUE) (Cost=1 Card=149190 Bytes=745950)
14 1 INDEX (UNIQUE SCAN) OF 'ACS_OBJECTS_PK' (UNIQUE) (Cost=1 Card=221850 Bytes=1109250)

select -1 as instructor, ' none' as instructor_name, ' none' as lower_instructor_name from dual
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'