Forum OpenACS Development: Re: Suggestions for calendar scalability?

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)