Dynamically selecting partitions

Question

I have a table with a few hundred partitions and I am generally interested on the latest 35.

Accordingly I am trying to create views which would access these dynamically. i.e. always use the latest in case ones are created.

The query:

  select PARTITION_NAME,
  PARTITION_POSITION,
  NUM_ROWS,
  AVG_ROW_LEN
  from all_tab_partitions
    where
    table_name = 'MY_TABLE'
    AND PARTITION_NAME <> 'P_LAST'
    AND PARTITION_POSITION < (SELECT MAX(PARTITION_POSITION) 
    FROM all_tab_partitions) - 35
    order by 2 DESC
    ;

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.:

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);

(not the actual view, just an example)

So how do I do that? How do I create a view which will acess always the latest partition (execpt of "MAX")?

I am using Oracle 10g

thanks

Answer 1

You can do it using PL/SQL only

create or replace package my_table_ is
  type t_records is table of my_table%rowtype;
  function getpart(c_parts sys_refcursor) return t_records pipelined;
end;

create or replace package body my_table_ is
  function getpart(c_parts sys_refcursor) return t_records pipelined is
  v_partition all_tab_partitions.partition_name%type;
  v_row my_table%rowtype;
  c_mytab sys_refcursor;
  begin
    loop
      fetch c_parts into v_partition;
      exit when c_parts%notfound;
      open c_mytab for 'select * from my_table partition ('||v_partition||')';
      loop
        fetch c_mytab into v_row;
        exit when c_mytab%notfound;
        pipe row (v_row);
      end loop;
    end loop;
  end;
end;

Now you can

select * from table(my_table_.getpart(cursor(<QUERY_RETURNING_PARTITION_NAMES>)));