From oracle-l-bounce@freelists.org Tue Jan 4 12:59:10 2005 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id j04IxAP22126 for ; Tue, 4 Jan 2005 12:59:10 -0600 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 j04IxAn22121 for ; Tue, 4 Jan 2005 12:59:10 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 49D0C72CA97; Tue, 4 Jan 2005 14:05:43 -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 24540-21; Tue, 4 Jan 2005 14:05:42 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D2C5172C745; Tue, 4 Jan 2005 14:04:02 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=YiGqw/D3+K7IGFp9Od/AI9j5+c+iNbIQmSES7zb3groJ2Xvzi+YB/5PknYLYP/CE1Oca+YMcjOfobXeAE90iSSkmJqHORjh5RBC6h5ZCPFhNuQwuTxE43jsCGxmHJ7sjktcT/aqbVyMDmBHKkMOxF1puvJt/463VrZABSBkf8kQ= Message-ID: Date: Tue, 4 Jan 2005 21:00:58 +0200 From: Edgar Chupit To: oracle-l@freelists.org Subject: Re: Mysterious FILTER operation ;) In-Reply-To: Mime-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit References: <021101c4f1d7$f5086fd0$6702a8c0@Primary> X-archive-position: 14343 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: chupit@gmail.com Precedence: normal Reply-To: chupit@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org This is very interesting observation that I would like to discuss. According to the "Performance Tuning Guide", for example, the execution order begins with the line that is the furthest indented to the right http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/optimops.htm#73843 But this experiment (v_p1>v_p2) ) proves that real execution order can be different. If we have LIO equal to 0, than INDEX RANGE SCAN was not performed and FILTER was performed before RANGE SCAN, this is also proved by sql_trace execution plan: Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=0 pr=0 pw=0 time=57 us) 0 FILTER (cr=0 pr=0 pw=0 time=8 us) 0 TABLE ACCESS BY INDEX ROWID OBJ#(282311) (cr=0 pr=0 pw=0 time=0 us) 0 INDEX RANGE SCAN OBJ#(282328) (cr=0 pr=0 pw=0 time=0 us)(object id 282328) As we can see from time column to FILTER step consumed 8 us but INDEX RANGE SCAN consumed 0 us (was not executed). Can somebody share some thoughts about real execution path of the statement? On Tue, 4 Jan 2005 15:40:50 +0200, J.Velikanovs@alise.lv wrote: > I tested it with > :v_p1:=500; > :v_p2:=1; > var. combination. > > In case of CBO > 0 consistent gets > RBO > 3 consistent gets > > CBO unlike RBO, even doesn't trying to run query. > Very smart ;) > > Thanks, for explanation. > Jurijs -- Edgar -- http://www.freelists.org/webpage/oracle-l