We have an etl process which looks up with a couple of small dimension tables before loading a fact table. Basically looks means left outer join. The source table will do a left outer join with dimension tables. The two dimension tables are a very small look tables and each may contain not more than 20 records.
basically the query looks like below:
from src, dim1,dim2
where src.id1 = dim1.id1(+)
and src.id2 = dim2.id2(+)
For above kind of query, is good to have indexes created on lookup keys of the dim tables..
We are looking performances issue of some etl process. I think creating indexes on small table causes more harm than improvising..
please let me know what your thoughts are..