Re: What, exactly, does "FILTER" mean in an execution plan

In article <3479C1C9.31EEF17B_at_inconcert.com>, Keith D Gregory <keith_at_inconcert.com> wrote:
>First, the background. I have three tables (all primary keys are
>indexed):
>
>"NEW_EVENTS" records events generated by my parts of the application and
>not yet processed. Each event is keyed by EVENT_ID, and references
>another object, identified by OBJECT_ID. There is no index on OBJECT_ID,
>as such an index would be infrequently used and would slow down
>insertions.
>
>"TRIGGERS" identifies actions that are to be invoked when a specific
>event is signalled for a specific object. It is keyed by TRIGGER_ID, and
>also has an OBJECT_ID.
>
>"CLASS_MEMBERS" identifies objects. It is keyed by OBJECT_ID.
>
>
>Now for the question. I have an asyncronous process, one of the tasks of
>which is to clean up triggers that refer to deleted objects, provided
>that there are no outstanding events for those objects. The basic query
>is as follows (it's a SELECT because there's other processing that has
>to happen, and the preceeding DECLARE CURSOR isn't shown):
>
> SELECT TRIGGER_ID
> FROM TRIGGERS
> WHERE NOT EXISTS
> (
> SELECT OBJECT_ID
> FROM CLASS_MEMBERS
> WHERE CLASS_MEMBERS.OBJECT_ID = TRIGGERS.OBJECT_ID
> )
> AND NOT EXISTS
> (
> SELECT OBJECT_ID
> FROM NEW_EVENTS
> WHERE NEW_EVENTS.OBJECT_ID = TRIGGERS.OBJECT_ID
> );
>
>When I get the execution plan, it shows a FILTER operation, as follows:
>
> SELECT STATEMENT
> FILTER
> TABLE ACCESS FULL TRIGGERS
> INDEX RANGE SCAN CLASS_MEMBERS_OBJ_INDEX
> TABLE ACCESS FULL NEW_EVENTS
>
>The table-scan of TRIGGERS is expected and desirable, as is the
>index-scan of CLASS_MEMBERS. The table-scan of NEW_EVENTS is expected
>also. But what is the "FILTER". I am assuming that it's something
>similar to a merge-join, where the two sorted result sets of the
>subqueries are merged and the intersection returned. What I am worried
>about, however, is that the table-scan of NEW_EVENTS is actually being
>performed as a nested join, which would be "very bad".

FILTER performs a where clause when no index can be used. Doesn't show up sometimes because it is implicit for a table access (like ROWID). This is what you see with a missing index. Explained in the Advanced Oracle Tuning book by Aronoff, etal.

jg

>
>Neither the "Server Concepts Manual" nor the "Application Developer's
>Guide" give a good explanation of this operation. Can someone enlighten
>me?
>
>-kdg

-- 
These opinions are my own and not necessarily those of Information Quest
jgarry@eiq.com                           http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry
"See your DBA?"  I AM the @#%*& DBA!