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

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

Solution 2

Ok... I don't think your can use the Partition-Names, but you can use the Starting-Values of the Partitions to select the Data matching these Partitions...

So you View would look like this:

SELECT * FROM my_table WHERE date_col > get_part_limit( 'my_table', 35 ):

Where date_col is the column you use for partitioning - and get_part_limit is a stored function you write like this:

...
BEGIN
  SELECT high_value FROM all_tab_partitions
    INTO local_var
   WHERE table_name = parameter_name
     AND PARTITION_POSITION = MAX... - 35

  EXECUTE IMMEDIATE 'SELECT '||local_var||' FROM DUAL' INTO local_return_value;
  RETURN local_return_value;
END;

Solution 3

partitions are designed to be transparent for the data, so when you write a query, you simply don't know how your data is stored.

I see only one possibility to hit a particular partition: your WHERE clause should match values to the partitioned columns of latest (or latest 5) partition.

Next question is to build this WHERE clause on the fly. You already know that there is plenty of information in oracle dictionary. So you will read that and create a constructor to convert metadata conditions back into SQL.

Solution 4

irl we do exactly the same thing and use falco's solution like. Here is my code:

create or replace function longToDate( myOwner varchar2,
                     mytable_name  in varchar2,
                     mypartition_name  in varchar2
                    ) return date
   as

cDate date;
cvar varchar2(1024);
rq   varchar2(1024);
infiniteValue EXCEPTION;
PRAGMA EXCEPTION_INIT(infiniteValue, -00904);
begin
  select high_value into cvar FROM dba_tab_partitions t where  t.table_owner=myOwner and table_name=mytable_name and partition_name=mypartition_name;

  rq:='select '||cvar||'  from dual';
  execute immediate rq into cDate;
  return cdate;

EXCEPTION
WHEN infiniteValue
then return'01 jan 3000';
when others
then return null;

end longToDate;

Ant the view is something like this

create or replace view last_35 as
with maxdate as
(select longToDate(p.table_owner,p.table_name,p.partition_name) mydate,
       rank()over(order by p.partition_position desc) mypos,
       p.* from all_tab_partitions p
where p.table_name='MY_TABLE'
)
select /*+full(a)*/* from MY_TABLE a, maxdate
where MY_TABLE.partition_name>maxdate.mydate
and maxdate.mypos=35

View more solutions

Comments

[Justin Cave]

If you want to dynamically specify the partition name, you'd have to use dynamic SQL to generate and run the query (i.e. EXECUTE IMMEDIATE or dbms_sql). Are you sure that's necessary, though? If you're interested in the "last 35 partitions", I'm guessing that you have daily partitions in your table. Do you (or could you) have the dates that data has been loaded for in a separate table so that you could filter using the partition key rather than dynamically specifying the partition name?

[Jon Heller]

A pipelined function is a good idea but you'd likely want to use bulk collect. I assume there is a large number of rows.

[Naeel Maqsudov]

Yes, I thought about that. Do you need the version of the function with bulk collect?

[Jon Heller]

That's up to filippo (I hope he's still interested in this question!)

[Falco]

I like pipelined functions - but remember they have a lot of drawbacks... If you want to sort, join or filter the data returned by the function, it will come with a big performance hit!!! Since the whole table will be generated in Memory sequentially beforehand!