Applies a filter to a set of rows
This operation is implemented in the following versions
|
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
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'
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'
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*
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
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'