RE: refreshing DW environment from APPS (9i)

not necessary only that.
  there could be order headers in 2005 but additional lines in 2006 that need to be included.   By the same token, there could be order lines in 2005 for order headers entries in 2004 that also need to be included....    

  so it's basically one grand ole' view of anything that could have possibly changed in a massively joined (multiple tables, not just the two enclosed in the little example) view ;-)    

  is there such a thing as (complex) partitioned/subpartitioned materialized view in any newer Oracle versions? or any plans for that?                       

"Mark W. Farnham" <mwf_at_rsiz.com> wrote:

        
            why join at all?
   

  You probably want all the changed oe_order_headers_all rows and all the oe_order_lines rows.    

  Then you likely want to make aggregates on the changed lines and apply net deltas to the aggregates you keep in your datawarehouse.    

  Maybe I’m not groking something.    

  mwf            

  -----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of cosmin ioan Sent: Thursday, January 18, 2007 9:51 AM To: oracle-l_at_freelists.org
Subject: re: refreshing DW environment from APPS (9i)    

  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

--
http://www.freelists.org/webpage/oracle-l