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

oracle full table scan for
Oracle explain plan showing full table scan for “<>” operator even though there is an index for Order_ID

GOOD


Select order_ID, sales_date, total_amount, product _amount

from sales_history

where  order_ID < 12345 and  order_ID > 12345
Oracle explain plan without
Oracle explain plan without greater than and Less than separately  using the index on order_ID

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"
Oracle explain plan for substring shows a full table scan
Oracle explain plan for substring shows a full table scan

GOOD


Select order_ID, sales_date, total_amount, product _amount, product_name

from sales_history

where product_name like 'HIT%'
Oracle Explain plan for LIKE instead of substring
Oracle Explain plan for LIKE instead of substring

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
In Oracle using arithmetic operators in a where condition can disable the index
In Oracle using arithmetic operators in a where condition can disable the index

GOOD


Select order_ID, sales_date, total_amount, product _amount, product_name

from sales_history

where total_amount < 4000
Oracle Explain plan with arithmetic operators avoided
Oracle Explain plan with arithmetic operators avoided

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'
Oracle using truncate function on date field in a where condition causes a full table scan and avoids the index
Oracle using truncate function on date field in a where condition causes a full table scan and avoids the index

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'
Oracle Replacing the truncate function  on dates with a between operator will help utilize the index
Oracle Replacing the truncate function on dates with a between operator will help utilize the index

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)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s