Re: Mysterious FILTER operation ;)

From: Edgar Chupit <chupit_at_gmail.com>
Date: Tue, 4 Jan 2005 21:00:58 +0200
Message-ID: <a8f0771c05010411002fc24dbf@mail.gmail.com>

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_at_alise.lv <J.Velikanovs_at_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

Received on Tue Jan 04 2005 - 12:59:10 CST