title: Speeding up aggregations for a large table in Oracle
body: <p>I am trying to see how to improve performance for aggregation queries in an Oracle database. The system is used to run financial series simulations.</p>
  
  <p>Here is the simplified set-up:</p>
  
  <ol>
  <li><p>The first table table1 has the following columns</p>
  
  <p>date | id | value</p>
  
  <p>It is read-only, has about 100 million rows and is indexed on id, date</p></li>
  <li><p>The second table table2 is generated by the application according to user input, is relatively small (300K rows) and has this layout:</p>
  
  <p>id | start_date | end_date | factor </p></li>
  </ol>
  
  <p>After the second table is generated, I need to compute totals as follows:</p>
  
  <pre><code>    select date, sum(value * nvl(factor,1)) as total
      from table1 
      left join table2 on table1.id = table2.id 
      and table1.date between table2.start_date and table2.end_date group by date 
  </code></pre>
  
  <p>My issue is that this is slow, taking up to 20-30 minutes if the second table is particularly large. Is there a generic way to speed this up, perhaps trading off storage space and execution time, ideally, to achieve something running in under a minute?</p>
  
  <p>I am not a database expert and have been reading Oracle performance tuning docs but was not able to find anything appropriate for this. The most promising idea I found were OLAP cubes but I understand this would help only if my second table was fixed and I simply needed to apply different filters on the data. </p>