I often times stage data into tables in my OWB mappings because it improves performance rather than just waiting until the end of my mapping to put data into a table. I have put some indexes on these staging tables to coincide with the WHERE clause in my JOINERs to attempt to speed things up further. However, am I shooting myself in the foot? Will indexes on these intermediary tables negatively affect loading performance?
Well, there is no rule about this, you should test it. Just be sure that mapping will use indexes you created and define hints on mapping source tables. INSERT in that table will certainly be slower, but SELECT maybe will be much faster and you will have a good final result. Also, if you have small set of records, then leave it without indexes and just use USE_HASH.
Mina