re: refreshing DW environment from APPS (9i)
- From: cosmin ioan <cosmini@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 18 Jan 2007 06:51:12 -0800 (PST)
hello all,
we have a quandary, on the topic above, on a Oracle Applications environment
(normalized) that we're trying to refresh to a DW (big/fact tables), basically
looking for a way to efficienty capture changes.
Here's a scenario of the oe_order_lines_all and oe_order_headers_all, some
basic tables in the apps:
select count(1)
from oe_order_headers_all ooha,
oe_order_lines_all oola
where ooha.header_id = oola.header_id
and ((ooha.last_update_date > '1-jan-2005' and ooha.last_update_date <=
'31-dec-2005')
or (oola.last_update_date > '1-jan-2005' and oola.last_update_date <=
'31-dec-2005'))
so, basically, what we're tying to do is capture any changes for the year
(month/day/whatever), regardless of whether the orders or lines have been
placed in 2005. The problem with this approach, because of the "OR" clause,
you're forced into full table scans. (and here, I'm only giving as an example
a 2 table join however there are FACTS, etc, that require many more joins so
the "full table scan" stuff gets really ugly).
One can probably circumvent this by using materialized views to determine the
delta, however, for speed purposes, we really like partitioned tables and I'm
not sure yet there are such things as "partitioned materialized views", so
probably we'll need to go the MV route and then further, populate an identical
schema partitioned table.... don't really like this due to the doubly wasted
space.
Any thoughts on how other enterprises do this, for large APPS deployments
(billions of rows) converting the data to DW style tables, in an efficient way?
thanks much,
Cos
Other related posts:
