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

Collapse
Posted by Brian Fenton on
Yes, that's definitely the correct explain plan.
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=294 Card=44 Bytes=222)
   1    0   SORT (UNIQUE) (Cost=289 Card=44 Bytes=222)
   2    1     UNION-ALL
   3    2       NESTED LOOPS (Cost=276 Card=3 Bytes=222)
   4    3         NESTED LOOPS (Cost=273 Card=3 Bytes=207)
   5    4           NESTED LOOPS (Cost=270 Card=3 Bytes=192)
   6    5             NESTED LOOPS (Cost=267 Card=3 Bytes=177)
   7    6               NESTED LOOPS (Cost=264 Card=3 Bytes=138)
   8    7                 NESTED LOOPS (Cost=264 Card=609 Bytes=24969)
   9    8                   INDEX (FAST FULL SCAN) OF 'GROUP_ELEMENT_INDEX_PK' (UNIQUE) (Cost=264 Card=609 Bytes=21924)
  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=18165 Bytes=236145)
  13   12                 INDEX (UNIQUE SCAN) OF 'MEMBERSHIP_REL_REL_ID_PK' (UNIQUE)
  14    5             INDEX (UNIQUE SCAN) OF 'PERSONS_PK' (UNIQUE) (Cost=1 Card=149340 Bytes=746700)
  15    4           INDEX (UNIQUE SCAN) OF 'PARTIES_PK' (UNIQUE) (Cost=1 Card=149720 Bytes=748600)
  16    3         INDEX (UNIQUE SCAN) OF 'ACS_OBJECTS_PK' (UNIQUE) (Cost=1 Card=222490 Bytes=1112450)
  17    2       TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=41)




Statistics
----------------------------------------------------------
      74048  recursive calls
         10  db block gets
     261044  consistent gets
          0  physical reads
          0  redo size
       8319  bytes sent via SQL*Net to client
       1424  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
        139  rows processed