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.
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
pie. tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop