why index is not used in query execution plan ?

From: Harry Sheng <hsheng_at_innovance.com>
Date: Wed, 11 Sep 2002 16:37:03 -0400
Message-ID: <sUNf9.11239$H67.56785@tor-nn1.netcom.ca>

Hi, guru

I have the following very simple query,

    select * from nms1_iCOM_OchCTPBi where (OW_OMS_DistinguishedName =
'adfjlkgj') and deleted = 'Y';

and I have a index PF_IDX_ICOM_OCHCTPBI on table nms1_iCOM_OchCTPBi. The following is the index def:

TABLE                                   INDEX
POSITION    COLUMN_NAME
NMS1_ICOM_OCHCTPBI    PF_IDX_ICOM_OCHCTPBI                        1
OW_OMS_DISTINGUISHEDNAME
NMS1_ICOM_OCHCTPBI    PF_IDX_ICOM_OCHCTPBI                        2

DELETED Statistics on both table and index (with other 2 indexes on the same table) are collected using "analyze ... compute statistics". The table has 62 columns (char, varchar2, number and date type only) and its max record size is 3043 bytes.
I have about 8000 records in the table.

    OW_OMS_DISTINGUISHEDNAME varchar2(255)

    DELETED                                           char(1)

The following is the "explain plan" output:

OPERATION            OPTIONS                        OBJECT_NAME
POSITION       COST

SELECT STATEMENT
48         48
  TABLE ACCESS       FULL                           NMS1_ICOM_OCHCTPBI
1         48

Why the index is not used in the query execution plan ?

If I change my query to

    Select /*+ INDEX (NMS1_ICOM_OCHCTPBI) */

• from nms1_iCOM_OchCTPBi where (OW_OMS_DistinguishedName = 'adfjlkgj') and deleted =
  'Y'
  and run the "explain plan" again, the output changes to

OPERATION            OPTIONS                        OBJECT_NAME
POSITION       COST

SELECT STATEMENT
108        108
  TABLE ACCESS       BY INDEX ROWID                 NMS1_ICOM_OCHCTPBI
1        108
    INDEX            RANGE SCAN                     PF_IDX_ICOM_OCHCTPBI
1         39

I cannot explain why "Table Access By Index RowId" is in this execution plan and its cost so high.

To me, it is obvious that the index should be used in executing the query. But the reality is that the index is not used without the "INDEX" hint, it causes a huge number of disk read.
Unfortunately that query is auto-generated by an app, I cannot add the "INDEX" hint to it.
Is there any other way that I can force the query to use the index ?

My environment is Oracle8i release 3 on Solaris 8, the OPTIMIZER_INDEX_COST_ADJ is set to 50
in the parameter file.

Harry Received on Wed Sep 11 2002 - 15:37:03 CDT