FreeLists / oracle-l / Re: Mysterious FILTER operation ;)

[J.Velikanovs@xxxxxxxx]

>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@xxxxxxxx>
>To: <oracle-l@xxxxxxxxxxxxx>
>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