Re: Explain Plan, Help!

Ah, dear old PeopleSoft.
> Specifically, my ignorance is in regards to the FILTER operations.

The FILTER operation applies a WHERE clause condition when no index can be used.
In this case, following the NESTED LOOPS join of the MERGE JOIN results and the
INDEX RANGE SCAN of PS0JOB, Oracle has to apply the two WHERE clauses in the query. If OPRCLASS or NAME is not indexed, then FILTER is required. Check
your index structures. And while you're at it, try to add an index on the join column for the
join that is using a MERGE JOIN instead of a NESTED LOOPS join.

Kevin.

Mike Quist <mquist_at_nospam.concentric.net> wrote in article <344D7FC7.420C_at_nospam.concentric.net>...
> 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
Received on Thu Oct 23 1997 - 00:00:00 CDT