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

tf842
tf842 used Ask the Experts™
on
i.e.:
Instead of
Select purchase_date, sum(amt)
from mytable
group by purchase_date

do this:
select partition_name p_name, sum(amt) total
from mytable
group by partition_name

given mytable has 3 partitions named, p1, p2 and p3
the result should look like this:
p_name total
p1         1000.25
p2         1300.50
p3         2400.42

Thank you for your consideration.
Sami
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you provide more details...
* Table structure
* Sample data

First you use <purchase_date>, then <partition_name>  <<--- What are trying to group here
Mark GeerlingsDatabase Administrator

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

Author

Commented:

***>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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

jrb1senior developer
Top Expert 2005

Commented:
How is the table partitioned?  By range?

Author

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

Commented:
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!!

Commented:
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>);
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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?
Database Administrator
Commented:
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.
jrb1senior developer
Top Expert 2005
Commented:
Here is a function that will return the partition a date value belongs to:

create or replace function GetPartitionNameRange (i_table_name varchar2, i_value varchar2) return varchar2
is
   v_value varchar2(255);
   v_name varchar2(30);
   v_found integer:=0;
   cursor c1 is select high_value, partition_name
     from all_tab_partitions
    where table_name = i_table_name
    order by PARTITION_POSITION;
begin
   open c1;
   loop
      fetch c1 into v_value, v_name;
      exit when c1%notfound;
      if v_value = 'MAXVALUE' then
         v_found := 1;
      else
         execute immediate 'select count(*) from dual where to_date(''' || i_value || ''') <= ' || v_value
             into v_found;
      end if;
      if v_found = 1 then
         return v_name;
      end if;
   end loop;
   return ' ';
end;
/

Now, just execute this like:

select substr(GetPartitionNameRange('YOURTABLE',to_char(purchase_date,'DD-MON-YYYY')),1,10) "Partition"
, sum(amt) Total
 from YOURTABLE
group by GetPartitionNameRange('TAB1',to_char(purchase_date,'DD-MON-YYYY'))

In my test, I get:

Partition    SUM(AMT)
---------- ----------
P1                  5
P2                 10
P3                  8
awking00Information Technology Specialist
Commented:
SELECT 'P1' P_NAME, SUM(AMT) TOTAL FROM MYTABLE PARTITION(P1)
UNION
SELECT 'P2' , SUM(AMT) FROM MYTABLE PARTITION(P2)
UNION
SELECT 'P3', SUM(AMT) FROM MYTABLE PARTITION(P3);

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

Author

Commented:
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
jrb1senior developer
Top Expert 2005

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

Author

Commented:
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
jrb1senior developer
Top Expert 2005

Commented:
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;
awking00Information Technology Specialist

Commented:
I know poinst have already been awarded but you may want to consider this.
>>but we wanted to do just one query instead of multiple queries and unions<<
A union is a single query, it's like a join without the join conditions. At any rate, you can still apply the methodology in a procedure as follows:

create or replace procedure insert_part_sums is

num_partitions   number;
count_partitions number := 1;
sql_str  varchar2(2000) := 'insert into test_partitions ';

begin

select count(*)
into num_partitions
from all_tab_partitions
 where table_owner = <schema_name>
 and table_name = 'MYTABLE';

for part_rec in
(select partition_name, partition_position
 from all_tab_partitions
 where table_owner = <schema_name>
 and table_name = 'MYTABLE')
loop

sql_str := sql_str||' select '||part_rec.partition_name||','
                              ||part_rec.partition_position
                  ||', sum(amt) total from mytable partition('
                  ||part_rec.partition_name||') ';

count_partitions := count_partitions + 1;
if count_partitions < num_partitions then
sql_str := sql_str||'union ';
end if;

end loop;

execute immediate sql_str;

end;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial