Date: Sat, 3 Mar 2007 08:03:26 -0800 (PST)
From: Cosmin Ioan <cosmini@bridge-tech.com>
Subject: DW loading of Ora Apps
To: oracle-l@freelists.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-121083562-1172937806=:86343"
Message-ID: <457448.86343.qm@web60419.mail.yahoo.com>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: cosmini@bridge-tech.com
--0-121083562-1172937806=:86343
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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 
Cos

--0-121083562-1172937806=:86343
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

<div>hi all, </div>  <div>&nbsp;</div>  <div>I was wondering if&nbsp;the DW gurus out there&nbsp;can shed light (as in "how it is done in big bad&nbsp;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):</div>  <div>&nbsp;</div>  <div>select * from tableA A, tableB B, tableC C, TableD D<BR>where a.col1=b.col1 and b1.col2=c.col2 and c.col3=d.col3 and<BR>(A.last_update_date &gt;= trunc(sysdate) OR<BR>B.last_update_date &gt;= trunc(sysdate) OR<BR>C.last_update_date &gt;= trunc(sysdate) OR<BR>D.last_update_date &gt;= trunc(sysdate))</div>  <div>&nbsp;</div>  <div>so, basically, because of the “OR” clauses, there’s full table scans on just about every table — rightfully so, I believe; </div>  <div>&nbsp;</div>  <div>When these tables are into the millions of records, this
 is an unreasonable/untunable query — it seems.</div>  <div>&nbsp;</div>  <div>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” </div>  <div>&nbsp;</div>  <div>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…</div>  <div>&nbsp;</div>  <div>thanks much for any feedback into this pesky "OR"&nbsp;issue <IMG class=wp-smiley alt=;-) src="http://jonathanlewis.wordpress.com/wp-includes/images/smilies/icon_wink.gif"><BR>Cos</div>
--0-121083562-1172937806=:86343--
--
http://www.freelists.org/webpage/oracle-l