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

From: Harry Sheng <SHui1002_at_rogers.com>
Date: Fri, 13 Sep 2002 00:02:35 GMT
Message-ID: <vW9g9.4577$8b1.1567@news01.bloor.is.net.cable.rogers.com>

"Andrew Allen" <ajallen_at_mailhost.det.ameritech.net> wrote in message news:3D7FDF5D.91F2F8AF_at_mailhost.det.ameritech.net...
> 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.

I checked about 20 similiar simple queries, tables and indexes in my system. Almost all non-unique indexes are not used if the indexed key has duplicated records, no matter how I collect the statistics, and how update-to-date the statistics is. The CBO always choses the "FULL TABLE SCAN" execution path.

I changed the "OPTIMIZER_INDEX_COST_ADJ" to 5, force the non-unique indexes be used in query. For the same task, I reduced the processing time from
more than 1 hour to less than 30 minutes.

I set "OPTIMIZER_MODE" to "ALL ROWS" in the parameter file, I am not sure if the affects CBO chosing the execution path.

>

> 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
Received on Thu Sep 12 2002 - 19:02:35 CDT