Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let`s suppose that alfa,beta and gamma contains milions of rows so we need to create indexes obviously to get optimal performace for this query :

SELECT * FROM alfa 
 JOIN beta on beta.id = alfa.beta_id 
 JOIN gamma on gamma.id = alfa.gamma_id
WHERE 
 beta.someattribute = 'X' AND
 gamma.anotherone = 'Y' AND
 alfa.something = 'Z'

Now what is optimal way to create indexes on alfa table ? Is is better to create one index across two columns (beta_id,gamma_id) or two separate indexes for each column ? (I am not sure how is JOIN query internally interpreted).

DB : Oracle 11g 11.1.0.7.0

share|improve this question
1  
That's going to depend a lot on the selectivity of the where conditions I believe. If they're very selective, indexing beta_id or gamma_id on alfa might not even be worth it at all. If they're not very selective, well, you're probably going to have expensive scans anyway since you're pulling all the columns from all the tables - indexes might not help. – Mat Apr 24 at 5:43

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.