Home » RDBMS Server » Performance Tuning » Index Usage and SQL Tuning Question
icon5.gif  Index Usage and SQL Tuning Question [message #191702] Thu, 07 September 2006 17:15 Go to next message
Messages: 1
Registered: September 2006
Junior Member

Using LIKE/OR in where clause (of an indexed column) will force the query to NOT use INDEX? We have these where clause in Oracle Forms Records Group and wondering if this kills the usage of index.

Below are two examples...

1. If we have a where clause with LIKE would that NOT use the index?
Example: ColumnName like :block.Column||%

2. How about having an OR clause?
Example: and (ColumnName = :block.column or :block.column is null)

Re: Index Usage and SQL Tuning Question [message #191705 is a reply to message #191702] Thu, 07 September 2006 17:43 Go to previous messageGo to next message
Messages: 25531
Registered: January 2009
Location: SoCal
Senior Member
What does the EXPLAIN_PLAN show?
Re: Index Usage and SQL Tuning Question [message #191867 is a reply to message #191705] Fri, 08 September 2006 07:45 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
LIKE can use an index unless the comparison value begins with a wildcard ('%' or '_'). However, even if the comparison value begins with some other character (or a bind variable in your example), the CBO may choose NOT to use an index because it believes a Full Table Scan will perform better more often than an index scan.

ORs can also use an index. If there are two predicates in the OR, the CBO can expand the query into an equivalent UNION that does not use an OR, and each part of the UNION can use an index.
FROM emp
WHERE empno > 5
OR sal = 10000
is equivalent to
FROM emp
WHERE empno > 5
FROM emp
WHERE sal = 10000
In this example, the expanded UNION can exploit separate indexes on empno and sal.

This technique has been refined in more recent versions of the CBO by performing a BITMAP conversion (similar to BITMAP indexes).

Ross Leishman
Previous Topic: column to look for in v$system_event
Next Topic: inserting data in append mode .. what adv ??
Goto Forum:

Current Time: Sun Jul 23 14:14:49 CDT 2017

Total time taken to generate the page: 0.14809 seconds