Oracle Database Discussions

Please could someone offer me some advice for a data warehouse table I am designing which will have ad hoc queries running against it mainly grouping by day/month/year and needs to use as little resources as possible.

TRANS_DATE DATE, LOC_ID VARCHAR2(8), USER_ID VARCHAR2(8), TRANS_CODE VARCHAR2(3), COUNT NUMBER(8,0)

In populating the table I truncated the trans_date to hourly data and aggregated the other columns to give me an hourly count for every combination of location, user and code. I wasn't sure if I should create 2 more columns with truncated dates by day and by month? There are 200,000 rows per day in this table.

The first 4 columns have low cardinality so I decided to create Bitmap indexes on them. However, when querying in Application Express SQL Workshop and looking at the query plan it seems that a full table scan is being performed whenever I use a group_by(example below), even when i use a hint for the index. The bitmap index is used on simple select queries with where clauses but no grouping.

SELECT LOC_ID, count(TOTAL)
FROM TRANS_SUMMARY
GROUP BY LOC_ID

Am I doing this the right way? Or would multiple materialised views / btree indexes be a better way of ensuring fast group_by queries on this table?

Thanks in advance.
Paul