Re: Physics of the FILTER operation within SQL

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 23 Jun 2004 09:15:08 +0100
Message-ID: <016201c458fa$35e20650$7102a8c0@Primary>

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_at_Sun.COM> To: <oracle-l_at_freelists.org> 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_at_alise.lv 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_at_freelists.org put 'unsubscribe' in the subject line.

--
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 - 03:11:53 CDT

Re: Physics of the FILTER operation within SQL_PLANE.