Explain Plan, Help!

From: Mike Quist <mquist_at_nospam.concentric.net>
Date: 1997/10/21
Message-ID: <344D7FC7.420C@nospam.concentric.net>#1/1

The following statement (a select from a view) generated the following plan:

---

SELECT
DISTINCT EMPLID, EMPL_RCD#, NAME, SSN FROM SYSADM.PS_EMPLMT_SRCH_US

         WHERE OPRCLASS='STRATSVC' AND NAME BETWEEN 'YOUNG' AND
         'YOUNG~' ORDER BY NAME, EMPLID, EMPL_RCD#

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   HINT: ALL_ROWS
      2   SORT (UNIQUE)
      7    FILTER
      7     NESTED LOOPS
   2071      MERGE JOIN
   1962       SORT (JOIN)
   1962        NESTED LOOPS
    110         INDEX   HINT: ANALYZED (RANGE SCAN) OF


'PS0PERSONAL_DATA' (NON-UNIQUE)

   2071         INDEX   HINT: ANALYZED (RANGE SCAN) OF


'PSBSCRTY_TBL_DEPT' (NON-UNIQUE)

 282528       FILTER
    146        SORT (JOIN)
    147         INDEX   HINT: ANALYZED (RANGE SCAN) OF 'PS_PSTREENODE'
(UNIQUE)
5084196        TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'PS_SCRTY_TBL_DEPT'

5366506         INDEX   HINT: ANALYZED (RANGE SCAN) OF


'PS_SCRTY_TBL_DEPT' (UNIQUE)

   2078      INDEX   HINT: ANALYZED (RANGE SCAN) OF 'PS0JOB'
(NON-UNIQUE)
      8     SORT (AGGREGATE)
     10      INDEX   HINT: ANALYZED (RANGE SCAN) OF 'PS_JOB' (UNIQUE)
************************************************************************

I've seen alot of plans before but I'm confused as to what Oracle is doing here.
Specifically, my ignorance is in regards to the FILTER operations. I've included the
view text below, and all index names reflect the tables on which they're built.
Initially, I'm just trying to understand the order of execution, etc. Tuning will then follow. DISCLAIMER: I am not responsible for writing this code, it
is canned 3rd Party software. Any help is greatly appreciated! Thanks in advance,
Mike

view text:
CREATE VIEW SYSADM.PS_EMPLMT_SRCH_US
(EMPLID, EMPL_RCD#, OPRCLASS,NAME, LAST_NAME_SRCH, SSN, SIN, NATIONAL_ID, ACCESS_CD)
AS SELECT /*+ALL_ROWS*/
 A.EMPLID ,B.EMPL_RCD#,C.OPRID,A.NAME,A.LAST_NAME_SRCH ,A.SSN,A.SIN,A.NATIONAL_ID,C.ACCESS_CD
FROM

    PS_PERSONAL_DATA A  ,
    PS_JOB B  ,
    PS_SCRTY_TBL_DEPT C

WHERE
    C.ACCESS_CD='Y' AND
    A.EMPLID = B.EMPLID AND
    (B.EFFDT>=SYSDATE OR B.EFFDT=

     (SELECT MAX(D.EFFDT)
      FROM PS_JOB D
      WHERE
      B.EMPLID=D.EMPLID AND
      B.EMPL_RCD#=D.EMPL_RCD# AND
      D.EFFDT<=SYSDATE))  AND EXISTS
      (SELECT 'X'
       FROM PSTREENODE E
       WHERE E.SETID=' ' AND
       E.TREE_NAME='DEPT_SECURITY' AND
       E.EFFDT=C.TREE_EFFDT AND
       E.TREE_NODE=B.DEPTID AND
       E.TREE_NODE_NUM  BETWEEN C.TREE_NODE_NUM AND C.TREE_NODE_NUM_END
AND
       NOT EXISTS
        (SELECT 'X'
         FROM PS_SCRTY_TBL_DEPT G
         WHERE C.OPRID=G.OPRID AND
         C.TREE_NODE_NUM <> G.TREE_NODE_NUM AND
         E.TREE_NODE_NUM BETWEEN G.TREE_NODE_NUM AND G.TREE_NODE_NUM_END
AND
         G.TREE_NODE_NUM BETWEEN C.TREE_NODE_NUM AND

C.TREE_NODE_NUM_END)) Received on Tue Oct 21 1997 - 00:00:00 CDT