How to flatten sparse table records?
724067Jun 15 2010 — edited Jun 15 2010We have an application that (unfortunately) stores its data in a "long thin" name-value style format, which we then have to pivot into "wide" records for outputs. We're aiming to do this with materialised views, but want to take advantage of partition change tracking to refresh the views, which imposes some restrictions on how we build our views. In particular, we cannot use sub-queries or analytical functions to fully flatten the data into output records.
Right now, our pivoted materialized views produce something like the following:
Record Key | Date Created | Column1 | Column2 | Column3 | etc
--------------------------------------------------------------------------------------------
REC01 | 01-JAN-2010 | A | B | | ...
REC01 | 04-JAN-2010 | | C | D | ...
What we need is to flatten these records so that there is only one record with a given Record Key, and having only the most recent non-NULL value for each ColumnN e.g.:
Record Key | Column1 | Column2 | Column3 | etc
--------------------------------------------------------------------------------------------
REC01 | A | C | D | ...
The problem is that there can be hundreds of ColumnN columns, may be many separate update-records for a given Record Key (and potentially millions of different Record Keys), so I'm having trouble working out how to do this (a) efficiently or indeed (b) at all.
We can't just use the latest Date Created, because we may need to look at a different Date Created for each column. Because the MV needs to be PCT-capable, it seems we cannot use a sub-query to fetch only the latest value for each ColumnN before pivoting the data, and I can't see how to apply a group or analytical function to do this reliably on the wide records in the pivoted view.
What we need is a kind of FLATTEN() OVER (PARTITION BY RecordKey ORDER BY DateCreated DESC) function, but Oracle seems sadly lacking in this respect. Or am I missing something?
Looking at the data, it looks like it should be a pretty simple, but my SQL-brain is running out of juice, so if anybody else can suggest how to approach this, I'd be very grateful.
thanks,
Chris