Re: why index is not used in query execution plan ?

Harry Sheng wrote:
>
> 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

Ok, here we go.
1. I assume your stats are current.
2. Look at the query plan when you use an index hint. The index range scan tells me that the index you are trying to use is not unique.
3. The Oracle optimizer knows, through the current table and index stats as well as the fact that the index is not unique. 4. The optimizer figured out that it would be less costly to do a table scan than an index range scan and then a table access for each hit in the index scan.

I would suggest that you keep your stats current and let the optimizer do its job. It seems to have gotten it right here.

Since you are using 8i, I would suggest that you change to DBMS_ANALYZE instead of analyze table.

Finally, does the application really submit queries with literals? It should be using bind variables. Using literals in queries like this forces a costly hard parse every time it is submitted. Using literals instead of binding variables is the most efficient way of making an application non scalable because each user adds more one-time sql to the shared pool.

-- 
Andrew Allen
Livonia, MI
E- Mailto:ajalle_at_ameritech.net