I am new to Oracle and have some performance problems. I am looking to see what the best way is to setup the indexes for my database. For example I have 3 dimension tables of D_DATE, D_GEOGRAPHY and D_DEMOGRAPHIC joined to a fact table called F_DATA. F_DATA is joined to the three dimension tables with DATE_KEY, GEOGRAPHY_KEY and DEMOGRAPHIC Key. When the data is accessed in the reporting tool it will be filtered on YEAR, COMMUNITY_CODE, AGE_GROUP_CODE, GENDER_CODE from the dimension tables and CHEMICAL_TYPE_CODE and CHEMICAL_CODE from the fact table.
Is it better to have a separate index for every column in every table, 2 for the D_DATE, 2 for D_GEOGRAPHY, 3 for D_DEMOGRAPHIC and 5 for F_DATA or should there be multiple indexes with more than one column it like 1 for the fact table with DATE_KEY, GEOGRAPHY_KEY, DEMOGRAPHIC_KEY and CHEMICAL_TYPE_CODE then another one with DATE_KEY, GEOGRAPHY_KEY, DEMOGRAPHIC_KEY and CHEMICAL_CODE? Also when should Unique vs non-unique be applied to the index?
Any recommendations would be greatly appreciated.