How to force the use of an index ?

From: Jean-Daniel TOULY <jdtouly_at_staffandline.com>
Date: Thu, 7 Oct 1999 12:00:12 +0200
Message-ID: <7thqqc$mjr$1@wanadoo.fr>

I want to force a SQL command to use an index because ORACLE doesn't choose to use it and give me long responses times.

Optimizer goal is choose and statistics are applied on both table and index.

The filed for the order is set for all the records of the table.

the command I use is

select /*+ INDEX(MATERIEL MATRIEL_FK19)*/ * from MATERIEL order by CODE_MATERIEL but the explain plan results in :

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1047 Card=20953 Byte
          s=5950652)

   1    0   SORT (ORDER BY)
   2    1     TABLE ACCESS (FULL) OF 'MATERIEL' (Cost=138 Card=20953 B
          ytes=5950652)

Does anyone know why the index is nerver used, even when I force it ?

Or why the index is not used at all with the ORDER BY sequence ?

Thanks for your help Received on Thu Oct 07 1999 - 05:00:12 CDT