Forum OpenACS Development: Re: Suggestions for calendar scalability?
Posted by
Andrew Grumet
on 08/15/04 12:31 AM
Sure. Here are the plans without the indexes mentioned above:
Without /*+ ORDERED */ [SLOW]
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=41 Card=1 Bytes=208) 1 0 SORT (ORDER BY) (Cost=41 Card=1 Bytes=208) 2 1 NESTED LOOPS (Cost=37 Card=1 Bytes=208) 3 2 NESTED LOOPS (Cost=36 Card=1 Bytes=188) 4 3 NESTED LOOPS (Cost=34 Card=1 Bytes=179) 5 4 NESTED LOOPS (Cost=33 Card=1 Bytes=61) 6 5 HASH JOIN (Cost=32 Card=1 Bytes=35) 7 6 TABLE ACCESS (FULL) OF 'CAL_ITEMS' (Cost=9 Car d=852 Bytes=10224) 8 6 TABLE ACCESS (FULL) OF 'ACS_EVENTS' (Cost=22 C ard=36493 Bytes=839339) 9 5 INLIST ITERATOR 10 9 TABLE ACCESS (BY INDEX ROWID) OF 'CALENDARS' ( Cost=1 Card=15 Bytes=390) 11 10 INDEX (UNIQUE SCAN) OF 'CALENDARS_CALENDAR_I D_PK' (UNIQUE) 12 4 TABLE ACCESS (BY INDEX ROWID) OF 'ACS_ACTIVITIES' (Cost=1 Card=16707 Bytes=1971426) 13 12 INDEX (UNIQUE SCAN) OF 'ACS_ACTIVITIES_PK' (UNIQ UE) 14 3 TABLE ACCESS (BY INDEX ROWID) OF 'TIMESPANS' (Cost=2 Card=41130 Bytes=370170) 15 14 INDEX (RANGE SCAN) OF 'TIMESPANS_IDX' (NON-UNIQUE) (Cost=1 Card=41130) 16 2 TABLE ACCESS (BY INDEX ROWID) OF 'TIME_INTERVALS' (Cos t=1 Card=206 Bytes=4120) 17 16 INDEX (UNIQUE SCAN) OF 'TIME_INTERVALS_PK' (UNIQUE)With /*+ ORDERED */ [FAST]
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=41 Card=1 Bytes=208) 1 0 SORT (ORDER BY) (Cost=41 Card=1 Bytes=208) 2 1 NESTED LOOPS (Cost=37 Card=1 Bytes=208) 3 2 NESTED LOOPS (Cost=36 Card=1 Bytes=182) 4 3 NESTED LOOPS (Cost=35 Card=1 Bytes=64) 5 4 NESTED LOOPS (Cost=34 Card=1 Bytes=44) 6 5 HASH JOIN (Cost=32 Card=1 Bytes=35) 7 6 TABLE ACCESS (FULL) OF 'CAL_ITEMS' (Cost=9 Car d=852 Bytes=10224) 8 6 TABLE ACCESS (FULL) OF 'ACS_EVENTS' (Cost=22 C ard=36493 Bytes=839339) 9 5 TABLE ACCESS (BY INDEX ROWID) OF 'TIMESPANS' (Co st=2 Card=41130 Bytes=370170) 10 9 INDEX (RANGE SCAN) OF 'TIMESPANS_IDX' (NON-UNI QUE) (Cost=1 Card=41130) 11 4 TABLE ACCESS (BY INDEX ROWID) OF 'TIME_INTERVALS' (Cost=1 Card=206 Bytes=4120) 12 11 INDEX (UNIQUE SCAN) OF 'TIME_INTERVALS_PK' (UNIQ UE) 13 3 TABLE ACCESS (BY INDEX ROWID) OF 'ACS_ACTIVITIES' (C ost=1 Card=16707 Bytes=1971426) 14 13 INDEX (UNIQUE SCAN) OF 'ACS_ACTIVITIES_PK' (UNIQUE ) 15 2 INLIST ITERATOR 16 15 TABLE ACCESS (BY INDEX ROWID) OF 'CALENDARS' (Cost=1 Card=15 Bytes=390) 17 16 INDEX (UNIQUE SCAN) OF 'CALENDARS_CALENDAR_ID_PK' (UNIQUE)