• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Rob Spoor
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
  • Scott Selikoff
Bartenders:
  • Piet Souris
  • Jj Roberts
  • fred rosenberger

Indexes in queries

 
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
pie. tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic