Crude SQL - recommend improvement

From: Randy Harris <randy_at_SpamFree.com>
Date: Fri, 14 Jan 2005 08:31:20 GMT
Message-ID: <srLFd.15048$by5.2835@newssvr19.news.prodigy.com>

I managed to get this rather gruesome looking view to work, but am concerned about performance. I ran explain plan and see a bunch of full table scans. I've got everything indexed but don't know what else to do to improve it. None of the tables is large. Events_tbl is currently the largest by far and it has only about 25,000 rows. Evals_tbl will grow quickly, eventually it will be the largest.

Please provide constructive criticism.

CREATE OR REPLACE VIEW ESS.Evals_view AS   SELECT W.Weekstart || ',' || W.Year AS Sessiondate,   V.SessionID,
  session_hash(e.eventid,e.weekid) AS sessID,

  A.ActivityLong AS CourseName,
  A.CourseNumber AS CourseCode,
  E.Location,
  I.InstructorFirstName || ' ' || I.InstructorName AS Instructor,

  COUNT(V.sessionID) AS EvalCount,
  E.WeekID
FROM
  EVENTS_TBL E,
  WEEKS_TBL W,
  ACTIVITIES_TBL A,
  INSTRUCTORS_TBL I,
  EVALS_TBL V
WHERE

  E.WeekID = W.WeekID AND
  E.ActivityID=A.ActivityID AND
  E.InstructorID=I.InstructorID AND
  A.ActivityCode=1 AND
  I.InstructorID<>6 AND
  E.Task_ IS NULL AND

  SUBSTR(V.SessionID(+), -6, 5)=E.EventID GROUP BY E.WeekID, E.EventID,A.ActivityLong,A.CourseNumber,   V.SessionID,E.Location,W.WeekStart,W.Year,   I.InstructorFirstName,I.InstructorName

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=332 Card=6281 Bytes=
          866778)
   1    0   VIEW OF 'EVALS_VIEW' (Cost=332 Card=6281 Bytes=866778)
   2    1     SORT (GROUP BY) (Cost=332 Card=6281 Bytes=577852)
   3    2       HASH JOIN (Cost=114 Card=6281 Bytes=577852)
   4    3         TABLE ACCESS (FULL) OF 'ACTIVITIES_TBL' (Cost=1 Card
          =214 Bytes=7062)
   5    3         HASH JOIN (Cost=99 Card=6281 Bytes=370579)
   6    5           TABLE ACCESS (FULL) OF 'WEEKS_TBL' (Cost=1 Card=14
          32 Bytes=18616)
   7    5           HASH JOIN (Cost=69 Card=6281 Bytes=288926)
   8    7             TABLE ACCESS (FULL) OF 'INSTRUCTORS_TBL' (Cost=1
           Card=56 Bytes=840)
   9    7             HASH JOIN (OUTER) (Cost=62 Card=8637 Bytes=267747)
  10    9               TABLE ACCESS (FULL) OF 'EVENTS_TBL' (Cost=27 C
          ard=8637 Bytes=172740)
  11    9               TABLE ACCESS (FULL) OF 'EVALS_TBL' (Cost=7 Car
          d=972 Bytes=10692)

Statistics

---

         16  recursive calls
         41  db block gets
        236  consistent gets
        212  physical reads
          0  redo size
     509523  bytes sent via SQL*Net to client
      32837  bytes received via SQL*Net from client
        294  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
       4381  rows processed

SQL> Received on Fri Jan 14 2005 - 02:31:20 CST