Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Mysterious FILTER operation ;)

Re: Mysterious FILTER operation ;)

From: <J.Velikanovs_at_alise.lv>
Date: Tue, 4 Jan 2005 15:40:50 +0200
Message-ID: <OFD611976A.648DA39B-ONC2256F7F.004ABC76-C2256F7F.004B7B40@alise.lv>


>bind variables, then there has to be a mechanism
>that stops the real work being done when the
>first variable is larger than the second variable
>at run-time.

CBO is smarter then RBO , then ;)
It is logical explanation of
" 2 - filter(TO_NUMBER(:Z)<=TO_NUMBER(:Z))" operation.

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

On 03.01.2005 23:05:30 oracle-l-bounce wrote:

>As far as the logical I/O goes, I would guess
>that the execution plan you are seeing from
>explain plan is NOT the plan that is actually
>occurring for CBO - bind variable peeking
>is probably switching you to a full tablescan.
>
>The filter operation is probably similar to
>the type of filter that used to precede the
>parts of partition views which were not
>executed - in other words a constant
>test that could pre-empt the execution
>of the child portions of the plan. If your
>specific plan has to be generated once with
>bind variables, then there has to be a mechanism
>that stops the real work being done when the
>first variable is larger than the second variable
>at run-time.
>
>Regards
>
>Jonathan Lewis
>
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>The Co-operative Oracle Users' FAQ
>
>http://www.jlcomp.demon.co.uk/seminar.html
>Public Appearances - schedule updated Dec 23rd 2004
>
>
>
>
>
>
>----- Original Message -----
>From: <J.Velikanovs_at_alise.lv>
>To: <oracle-l_at_freelists.org>
>Sent: Monday, January 03, 2005 6:19 PM
>Subject: Mysterious FILTER operation ;)
>
>
>Win2000 9.2.0.6 (tested on 9.2.0.4/SPARC Solaris as well)
>FULL TEST text see at the end of letter.
>I just trying to understand what FILTER operation doing in case described
>below.
>I have ran the same SQL two times (with and without stats, CBO/RBO)
>
>
>--
>http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jan 04 2005 - 07:43:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US