| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Physics of the FILTER operation within SQL_PLANE.
I'm a little confused over your distinction between FILTER and NESTED LOOPS,
for this particular query. It has always been my understanding that FILTER,
especially in 8i and below, with respect to correlated EXISTS sub-queries,
was indeed a NESTED LOOPS operation, and a SEMI operation at that, bailing
as soon as possible. And Note 46234.1 seems to back that up in it's
discussion of the various meanings of FILTER. So FILTER is simply the way a
NESTED LOOPS operation is indicated in the plan for a correlated EXISTS
sub-query, but that it *is* indeed a NESTED LOOPS operation, and SEMI at
that.
And along those same lines, you take the same query against 9i, you see the plan "change" to indicate a NESTED LOOPS (SEMI). While they may very well truly be different plans, my take on it is the plan is the same as in 8i, but that simply the *notation* for the plan was changed from FILTER to NESTED LOOPS (SEMI) to be clearer about the operation. You see this with a number of correlated EXISTS when run between the two versions (not going to get into the "_always_semi_join" parameter with a value of CHOOSE at this time and how it might change to a hash semi ;-))
So that's where I'm confused, with respect to a correlated EXISTS, you seem to be drawing a distinction between the FILTER operation and a true NESTED LOOPS operation.
SQL> connect scott/tiger_at_orcl
Connected.
SQL> set autotrace trace explain
SQL> select *
2 from code_master cm
3 where exists (select null
4 from code_detail cd
5 where cd.code=cm.code)
6 and foo_date between to_date('1/1/2001','MM/DD/YYYY') and
7 to_date('1/31/2001','MM/DD/YYYY');
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=11)
1 0 FILTER
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CODE_MASTER' (Cost=3 Card=1
Bytes=11)
3 2 INDEX (RANGE SCAN) OF 'CM_FD_IDX' (NON-UNIQUE) (Cost=2 Card=1)
4 1 INDEX (RANGE SCAN) OF 'CODE_DETAIL_PK' (UNIQUE) (Cost=3 Card=3 Bytes=9)
SQL> connect scott/tiger_at_or92
Connected.
SQL> set autotrace trace explain
SQL> select *
2 from code_master cm
3 where exists (select null
4 from code_detail cd
5 where cd.code=cm.code)
6 and foo_date between to_date('1/1/2001','MM/DD/YYYY') and
7 to_date('1/31/2001','MM/DD/YYYY');
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=15) 1 0 NESTED LOOPS (SEMI) (Cost=5 Card=1 Bytes=15) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CODE_MASTER' (Cost=3 Card=1 Bytes=11)
3 2 INDEX (RANGE SCAN) OF 'CM_FD_IDX' (NON-UNIQUE) (Cost=2 Card=1)
4 1 INDEX (RANGE SCAN) OF 'CODE_DETAIL_PK' (UNIQUE) (Cost=2 Card=299600 Bytes=1198400)
Regards,
Larry G. Elkins
elkinsl_at_flash.net
214.954.1781
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jonathan Lewis
> Sent: Wednesday, June 23, 2004 3:15 AM
> To: oracle-l_at_freelists.org
> Subject: Re: Physics of the FILTER operation within SQL_PLANE.
>
>
>
> This example of a FILTER is very similar to
> a nested loop join - for each row in the driving
> table, Oracle operates the filter condition to
> determine whether or not to keep a row.
>
> However, FILTER can be much more efficient
> than nested loop, because it can remember results
> of previous probes into the second table - effectively
> making the probe an in-memory lookup.
>
> I suspect that this is happening in this case.
>
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Optimising Oracle Seminar - schedule updated May 1st
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed Jun 23 2004 - 07:53:54 CDT
 |
 |