DW loading of Ora Apps

  • From: Cosmin Ioan <cosmini@xxxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Sat, 3 Mar 2007 08:03:26 -0800 (PST)

hi all, 
  I was wondering if the DW gurus out there can shed light (as in "how it is 
done in big bad DW shops") on something of a pesky problem that I?m having, 
with a big Ora Apps scenario: we?re trying to pull in data for any and all 
records in a view that have records that have changed in **any** table after a 
particular date such that (a very basic example):
  select * from tableA A, tableB B, tableC C, TableD D
where a.col1=b.col1 and b1.col2=c.col2 and c.col3=d.col3 and
(A.last_update_date >= trunc(sysdate) OR
B.last_update_date >= trunc(sysdate) OR
C.last_update_date >= trunc(sysdate) OR
D.last_update_date >= trunc(sysdate))
  so, basically, because of the ?OR? clauses, there?s full table scans on just 
about every table ? rightfully so, I believe; 
  When these tables are into the millions of records, this is an 
unreasonable/untunable query ? it seems.
  One cannot use ?AND? because, let?s say, when retrieving data for a child 
table, a parent?s record might be outside of the date range (?previous dates?) 
sought, so hence the thought of using ?OR?s? 
  any thoughts/suggestions? ? this is just a small subset/example, however the 
real example has about 8 tables with the ?OR?s? ? which to me, the problem 
seems untunable? or the approach, at least?
  thanks much for any feedback into this pesky "OR" issue 

Other related posts:

  • » DW loading of Ora Apps