06 Index Suppression

Reasons for Index Suppression (Accidental)

There are many reasons why an index might not be used even though you have an index on the column you are searching for.

• Use of <> operator on an indexed column
• Use o a SUBSTR function on an indexed column
• use of arithmetic operators (+,_,*,/) on an indexed column
• Use of TRUNC function on an indexed column
• Use of || operator on an indexed column
• Data type conversion on an indexed column
• Use of IS NULL/ IS NOT NULL on an indexed column

USE of <> operator

Indexes can tell you what is in a table, but it cannot tell you whats not in a table (!= and <>). Use of these operators will disable the index

BAD

Select order_ID, sales_date, total_amount, product _amount

from sales_history

where  order_ID<>12345

GOOD

Select order_ID, sales_date, total_amount, product _amount

from sales_history

where  order_ID < 12345 and  order_ID > 12345

Substring function disables the Index

BAD

Select order_ID, sales_date, total_amount, product _amount, product_name

from sales_history

where substr(product_name, 1,3)= 'HIT"

GOOD

Select order_ID, sales_date, total_amount, product _amount, product_name

from sales_history

where product_name like 'HIT%'

Notice that it is using the index instead of a full table scan

Use of Arithmetic Operators

Use of arithmetic   Operators suppress the index (+,-,/,*)

Example

BAD

Select order_ID, sales_date, total_amount, product _amount, product_name

from sales_history

where total_amount + 1000 < 5000

GOOD

Select order_ID, sales_date, total_amount, product _amount, product_name

from sales_history

where total_amount < 4000

Use of Truncate Function on Date Fields

Truncate unction disables indexing on date fields

BAD

Select order_ID, sales_date, total_amount, product _amount, product_name

from sales_history
Where trunc(sales_date, 'Mon')= '01-Jan-2013'

GOOD

Select order_ID, sales_date, total_amount, product _amount, product_name
from sales_history
Where sales_date between  '01-Jan-2013' AND '01-Feb-2013'

Use of || Operator

|| Operator disables the index

BAD

select * from products

where color || category= 'BlackMobile'

GOOD

select * from products

where color= 'black' and  category= 'Mobile'

Compare character column to a number value

Ensure that the data types of the columns being compared are of the same types, otherwise Oracle will get confused and do a full table scan

BAD

Select * from sales history where product_name= 123

GOOD

select * from sales_history where product_name = '123'

Avoid IS NULL and IS NOT NULL  on Indexed  columns

Relational databases ignore NULL values (because the relational model says that NULL means not present). Hence, Oracle indexes will not include NULL values

BAD

Select * from sales history where order_id IS NULL

GOOD

Solution is to default all NULL values to a numeric or character value. (using updates or triggers or some other means)

Function Based Index

Where ever possible try to avoid using functions on an indexed column, but when here is a business requirement that imposes this condition, try to  use function based index

Example:

CREATE INDEX product_s_idx

on Sales_history (substr(product_name,1,3));

The index is being created on the function SubSTR(), so when ever this function is used on the product_name the index will be used.

For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)

Alternatively click through the link   if you found this article interesting. (This will help  elevate my companies Search engine rankings)