Re: esplain plan and optimizer question?

FILTER: An operation that accepts a set of rows, eliminates some of them, and returns the rest.

In other words, Oracle is processing part of your "where" clause.

If you have an index on DEPTNO and "select * from emp where DEPTNO = 10" you will not see a "filter" operation, but should see an "index range scan". If you have no index on SEX and "select * from emp where DEPTNO = 10 and SEX = 'M'" you should again see the "index range scan" and the "filter". The index is used to get a sub set (DEPTNO = 10) of emp, and the filter is used to get a sub set (and SEX = "M") of the sub set.

To index or filter? It used to be that if the filter was to return < 20% of the total rows of a full table scan, create an index. Then it was 10%. Now I think it's down to 5%. Filters are not bad to have as long as you understand what is being returned.

Recomended reading: Advanced Oracle Tuning and Administration, Chapter 10 "EXPLANing SQL Statment Tuning", Osborne ISBN 0-07-882241-6, http://www.osborne.com/oracle/index.htm

Tlcbus wrote:

> What is access path 'FILTER' in explain plan means ? One of the reference tells
> me it represents missing indexes. If it is so, other step in the same explain
> indicate index is used. can any body shed light on this ?
>
> Thankx in advance Ted Chyn
Received on Mon Oct 20 1997 - 00:00:00 CDT