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: Edgar Chupit <chupit_at_gmail.com>
Date: Mon, 3 Jan 2005 23:26:28 +0200
Message-ID: <a8f0771c0501031326793a43e@mail.gmail.com>


If your database is like mine and have default block size 8K, than TESTRS table will consume 1188 blocks and index will consume 1114 blocks. In first case explain plan simply lies and in reality you have FULL TABLE SCAN + FILTER (cr close to number of blocks in table), in second case you have RANGE SCAN + TABLE ACCESS BY ROWID (cr close to number of blocks in table + number of blocks in index).

I think that moral of this story is that you should always check SQL_TRACE trace files for real execution plans.

On Mon, 3 Jan 2005 20:19:16 +0200, J.Velikanovs_at_alise.lv <J.Velikanovs_at_alise.lv> wrote:
> 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)
>
> There is index range scan caused by "where n between :v_p1 and :v_p2;"
> predicate usage.
> Only difference between two runs is additional FILTER operation added by
> CBO.
>
> What mysterious for me is why operation added by CBO reduce LIO as well as
> execution time.

-- 
  Edgar
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 03 2005 - 15:34:55 CST

Original text of this message

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