Dimension Build - Performance Problem

[photh]

I have a dimension build that inserts and updates (not a SCD type).  The dimension (reference table) has 27 million rows.  Update typically inserts less than 5,000 and updates less than 2,000 - this is daily.
The sql to create the data for the insert or update is efficient.. what is taking up the time is to cache the reference table.  (database is Oracle 10g) I thought about partitioning the table, but still do not see how that could improve performance when the bottleneck is the caching of the entire ref structure.  I am about to write an oracle procedure to do a MERGE, but thought I'd check with this community for ideas.

[MFGF]

How much memory is being used in caching the reference data for the build (ie the hierarchy)? Are you loading just the new/updated members into the hierarchy each time, or the whole dimension? Are any of the levels of the hierarchy defined as being cached to disk rather than in memory?

MF.


Sent from my iPad using Tapatalk HD