 Index Usage and SQL Tuning Question [message #191702] |
Thu, 07 September 2006 17:15  |
mohannemani
Messages: 1
Registered: September 2006
|
Junior Member |
|
|
Hello:
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)
Thanks
|
|
|
|
|
|
| Re: Index Usage and SQL Tuning Question [message #191867 is a reply to message #191705] |
Fri, 08 September 2006 07:45  |
rleishman
Messages: 3728
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.
eg.
SELECT *
FROM emp
WHERE empno > 5
OR sal = 10000 SELECT *
FROM emp
WHERE empno > 5
UNION
SELECT *
FROM emp
WHERE sal = 10000
This technique has been refined in more recent versions of the CBO by performing a BITMAP conversion (similar to BITMAP indexes).
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
