title: How to obtain the latest record using PARTITION BY in a performant way in SQL?
body: <p>In our normalized Oracle 12.2 database used for obtaining information for dashboards and reports we have found that users often come across a need of wanting to know the data on the most recent record per some partition. In some cases we can filter the data to a smaller subset, whereas in others the whole data set is desired. In most cases users want to know the latest results for multiple partitions at once. The typical pattern for this is in oracle is as follows:</p>
  
  <pre><code>select * from (
      select my_table.*,
             row_number() over (partition by fk1, fk2, ... order by my_date desc) rn
      from my_table
      [where fk1 = 1234]
  ) where rn = 1
  </code></pre>
  
  <p>Originally we wanted to abstract this out into a view for convenience so people could just write a query against a view. We tried something like this:</p>
  
  <pre><code>create view my_table_latest as (
      select * from (
          select my_table.*,
                 row_number() over (partition by fk1, fk2, ... order by my_date desc) rn
          from my_table
      ) where rn = 1
  )
  
  select * from my_table_latest where fk1 = 1234
  </code></pre>
  
  <p>Unfortunately this had two problems. First, it seems that the analytical function inside of the view always computes over the entire table before applying any filters. Hence the full table was scanned regardless of the filters and indexes that were used. Second, when used on tables with millions of records the queries were taking longer than we would like.</p>
  
  <p>Given that we would like our data to stay relatively fresh (within 10 minutes), what would be the best way to obtain the latest records for some business key in a performant manner? The method to obtain the data should be hidden in a view so it is easily consumable by the fronting dashboarding application.</p>
  
  <p>Here are two ideas that we had:</p>
  
  <ul>
  <li><strong>Materialized View</strong> - Use a materialized view to recompute the results every 10 minutes. Given that the query takes several minutes to compute we are concerned this may not work well. Furthermore, based on what we found with having the analytical query in a view we suspect that using a more efficient refresh strategy would not work due to the use of the analytical function.</li>
  <li><strong>Track as we go</strong> - Given that we have access to the code that writes the data and data is always processed oldest -> newest we could easily keep track of the latest record and save it off to a different table. A view then could be created that uses this information and joins back to the original table to obtain the rest of the record details. The "custom index" table would be something like (fk1, fk2, my_table.pk, date). Unfortunately this would require a code change.</li>
  </ul>