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.
I am not sure exactly what you want. It would help to see some sample queries. But generally when you are designing an index for a specific set of queries you want to start with the columns that you are using to filter with then include columns that you either need in the query or are a part of the primary key. You want to start with low cardinality columns then move to high cardinality columns. An example would be below. If you are running a query for a specific geographical area and a specific demographic within a date range, and especially if you want to sort by the date_key the index will give you exactly what you want and in the right sort order. Therefore you limit the amount of logical/physical reads and you bypass having to sort the data. That would be the best case scenario.
It is not helpful to just have an index on every column, since using an index generally means that you do two reads for every row, one on the index and one on the row. The exception to that is if you have every column you need in the index with the columns that you want to filter with first, followed by any remaining columns. There is a point at which you can have too many columns and having too many indexes can be expensive as well given that you have to maintain those indexes every time you rebuild the table.
You need to experiment with different indexes with timing and SQL Trace on. Then you can look at logical IO, full table scans, full index scans, and of course the time taken by the query, to see which one works best. Bitmap indexes can help as well especially with a data ware house. Oracle can use them with other indexes to reduce the cost of the queries.
CREATE INDEX F_DATA_idx1 ON F_DATA ( GEOGRAPHY_KEY, DEMOGRAPHIC, DATE_KEY, primary key );