I understand that it makes sense to store dimension tables as index organized tables and fact tables using bitmap indexes for the dimension columns, however what should be done in the scenario where a fact table is used within another fact table? Also can a single b-tree or Oracle Text index be used by the cost based optimizer in addition to the multiple b-tree indexes?
For example I have an employees fact table with Oracle Text indexes on the first name and surname columns; b-tree indexes on the employee number, national identifier, passport number and employee id columns; bitmap indexes on title, gender, payroll and company. The employees fact table would then be joined to the leave of absence fact table (which has b-tree indexes for start and end date and bitmap indexes for leave type and process/reversal) using the employee id.
Can the cost based optimizer use a single b-tree index in addition to multiple bitmap indexes or do I need to change this design (maybe use compound compressed indexes)?
I would mostly be searching by employee name, national identifier, passport number and employee number.