I would try it in FIRST_ROWS mode, if you always expect few rows to be returned. Regards, Stephane Faroult RoughSea Ltd http://www.roughsea.com On Fri, 21 Jan 2005 17:10 , Joerg Jost <jost@xxxxxxxxxxxx> sent: 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@xxxxxxxxxxx[1]= m http://www.unitrade.com[2] ------------------------------------------------------------------ = -- //www.freelists.org/webpage/oracle-l[3] --- Links --- 1 javascript:parent.opencompose('jost@xxxxxxxxxxx','','','') 2 modules/refer.pl?redirect=http%3A%2F%2Fwww.unitrade.com 3 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l -- //www.freelists.org/webpage/oracle-l