my dog learned polymorphism*
The moose likes JDBC and the fly likes Indexes in queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Indexes in queries" Watch "Indexes in queries" New topic
Author

Indexes in queries

Mallika Kumar
Ranch Hand

Joined: Feb 15, 2001
Posts: 61
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

Joined: May 22, 2001
Posts: 6
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

Joined: May 15, 2001
Posts: 35
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


Laurent LEONARD
 
 
subject: Indexes in queries
 
Similar Threads
SQL question
when to index column(s)
SQL invalid column index
A few doubts
diffrences between KEY, Index in mysql and postgres