RE: Physics of the FILTER operation within SQL

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Tue, 22 Jun 2004 08:46:54 -0500
Message-id: <002501c4585f$612441c0$212f200a@rshamsudxp>

In this case, FILTER operation is implementing a join operation more efficiently. Without looking at 10053 trace file, I would say that CBO figured out that there are very few rows in MNS_PHARMACIES table with unify='N'. Instead of probing the MNS_PHARMACIES for each row from the outer row source ( as in the case of Rule based optimizer's NL decision), it might be cheaper to read the DKM_OUTBILL_RECEIPTS and generate a row source using the SQL "select id from MNS_PHARMACIES where unify='N'" and then apply the filters to generate the next row source in the parse tree.
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Daniel Fink Sent: Tuesday, June 22, 2004 8:02 AM
To: oracle-l_at_freelists.org
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
-----------------------------------------------------------------



-- Attached file included as plaintext by Ecartis --
-- Desc: Signature

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.


----------------------------------------------------------------
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 Tue Jun 22 2004 - 08:44:30 CDT

RE: Physics of the FILTER operation within SQL_PLANE.