title: Oracle - Query for large dataset taking very long time - Is there a way to optimize it?
body: <p>Suppose I had a very large table in my Oracle DB filled with data for thousands of items. This data would get updated very frequently throughout the day and each update gets a timestamp.</p>
  
  <p>So, for example, the table looks as follows (I know column names are bad, this is only illustration):</p>
  
  <p><strong>TBLDaily:</strong></p>
  
  <pre><code>Date:         ItemNo:     CharA:  ....  CharN:    Time_Stamp:
  2014/02/15    123         ....                    2014/02/15 10:00AM
  2014/02/15    123         ....                    2014/02/15 11:00AM
  2014/02/15    123         ....                    2014/02/15 02:13PM
  2014/02/15    234         ....                    2014/02/20 01:00PM
  2014/02/15    234         ....                    2014/02/20 09:00PM
     ...
  2014/02/16    123         ....                    2014/02/20 08:15PM
     ...
  </code></pre>
  
  <p>Then, I have a table with the same item numbers that stores other information, but it stays static throughout the month, so it would look as follows:</p>
  
  <p><strong>TBLMonthly:</strong></p>
  
  <pre><code>Date:          ItemNo:    CharA:   .... CharK:
  2014/01/31     123        ....          
  2014/01/31     234        ....          
  2013/12/31     123        ....          
  2013/12/31     234        ....          
    ...
  </code></pre>
  
  <p>Now, I need to get, for each part number &amp; for each date, the latest information available from the Daily table and, for some characteristics, if they don't exist there, then grab them from the monthly table.</p>
  
  <p>My SQL query looks as follows:</p>
  
  <pre><code>WITH All_Data AS
  (
    SELECT 
      ROW_NUMBER() OVER(PARTITION BY A.Date, A.ItemNo ORDER BY A.Time_Stamp) AS RN,
      A.Date, A.ItemNo, 
      NVL(A.CharA, B.CharA),
      B.CharB,
      ... whatever other characteristics ...
  
    FROM 
      TBLDaily A,
      TBLMonthly B,
  
    WHERE
      A.ItemNo = B.ItemNo
    AND
      A.Date BETWEEN To_Date('2012-12-31', 'yyyy-MM-dd') AND To_Date('2014-02-24', 'yyyy-MM-dd') 
    AND
      B.Date = (SELECT max(Date) FROM TBLMonthly WHERE Date &lt;= A.Date)
  )
  
  SELECT * 
  FROM All_Data 
  WHERE RN = 1
  ORDER BY Date, ItemNo
  </code></pre>
  
  <p>Now, this query is taking an <strong>immensely</strong> long time to complete (I left it running since yesterday afternoon and it was still executing the query this morning). It is for an extremely large dataset, I know, but I've queried out larger datasets substantially faster. I'm <strong>guessing</strong> that it is due to the either the:</p>
  
  <ol>
  <li><code>PARTITION BY</code> 
  or </li>
  <li>The continual <code>B.Date = (SELECT max(Date) FROM TBLMonthly WHERE Date &lt;= A.Date)</code></li>
  </ol>
  
  <p>But I'm not sure and, even worse, I don't know how to fix it to make it more efficient and not take so long.</p>
  
  <p>Any thoughts / help would be greatly appreciated!!</p>