FILTER

Description

Applies a filter to a set of rows

Versions

This operation is implemented in the following versions

7.3.4
8.0.4
8.0.5
8.0.6
8.1.5
8.1.6
8.1.7
9.0.1
9.2.0

Example

Example 1

This example was developed using Oracle 9.2.0.1 on Windows 2000

This example requires the following table definitions

    CREATE TABLE t1 (c1 NUMBER);
    CREATE TABLE t2 (c1 NUMBER);

The table does not need to be analysed

The statement

    SELECT c1 FROM t1
    WHERE c1 NOT IN 
    (
      SELECT c1 FROM t2
    );

In Oracle 9.2 this generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   FILTER
2   1     TABLE ACCESS (FULL) OF 'T1'
3   1     FILTER
4   3       TABLE ACCESS (FULL) OF 'T2'

In Oracle 10.2 this generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   FILTER
2   1     TABLE ACCESS (FULL) OF 'T1'
3   1     TABLE ACCESS (FULL) OF 'T2'

In Oracle 10.2 the same plan is generated if the RULE hint is included in the statement

Example 2

This example was developed using Oracle 9.2.0.1 on Windows 2000

This example requires the following table definitions

    CREATE TABLE t1 (c1 NUMBER);

The table does not need to be analysed

Consider the statement

    SELECT c1 FROM t1
    WHERE ROWID > 
    (
      SELECT MIN (ROWID) FROM t1
    );

In Oracle 9.2 this statement generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   FILTER
2   1     TABLE ACCESS (FULL) OF 'T1'
3   1     SORT (AGGREGATE)
4   3       TABLE ACCESS (FULL) OF 'T1'

In Oracle 10.2 this statement generates the following execution plan which does not include the FILTER operation

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   TABLE ACCESS (BY ROWID RANGE) OF 'T1'
2   1     SORT (AGGREGATE)
3   2       TABLE ACCESS (FULL) OF 'T1'

This is because in this version the CBO is used to optimize the statement

In Oracle 10.2, if the RULE hint is included in the statement

    SELECT /*+ RULE */ c1 FROM t1
    WHERE ROWID > 
    (
      SELECT MIN (ROWID) FROM t1
    );

then the original execution plan is generated

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   FILTER
2   1     TABLE ACCESS (FULL) OF 'T1'
3   1     SORT (AGGREGATE)
4   3       TABLE ACCESS (FULL) OF 'T1'
Example 3

This example was developed using Oracle 9.2.0.1 on Windows 2000

This example requires the following table definitions

    CREATE TABLE t1 (c1 NUMBER);

The table does not need to be analysed

Consider the statement

    SELECT c1,COUNT(*) FROM t1
    GROUP BY c1
    HAVING COUNT(*) > 1;

In Oracle 9.2 this generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   FILTER
2   1     SORT (GROUP BY)
3   2       TABLE ACCESS (FULL) OF 'T1'

In Oracle 10.2 this generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   FILTER
2   1     HASH(GROUP BY)
3   2       TABLE ACCESS (FULL) OF 'T1'

This is because in this version the CBO is used to optimize the statement

In Oracle 10.2, if the RULE hint is included in the statement

    SELECT /*+ RULE */ c1,COUNT(*) FROM t1
    GROUP BY c1
    HAVING COUNT(*) > 1;

then the original execution plan is generated

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   FILTER
2   1     SORT (GROUP BY)
3   2       TABLE ACCESS (FULL) OF 'T1'
Example 4

This example was developed using Oracle 9.2.0.1 on Windows 2000

This example requires the following table definitions

    CREATE TABLE t1 (c1 NUMBER,c2 NUMBER);
    CREATE TABLE t2 (c1 NUMBER);

The table does not need to be analysed

Consider the statement

    SELECT c2 FROM t1
    WHERE c1 =
    (
      SELECT c1 FROM t2@database@link
    );

In Oracle 9.2 this statement generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   FILTER
2   1     TABLE ACCESS (FULL) OF 'T1'
3   1     REMOTE*

In Oracle 10.2 this statement generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   TABLE ACCESS (FULL) OF 'T1'
2   1     REMOTE*

This is because in this version the CBO is used to optimize the statement

In Oracle 10.2, if the RULE hint is included in the statement

    SELECT /*+ RULE */ c2 FROM t1
    WHERE c1 =
    (
      SELECT c1 FROM t2@database@link
    );

then the original execution plan is generated

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   FILTER
2   1     TABLE ACCESS (FULL) OF 'T1'
3   1     REMOTE*
Example 5

