RE: (long) Design question, historic and views

Hi!

To answer your original question about the design & DW transport, there is too much to write to answer it completely. There's too many different ways to do the task.
I'll try to give you a reply from my past experience with OLTP-> DW transfer (from up to 800GB OLTP systems to 2-3TB DWs).

1. Let say we have a table EMP which we want to replicate to DW.
2. EMP has a monotonically increasing timestamp/sequence column for being able to put versions in order or enforce optimistic locking.
3. There is a trigger on EMP which duplicates rows to EMP2 table, based on our rules (insert/update/delete in our case)
4. EMP2 table is range partititioned table on timestamp column, with single partition.
5. When we decide to transfer changes to DW, we split the EMP2 table to 2 partitions, one partition with all current rows in EMP2 table, second partitions for all values from max(timestamp in EMP2)+1 .. MAXVALUE
6. We do exchange partitions with table EMP3 (all the rows in EMP2 first partition go to EMP3 table)
7. Now we can safely transport the changes to DW staging area without interrupting triggered inserts to EMP2 and without having to worry about whether any new rows were inserted into EMP2 meanwhile.
8. We drop partition 1 of EMP2 table, generating practically no redo, and leaving only the records inserted after split partition into EMP2.
9. And we start all over from step 5 again if want to transport next set of changes.

Btw, if you write your trigger accordingly, you can just update the master table when new version arrives and let the trigger handle copying old version to EMP2 - no deletes are required. It could even be possible to write trigger to update only those columns in row which actually have changed, to reduce rollback and redo amount, but this will probably be harder on your CPU. Anyway if you do so, and your trigger gets fairly large, it might be reasonable to put the code in a package, pin it and call the package from trigger. It's matter of benchmarking.

So, I just described a solution we used to you - this was back in 8.0 days, today there's a lot of other solutions like logminer/streams for example. Ok, that much from transporting.

I don't quite get where do you want to place the views and what is their purpose? In your ODS? Or DW?
Were you asking for a mean to distinguish between current and old versions?

If in ODS you have your current and old version tables separate (EMP vs. EMP2) then there's no problems - all current versions are in EMP table. But in DW where all records are together you have two options (which first come into my mind):
1) Modify ETL process to update some column of future old record to set current=N when new record comes in. This means that you have to search & update old current version of a record every time you insert a new version. 2) Do not modify ETL process at all, use timestamp column instead (timestamp/sqn is monotonically increasing column), so whichever record has larger sequence# is the current one. There are buts as well, for example if you want to keep deleted versions also in your DW, then you could update timestamp to 0 or similar. Also, depending on average number of versions, this might get quite slow if you aren't able to use indexes properly (should use ascending index range scan instead of sorting with large number of versions).

I hope it was what you were asking about. This was my... erm... 3 cents (sync, sync, sync ;)

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, August 22, 2003 5:29 PM

> That's why my post on historic tables and views seem lonely ....;-)
>
>
> Stephane
>
> -----Original Message-----
> Jared Still
> Sent: Friday, August 22, 2003 2:19 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Has anyone else noticed?
>
> Not so long ago, we saw quite a few more questions about
> such things as data modeling, application security architecture,
> physical database design, and Oracle Designer
>
> Not so much anymore.
>
> Do you think it's because there are so few development projects
> taking place? Seems like in house development died with the
> dot bomb and has not begun to recover.
>
> I know at my place of employment there is very little development,
> but that is due more to the size and nature of this place, as
> well as the management. ( they don't like in house development :( )
>
> Now I spend my days with stuff like making NetBackup work with Oracle,
> migrating SAP all over the place and keeping things running.
>
> Not that we haven't always done those things, but I miss some
> not having a good development project. Ah, to do some real
> data modeling again.
>
> Just some food for thought.
>
> Jared
>
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jared Still
> INET: jkstill_at_cybcon.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Stephane Paquette
> INET: stephane.paquette_at_standardlife.ca
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).