Home » RDBMS Server » Performance Tuning » best way to create an index
best way to create an index [message #162123] Wed, 08 March 2006 19:27 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
if I have a SQL query like:

select a.*
  from tabA a,
       tabB b
 where a.in_dt = b.in_dt
   and a.out_dt = b.out_dt
   and a.he_dept = b.dept


Will it be better to use a composite index on tabA with the 3 atributes: in_dt, out_dt, he_dept
OR 3 individual indexes?? Any useful link on this topic??
Re: best way to create an index [message #162163 is a reply to message #162123] Thu, 09 March 2006 00:47 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
For that particular query, indexes on tabA won't help unless tabA contains many thousands of rows that have no match in tabB.

Assuming that matches exist for most rows, your SQL will return almost every row in tabA. An index is most useful when it is used to scan very small proportions of a table (say, <10%).

If you were to further constrain the query with something like:
AND b.colx = 'VAL'
then a composite index on the join columns of tabA will help.
Single column indexes are of no help, because Oracle generally cannot use a 2 index scan to resolve a join. The exceptions are rare, probably don't apply in this case, and are slower than a composite index.

Read the Oracle Performance Tuning manual for more information. Also see my web site particularly this page, which discusses the pitfalls of combining single-column indexes.

_____________
Ross Leishman
Previous Topic: help for not equal and index usage
Next Topic: Query to select users connected
Goto Forum:
  


Current Time: Thu Mar 28 19:18:33 CDT 2024