title: Dynamically selecting partitions
body: <p>I have a table with a few hundred partitions and I am generally interested on the latest 35.</p>
  
  <p>Accordingly I am trying to create views which would access these dynamically. i.e. always use the latest in case ones are created.</p>
  
  <p>The query:</p>
  
  <pre><code>  select PARTITION_NAME,
    PARTITION_POSITION,
    NUM_ROWS,
    AVG_ROW_LEN
    from all_tab_partitions
      where
      table_name = 'MY_TABLE'
      AND PARTITION_NAME &lt;&gt; 'P_LAST'
      AND PARTITION_POSITION &lt; (SELECT MAX(PARTITION_POSITION) 
      FROM all_tab_partitions) - 35
      order by 2 DESC
      ;
  </code></pre>
  
  <p>Seems to return me the partition names I'm interested, however, I don't manage to use it's results to select the partitions. e.g.:</p>
  
  <pre><code>CREATE OR REPLACE VIEW MY_VIIEW AS
  WITH t AS ( [Above query] )
  SELECT * FROM 
  MY_TABLE PARTITION (SELECT /*+ FIRST_ROWS(1) */ PARTITION_NAME 
                      from t);
  </code></pre>
  
  <p><em>(not the actual view, just an example)</em></p>
  
  <p>So how do I do that? How do I create a view which will acess always the latest partition (execpt of "MAX")?</p>
  
  <p>I am using Oracle 10g</p>
  
  <p>thanks</p>