Re: Mysterious FILTER operation ;)

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <chupit@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 4 Jan 2005 21:44:35 -0000

The statement in the performance tuning guide is wrong,
and the suggestion it makes has only ever been a rough

This particular type of FILTER was present in (at least) 7.2,
(this specific FILTER appeared only in 9i, it doesn't
appear in 8i).

For a more traditional example of a plan where the statement
is wrong, consider a simple nested loop:

nested loop
    table t1 full
    table t2 by rowid
        index unique t2_pk (unique)

The first action is the start of the scan of table T1,
it is NOT the rightmost action - which is the index
access into t2_pk.


Jonathan Lewis
The Co-operative Oracle Users' FAQ
Public Appearances - schedule updated Dec 23rd 2004

----- Original Message ----- 
From: "Edgar Chupit" <chupit@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, January 04, 2005 7:00 PM
Subject: Re: Mysterious FILTER operation ;)

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

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 
      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?


Other related posts: