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