From oracle-l-bounce@freelists.org Tue Jun 22 08:44:30 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5MDi5g32475 for ; Tue, 22 Jun 2004 08:44:15 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i5MDhs632385 for ; Tue, 22 Jun 2004 08:44:04 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E86E172C713; Tue, 22 Jun 2004 08:27:34 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 00540-83; Tue, 22 Jun 2004 08:27:34 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 38E9B72C583; Tue, 22 Jun 2004 08:27:34 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 22 Jun 2004 08:26:06 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D93BD72C530 for ; Tue, 22 Jun 2004 08:26:05 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 00769-40 for ; Tue, 22 Jun 2004 08:26:05 -0500 (EST) Received: from linus.jcpenney.com (146-235-98-30-jcp.jcpenney.com [146.235.98.30]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 56AE972C0B3 for ; Tue, 22 Jun 2004 08:26:05 -0500 (EST) Received: from i006.jcpenney.com (i006.jcpenney.com [10.33.181.175]) by linus.jcpenney.com (8.12.10+Sun/8.12.10) with ESMTP id i5MDiap7007314 for ; Tue, 22 Jun 2004 08:47:27 -0500 (CDT) Received: from rshamsudxp ([10.32.47.33]) by i006.jcpenney.com (iPlanet Messaging Server 5.2 HotFix 1.17 (built Jun 23 2003)) with ESMTPA id <0HZP008VEQA71H@i006.jcpenney.com> for oracle-l@freelists.org; Tue, 22 Jun 2004 08:46:55 -0500 (CDT) Date: Tue, 22 Jun 2004 08:46:54 -0500 From: Riyaj Shamsudeen Subject: RE: Physics of the FILTER operation within SQL_PLANE. In-reply-to: <40D82DCF.9050303@sun.com> To: oracle-l@freelists.org Message-id: <002501c4585f$612441c0$212f200a@rshamsudxp> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Mailer: Microsoft Outlook, Build 10.0.3416 Content-type: text/plain Content-Transfer-Encoding: 8bit Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Disclaimer: Outbound Correspondence X-VirusScan: JCP virus scan detected no virus X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 3303 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: rshamsud@jcpenney.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Hi 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@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Daniel Fink Sent: Tuesday, June 22, 2004 8:02 AM To: oracle-l@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@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@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@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 -----------------------------------------------------------------