How can I write a query to group by the names of the partitions on a table?

That's a good question.  I'm not sure there is a way to do that easily in a single SQL statement.  (I'm posting this partly to get e-mail notification from other postings here, in case someone else knows of a way.)

One possibility would be to write a PL\SQL procedure that starts with a query from user_tab_partitions (or: all_tab_partitions) to get the partition names, then a second query from each partition in turn, but that would require the use of dynamic SQL (execute immediate).   Plus, getting the results from that back to a single select statement would be a challenge.

***>First you use <purchase_date>, then <partition_name>  <<--- What are trying to group here
Purchase_date is the column on which the partitions exist, with the potential for multiple dates per partition.
i.e.:
P1 might be 1/1/2000 - 12/31/2002
P2 might be 1/1/2003 - 12/31/2003
P3 might be 1/1/2004 - forward

We are trying to group by the logic used for the partitions without having to replicate the logic in the query.

Partitions are occassionally added and removed. We want to create a query that will group by the current partitions and return their respective partition name and total.

We do not want to loop through the partition names if possible. We know how to do that.

Sami

How is the table partitioned?  By range?

It is partitioned by a range of calendar dates (in our case, purchase_date) as shown above.
Sami

if you know the columns and the way its partitioned, then i guess you can do this by joining user_tab_partitions with your table., Thats my perception, anyway!!

Try this,

select partition_name,partkey,(select sum(amt) from <yourtabname> where keyfield=partkey) --or the range you want to compare
from  (select partition_name,high_value partkey from user_tab_partitions where table_name=<yourtabname>);

Bizarre question:  Why would you want to do this?

If I run this query today and get:
p1 100
p2 200
p3 300

and someone doubles the range of p1 and cuts the range of p2 by half.

Then I run the query again a day or so later, and get:
p1 400
p2 25
p3 350

What does that tell me?

wow!! good awking!! I never heard of this!! seems like a BEST fit to the question asked!!

More comments are welcome!

Awking, but we wanted to do just one query instead of multiple queries and unions, but thanks for the comment.

jrb1, your solution won't work on our tables because the number of rows and partitions would be cost/time prohibitive if using cursors. Although, on small tables/partition combinations we may use this.

Here is a sample my partner put together that loops through all the partitions, and we will use it if there is not a simple way to reference all partitions in a single query.

create table TEST_PARTITIONS
(
  PARTITION_NAME          VARCHAR2(30),
  PARTITION_POSITION  NUMBER,
  TOTAL                                   NUMBER
);


declare
  cur_table_owner   varchar2(30) := 'DBO';
  cur_table_name    varchar2(30) := 'MYTABLE';
  cur_partition            varchar2(30);
  cur_partition_num number;
  sql_str                       varchar2(2000);

begin
  loop

    select partition_name, partition_position into cur_partition, cur_partition_num from
    (select partition_name, partition_position, row_number() over(order by partition_position) r
    from all_tab_partitions
    where table_owner=cur_table_owner and table_name=cur_table_name
    and partition_name not in (select partition_name from test_partitions)
    )
    where r=1;
   
    sql_str := 'insert into TEST_PARTITIONS (partition_name, partition_position, total)
               select ''' || cur_partition || ''' partition_name, ' || cur_partition_num || ',
               sum(amt) total
               from ' || cur_table_owner || '.' || cur_table_name || ' partition(' ||
               cur_partition || ')';

    execute immediate sql_str;

    commit;

  end loop;  

  exception when no_data_found then
      null;
end;
/

We'll wait a day or so before allocating the points.

Thank you,
Sami

>Partitions are occassionally added and removed. We want to create a query that will group by the current
>partitions and return their respective partition name and total.

All,
After reviewing all the comments,
Mark Geer's comment pretty much answwered it. We should have caught the significance of this earlier.

>>I think what makes this a challenge is the fact that Oracle implemented partitioning intentionally in a way that would not >>break existing applications at the time.  So, standard SQL statements in Oracle are not aware of the partitions.  The >>optimizer knows about them, and statements can be written that reference (or are limited to) a particular partition, but I >>don't know of a way to write one query that spans multiple partiions, yet pays attention to the partition boundaries.

In essence Mark is saying, 'The optimizer is aware of partitions but he SQL is not.'

We'll use the looping that my partner wrote. However, the points will be split as best we can.

Thank you to all who participated,
Sami

I already had a final take underway.  I created a temp table:

create global temporary table current_partitions
(tblname varchar2(30), partname varchar2(30), keyval varchar2(10), position number)
on commit preserve rows;

And then loaded the current values:

 declare
    cursor c1 is select * from all_tab_partitions where table_name = 'TAB1';
    v_row c1%rowtype;
    v_key current_partitions.keyval%type;
 begin
    delete from current_partitions;
    open c1;
    loop
       fetch c1 into v_row;
       exit when c1%notfound;
       if v_row.high_value = 'MAXVALUE' then
          v_key := '9999-12-31';
       else
          v_key := substr(v_row.high_value,11,10);
       end if;
       insert into current_partitions
       values (v_row.table_name
             , v_row.partition_name
             , v_key
             , v_row.partition_position);
    end loop;
end;

Then, last did a join to bring the temp table together with the base table, determining the partition on the fly:

select partname, sum(amt)
from tab1 a
   , current_partitions b
where to_char(purchase_date,'yyyy-mm-dd') <= keyval
  and not exists (select 1 from current_partitions where
                    to_char(purchase_date,'yyyy-mm-dd') <= keyval
                       and keyval < b.keyval)
group by partname;