Re: Mysterious FILTER operation ;)
- From: Edgar Chupit <chupit@xxxxxxxxx>
- To: J.Velikanovs@xxxxxxxx
- Date: Mon, 3 Jan 2005 23:26:28 +0200
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@xxxxxxxx
<J.Velikanovs@xxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