This example was developed using Oracle 9.2.0.1 on Windows 2000

This example requires the following table and view definitions

    CREATE TABLE t1 
    (
      c1 NUMBER,
      c2 NUMBER,
      CONSTRAINT con1 CHECK (c1 = 1)
    );

    CREATE TABLE t2 
    (
      c1 NUMBER,
      c2 NUMBER,
      CONSTRAINT con2 CHECK (c1 = 1)
    );

    ANALYZE TABLE t1 COMPUTE STATISTICS;

    CREATE VIEW v1 AS
    SELECT * FROM t1
    UNION ALL
    SELECT * FROM t2;

At least one of the tables in the partitioned view must be analyzed

Consider the statement

    SELECT c1 FROM v1;

In Oracle 9.2 this statement generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   VIEW OF 'V1'
2   1     UNION-ALL (PARTITION)
3   2       TABLE ACCESS (FULL) OF 'T1'
4   3       FILTER
5   4         TABLE ACCESS (FULL) OF 'T2'

In Oracle 10.2 this statement generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   VIEW OF 'V1'
2   1     UNION-ALL (PARTITION)
3   2       TABLE ACCESS (FULL) OF 'T1'
4   2       TABLE ACCESS (FULL) OF 'T2'

In Oracle 10.2 the same execution plan is generated if the RULE hint is included in the statement

Example 6

This example demonstrates how queries containing OR expressions in the WHERE clause can be written by Oracle to use the FILTER expression

This example was developed using Oracle 9.0.1 on Windows 2000

This example requires the following table definitions

    CREATE TABLE t1 (c1 NUMBER,c2 NUMBER);
    
    CREATE INDEX i1 ON t1 (c1);

    CREATE TABLE t2 (c1 NUMBER,c2 NUMBER);

    CREATE TABLE t3 (c1 NUMBER,c2 NUMBER);

The table does not need to be analysed as the RBO will be invoked

Consider the statement

    SELECT c2 
    FROM t1
    WHERE c1 =
    (
      SELECT MIN (c1) FROM t2
    )
    OR c1 = 
    (
      SELECT MIN (c1) FROM t3
    );

In Oracle 9.2 this statement generates the following execution plan

   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   CONCATENATION
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T1'
   3    2       INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE)
   4    3         SORT (AGGREGATE)
   5    4           TABLE ACCESS (FULL) OF 'T3'
   6    1     FILTER
   7    6       TABLE ACCESS (BY INDEX ROWID) OF 'T1'
   8    7         INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE)
   9    8           SORT (AGGREGATE)
  10    9             TABLE ACCESS (FULL) OF 'T2'
  11    6       SORT (AGGREGATE)
  12   11         TABLE ACCESS (FULL) OF 'T3'

The query has been internally rewritten to

    SELECT c2
    FROM t1
    WHERE (c1 != (SELECT MIN (c1) FROM t3)
    AND c1 = (SELECT MIN (c1) FROM t2))
    OR c1 = (SELECT MIN (c1) FROM t3);

Note that the RBO will evaluate these predicates in reverse order. If the condition

    c1 = (SELECT MIN (c1) FROM t3)

is true, then the entire WHERE clause evaluates to true and the FILTER operation prevents the remainder of the query from being evaluated

In Oracle 10.2 this statement generates the following execution plan

   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T1'
   2    1     SORT (AGGREGATE)
   3    2       TABLE ACCESS (FULL) OF 'T2'
   4    3         SORT (AGGREGATE)
   5    4           TABLE ACCESS (FULL) OF 'T3'

This is because in this version the CBO is used to optimize the statement.

In Oracle 10.2, if the RULE hint is included in the statement

    SELECT c2 /*+ RULE */
    FROM t1
    WHERE c1 = 
    (
      SELECT MIN (c1) FROM t2
    )
    OR c1 = 
    (  
      SELECT MIN (c1) FROM t3
     );

then the original execution plan is generated

   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   CONCATENATION
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T1'
   3    2       INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE)
   4    3         SORT (AGGREGATE)
   5    4           TABLE ACCESS (FULL) OF 'T3'
   6    1     FILTER
   7    6       TABLE ACCESS (BY INDEX ROWID) OF 'T1'
   8    7         INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE)
   9    8           SORT (AGGREGATE)
  10    9             TABLE ACCESS (FULL) OF 'T2'
  11    6       SORT (AGGREGATE)
  12   11         TABLE ACCESS (FULL) OF 'T3'