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.

                                 SELECT    id,
                                           oe
                                 FROM      oe
                                 WHERE     to_char(id) = 1232

                                 Time 97 secs.

                                 whereas 

                                 SELECT    id,
                                           oe
                                 FROM      oe
                                 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
FROM      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.

SELECT    * 
FROM      oe
WHERE     trunc(timestamp) = '26-MAR-91'

SELECT    *
FROM      oe
WHERE     timestamp between '26-mar-91' 
                                 AND       '27-mar-91'

                                 SELECT    * 
                                 FROM      oe 
                                 WHERE     timestamp >= to_date('26-mar-91:00:00',
                                           'dd-mon-yy:hh24:mi')
                                 AND       timestamp < to_date('27-mar-91:00:00',
                                           'dd-mon-yy:hh24:mi')

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)

a)
SELECT    *
FROM      job
WHERE     db_id||job_no = 'AZ0100201'

b) 
SELECT    *
FROM      job
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 in practice.

SELECT    deptno
                                 FROM      dept 
                                 WHERE     deptno = 1324

indexes

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.

SELECT    deptno
                                 FROM      dept 
                                 WHERE     deptno = to_char(1324)

SELECT    deptno
                                 FROM      dept 
                                 WHERE     deptno = '1324'

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips