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

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
tf842Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

paquicubaCommented:
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 AdministratorCommented:
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.
tf842Author 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 Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

jrb1senior developerCommented:
How is the table partitioned?  By range?
tf842Author Commented:
It is partitioned by a range of calendar dates (in our case, purchase_date) as shown above.
Sami
ram_0218Commented:
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!!
ram_0218Commented:
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>);
slightwv (䄆 Netminder) 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?
Mark GeerlingsDatabase AdministratorCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jrb1senior developerCommented:
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 SpecialistCommented:
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);
ram_0218Commented:
wow!! good awking!! I never heard of this!! seems like a BEST fit to the question asked!!
tf842Author 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 developerCommented:
>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.
tf842Author 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 developerCommented:
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 SpecialistCommented:
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;
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.