Monster dimension

[renu123]

I have an employee dimension where I am applying SCD2. This dimension table has 54 fields. The change any of the attributes needs to be tracked , so a record would be inserted.I have already separated out the fields for which I don't need to maintain history (27 fields) into another table.
Now, with the main employee table with 54 fields and inserts happening for any attribute change, what are the ways to optimize queries. If queries hit this table, the processing time would be large.
There are 3-4 frequently used 'where' attributes for which I am planning to create bit-map indexes.
Also, for each employee, there will be one record which would be a 'latest' record giving his latest profile. I have field called 'latest_transaction_flag' on which I am also planning to create a partition i.e. a partition having only latest employee data.
Am I going on the right track? Can anybody suggest any useful techniques to handle this situation.
My database is Oracle 9i and reporting tool is BO.

Regards

 

[blom0344]

My two cents:

Query performance will depend upon number of rows and conditions set. The fact that the table consists of 54 fields does not mean anything in itself.
Best strategy is to create a view that hold the 'active' records (the latest records) and test on that
I would suggest testing query performance BEFORE adding indices. Adding an index is NOT neccessarily beneficial. If you have a field like employee's gender (which sets to 2 value's) there is little point in indexing this field.

Do you work with a tool like TOAD? It will help you in analyzing queries (explain plan), before you start using them in Business Objects....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[ganotedp]

Partitioning may be helpful for very large tables; one of our DAs experimented and said that Oracle 8i partitioning was useful when each partition was 1-million or more rows.

We didn't have that kind of volume, so we unpartitioned our tables, and just used Oracle parallel query, which was very helpful.

Typically in these cases, I create two tables, one "current" and one "audit" table. The "current" table has a trigger that inserts into a read-only audit table the before-image for any change plus a change (CRUD) code, a timestamp, and sequence number (to supplement the granularity of the timestamp). The "current" table is used for querying, and can be appropriately indexed.