Hi Tom,
> ETL would have to consist of inserts only once, no updates or deletes EVER
> Current-image data would have to be implemented as either views or
materialized views, if desired...
I completely agree with this strategy but in my opinion there are two
problems to be solved:
- the dealing with rolling window - e.g. the data are held for 7 years, but
the "current image" should contain complete data. This is not a trivial
task , for example using materialized views a drop of an old partition
should have no influence to the materialized view (may be not so difficult)
and a complete refresh of a materialized view must remain possible (may be
more difficult).
- the validation and cleaning make the "insert only once" more complicated.
I mean the enforcing of business rules beyond of primary and foreign keys.
Those actions can be moved in current-image views, but this is for sure not
the desired solution (validation will be repeated for each query).
regards
Jaromir D.B. Nemec
http://www.db-nemec.com
- Original Message -----
From: <tim_at_sagelogix.com>
To: <oracle-l_at_freelists.org>
Sent: Wednesday, September 08, 2004 6:40 PM
Subject: Re: How to store 50 Terabytes per day?
A couple things are certain:
- ETL would have to consist of inserts only once, no updates or deletes
EVER.
This goes back to the design of the data model and the proper
understanding
of time-variant data. It's not hard, but so many people with experience
in the
OLTP world can only think in terms of current-image data.
- Current-image data would have to be implemented as either views or
materialized views, if desired...
- Along with the principle of insert only, partitioning by RANGE on a time
column
will be crucial, with subpartitioning on a frequently-queried other
column being
an important option to consider. However, due to the volume of data,
one might
well consider hourly partitions, which would consume almost the limit of
65,535
partitions by itself if data retention is 7 years or so, making
subpartitioning not
feasible...
Boy, would I love to get my hands on that database! :-)
-Tim
--
To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe
To search the archives - http://www.freelists.org/archives/oracle-l/
--
To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe
To search the archives - http://www.freelists.org/archives/oracle-l/
Received on Thu Sep 09 2004 - 14:50:43 CDT
