This is a very easy way to inadvertently disable the use of the indexes.
On able oe, which is described in the appendix.
Column id has a datatype of number.
WHERE to_char(id) = 1232
Time 97 secs.
WHERE id = 1232
Time .4 secs.
The reason for this is that oracle cannot use an index if the
column is modified in the where clause. It is up to the application programmer
to ensure that, WHERE clause columns, aren't modified in any way.
Note that the following query will also stop the use of indexes.
SELECT id, oe
WHERE id+1 = 1233
Dates in the where clause can also cause problems. To select
all the records entered on a certain day the following three ideas
come to mind.
WHERE trunc(timestamp) = '26-MAR-91'
WHERE timestamp between '26-mar-91'
WHERE timestamp >= to_date('26-mar-91:00:00',
AND timestamp < to_date('27-mar-91:00:00',
The first one when run takes 240 seconds to produce a result.
The second one only takes 1.05 seconds to run, however, it has a featurette.
It will include any records for midnight on the 27th of march.
The last one only takes .5 of a second and it
doesn't retrieve the extra records for midnight.
Examples along the lines of that shown below are also considered to
be column modifications. Therefore concatenating the columns, stops the
indexes from being used.
example a) should be rewritten as in example b)
WHERE db_id||job_no = 'AZ0100201'
WHERE db_id = 'AZ'
AND job_no = '0100201'
Developers need to be aware of the type conversions that
Oracle performs implicitly. Oracle may choose to convert either the column
or the constant. If the column is chosen then the indexes cannot
be used. The conversion chosen is defined in the following table.
Mixed datatypeCommon UnitFunction chosenChar with numberNumberto_numberChar
with rowidRowidto_rowidChar with dateDateto_date
The following example shows how the conversion table given above works
WHERE deptno = 1324
non unique index on deptno
The select would not use the index because the column deptno would
be to_numbered by the kernel.
The following queries will use the indexes.
WHERE deptno = to_char(1324)
WHERE deptno = '1324'
Have a Oracle Question
Do you have
an Oracle Question?
Certification, Database Administration, SQL, Application, Programming Reference
Application Hints and Tips
Database, SQL, Application, Programming Tips
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
All product names are trademarks of their respective
The site www.erpgreat.com is not affiliated with or endorsed
by any company listed at this site.
Every effort is made to ensure the content integrity.
Information used on this site is at your own risk.
The content on this site may not be reproduced
or redistributed without the express written permission of
www.erpgreat.com or the content authors.