| best way to create an index [message #162123] |
Wed, 08 March 2006 19:27  |
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  |
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: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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
