The following two changes reduce the query time to 170ms:
1. Force the join order as follows
select /*+ ORDERED */
column list
from cal_items ci,
acs_events e,
timespans s,
time_intervals t,
acs_activities a,
calendars cals
For some reason Oracle wasn't picking the right order. I'm pretty sure that most queries start by restricting to particular calendars, stored in cal_items.on_which_calendar, so we will almost always want to start there. A notable exception are "all day" events, which due to an implementation quirk may find better selectivity in the start and end dates in the timespans table.
2. Create these indexes:
create index acs_events_cal_idx on acs_events (activity_id,timespan_id,event_id,name,status_summary);
create index cal_items_fk_idx on cal_items (on_which_calendar,cal_item_id,item_type_id);
These prevent full table scans by combining key columns from the WHERE and SELECT clauses of the query.
I'll fiddle with these a bit more and wait for any feedback before committing to OACS.
