Re: Physics of the FILTER operation within SQL_PLANE.
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Jun 2004 09:15:08 +0100
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
----- Original Message -----
From: "Daniel Fink" <Daniel.Fink@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, June 22, 2004 2:02 PM
Subject: Re: Physics of the FILTER operation within SQL_PLANE.
Jurijs,
It is hard to determine exactly what is happenning without knowing the
structure and relationship of the objects. For example, what is the
mns_phs_pk index column(s)? Is unify only in the mns_pharmacies table?
I would venture to say that the FILTER operation is not a JOIN
operation. In the 1st query, the DKM_OUTBILL_RECEIPTS table is scanned
and a list of PHS_IDs is the result set. These are then used to probe
the MNS_PHARMACIES table via the MNS_PHS_PK index. When a PHS_ID is
found where UNIFY != 'N', the PHS_ID is FILTERed (discarded). Since you
are returning any data from the MNS_PHARMACIES table, there is no need
to join the tables.
The only thing that 'bothers' me is the low number of consistent reads
in the first query against the MNS_PHARMACIES and MNS_PHS_PK objects. I
suppose (really just a guess) that there are a small number of distinct
PHS_ID values in the DKM_OUTBILL_RECEIPTS table, so the UNIQUE SCAN
actually returns multiple values of ID, which oracle uses to FILTER.
This is the best I can reason out. Perhaps the more learned colleagues
on the list can shed more light on the inner workings of sql operations.
Regards,
Daniel
J.Velikanovs@xxxxxxxx wrote:
> Daniel, thanks for the answer.
> It is seams I miss some think.
> Take a look on the plan bellow.
> How FILTER operation can join to tables?
> From plane it is seems that FILTER operation join tow tables
> DKM_OUTBILL_RECEIPTS and MNS_PHARMACIES.
>
>
> ----------------------------------------------------------------
> | Id | Operation | Name |
> ----------------------------------------------------------------
> | 0 | SELECT STATEMENT | |
> | 1 | SORT AGGREGATE | |
> |* 2 | FILTER | |
> | 3 | TABLE ACCESS FULL | DKM_OUTBILL_RECEIPTS |
> |* 4 | TABLE ACCESS BY INDEX ROWID| MNS_PHARMACIES |
> |* 5 | INDEX UNIQUE SCAN | MNS_PHS_PK |
> ----------------------------------------------------------------
>
>
> Jurijs
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
