Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Indexes in queries

 
Mallika Kumar
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Can someone explain when indexes will be used and when they won't be used in queries ? I have the following cases:
1. Select statement uses outer joins.
2. Select statemet uses like %a% statement.
SELECT E.EMPNAME FROM EMPLOYEE E,ADDRESS A WHERE E.EMPNAME LIKE %A% AND E.ADDRESSNO = A.ADDRESSNO(+)
In the above sample select statement, which colums should have index on them for better performance of the query, and will the index be used in presence of the outer join and LIKE condition ?
Any response will be helpful.
Thanks.
 
Victor Boveda
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you should index the foreign key (in both tables). I don't think the SQL Engine will search by indexes when you use a like clause
Hope this helps
Victor.
 
Laurent Leonard
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
An index is used in Oracle
- if no function are applied on the column
ex: where col | '' > 'test' -> no use
- in a like, if it is not starting with a wildcard
ex: like 'abc%' -> use
ex: like '%abc' -> no use
- if a col is at the beginning of an index
ex: where col1 > 'a' and an index on (col1, col2) exists -> use
ex: where col2 > 'a' and an index on (col1, col2) exists -> no use
On small tables, avoid the use of indexes, it is costing.
On Oracle8i, don't forget to make an analyse regurlarly on your table, because by default it use the cost base policy to optimize the plan (using the statistics on the tables).
Laurent
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic