title: Alternatives to slow self join (oracle sql)
body: <p>I am self-joining a table in order to get, on the same row, the variable "status" at date t and at date t+1month, which are saved respectively as "status_a" and "status_b":</p>
  
  <pre><code>select * from 
    (select mydate, id, status status_a from T) A
  inner join 
    (select add_months(mydate,1) mydate, id, status status_b from T) B 
  using(mydate, id);
  </code></pre>
  
  <p>I do this since I need to check a condition that involves the value of the column "status" in the two adjacent periods, e.g. <code>status_a in(0,2,4) and status_b in(91,93,95)</code>.
  The issue is that this join takes a lot of time, given the size of the table T.
  Is there a way to do the same in a more efficient way?
  Possibility that I considered:</p>
  
  <ol>
  <li>Iterate the join over couples of dates rather than on the whole table T, which may require a PL/SQL statement. </li>
  <li>It might be possible  to directly incorporate a simple condition in a window function; however since the conditions I need to check require to consider whether the values belong to lists of values (e.g. <code>status_a in(0,2,4) and status_b in(91,93,95)</code>), the output probably cannot be obtained with a window function. Additional complications arise if the two dates to consider for the condition are not adjacent nor 'first and last' record for each id.</li>
  </ol>
  
  <p>Do you have any suggestion to speed up this self join, in Oracle SQL?</p>