RE: (long) Design question, historic and views

Our site is way smaller, the ODS will be around 30G of raw data. So
partitionning is out of the question here (I've used the one partition table
trick to use the exchange feature before on a bigger db).
Management is even considering DB2 for all the BI initiatives since the IBM
reps are almost giving it for free. So the cost makes the partitionning
option not being considered.

We will use an ETL tool to copy data from the sources to the ODS. I'm not
sure what will be done by the database and by the ETL tool.
I'll probably go with the 1 table containing current and old versions
approach.

For the view part, in the case where the old and current versions are in the
same table, the views are there to simplify the queries. It should be easy
to query the data, can not rely on using the maximum key and sort at each
access, those tables will be accessed by end-users via the internet.

>From experience, I prefer the query part to be easy to work with and the
loads can be less easy as no users are waiting on it.

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).

Let say we have a table EMP which we want to replicate to DW.

EMP has a monotonically increasing timestamp/sequence column for being
able to put versions in order or enforce optimistic locking.

There is a trigger on EMP which duplicates rows to EMP2 table, based on
our rules (insert/update/delete in our case)

EMP2 table is range partititioned table on timestamp column, with single
partition.

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

We do exchange partitions with table EMP3 (all the rows in EMP2 first
partition go to EMP3 table)

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.

We drop partition 1 of EMP2 table, generating practically no redo, and
leaving only the records inserted after split partition into EMP2.

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 ;)

> 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).
--
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).