We help IT Professionals succeed at work.

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

tf842
tf842 asked
on
447 Views
Last Modified: 2010-03-22
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

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
CERTIFIED EXPERT

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
jrb1senior developer
CERTIFIED EXPERT
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>);
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
jrb1senior developer
CERTIFIED EXPERT
Top Expert 2005
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
awking00Information Technology Specialist
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT

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;

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions