strange behavior of the optimizer

From: Joerg Jost <jost_at_unitrade.com>
Date: Fri, 21 Jan 2005 17:10:18 +0100
Message-Id: <200501211710.18864.jost@unitrade.com>

Hi list,=0A=0Ahere is an output of the autotrace from the following sta= tement:
select * from fa where fakt_art =3D 'BES' and fakt_nr between 5300001 a= nd
5300010=0AExecution Plan
----------------------------------------------------------=0A 0 =
SELECT
STATEMENT Optimizer=3DCHOOSE (Cost=3D4 Card=3D1 Bytes=3D30=0A1)=0A 1 =

   0 TABLE
ACCESS (BY INDEX ROWID) OF 'FA' (Cost=3D4 Card=3D1 Bytes=0A =3D= 301)=0A=0A 2
1 INDEX (RANGE SCAN) OF 'KEY_FA' (UNIQUE) (Cost=3D3 Card=3D1)=0A=0A= Statistics
----------------------------------------------------------=0A =
0
recursive calls=0A 0 db block gets=0A 13 consistent = gets
3 physical reads=0A 0 redo size=0A 3880 bytes sent vi= a
SQL*Net to client=0A 249 bytes received via SQL*Net from client=

2  SQL*Net roundtrips to/from client=0A          0  sorts (memory)
0  sorts (disk)=0A          9  rows processed=0A=0A=0ANow the same stat=

ement, just
another type of writing:=0A=0Aselect * from fa where fakt_art =3D 'BES'=  and
fakt_nr in (5300001 , 5300010=0A,
5300002,5300003,5300004,5300005,5300006,5300007,5300008,5300009)

Execution Plan=0A------------------------------------------------------=


----

0      SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D8783 Card=3D14471 By=

te
s=3D4355771)=0A=0A 1 0 TABLE ACCESS (FULL) OF 'FA' (Cost=3D8783 = Card=3D14471

Bytes=3D43=0A          55771)=0A=0AStatistics


----------------------------------------------------------=0A          =

0
recursive calls=0A          0  db block gets=0A      91957  consistent =
gets
54288  physical reads=0A         52  redo size=0A       3884  bytes sen=
t via
SQL*Net to client=0A        249  bytes received via SQL*Net from client=

2  SQL*Net roundtrips to/from client=0A          0  sorts (memory)
0  sorts (disk)=0A          9  rows processed=0A=0A=0A=0AWhat can we do=

 to get the
optimizer back to the right way? =0AThe table and the indexes are all analyzed. The fields fakt_art and fakt_nr=0A =0Aare the columns that bu= ild
together the primary key.=0A=0AThx in advance=0A=0AJ=F6rg=0A=0A=0ASyste= m:=0AOracle 9.2.0.5
AIX 5.3L=0AOptimizer_Feature_Enabled =3D 8.1.7 (due to some strange thi= ngs with
views =0A:-()=0A=0A--
-----------------------------------------------------------------=0ASE
PADERSOFT GmbH & Co. KG=0AVattmannstra=DFe 7, 33100 Paderborn=0APhone: = (+49) 52
51 / 30 1 6333=0AFax: (+49) 52 51 / 30 16 399=0AeMail: jost_at_unitrade.co= m
http://www.unitrade.com

---

=

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 21 2005 - 11:15:13 CST