Table Partition Count
raja
I have a table T1.
I have 5 partitions in that table, say : PART1, PART2, PART3, PART4,
PART5.
I need to take individual row count for these 5 partitions present in
the table T1.
How can i do that ?
Please help.
With Regards,
Raja.
Jonathan Lewis
"raja" <dexte...@gmail.com> wrote in message
news:506a12f9-9aab-4108...@x25g2000prf.googlegroups.com...
What version of Oracle ?
What do you want your output to look like ?
How generic do you want the query to be ?
How often do you want to do it ?
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
matthew
return sys.odcivarchar2list pipelined
is
s long;
cu sys_refcursor;
r varchar2(256);
begin
for su in
(
select table_name, partition_name, subpartition_name
from user_tab_subpartitions
where table_name = vtabname
) loop
s := 'select ''Table "' || su.table_name || '", Partition "' ||
su.partition_name || '", Subpartition "' || su.subpartition_name ||
'": count '' || count(*) from "' ||
dbms_assert.sql_object_name(vtabname) || '" subpartition("'
|| su.subpartition_name || '")';
open cu for s;
loop
fetch cu into r;
exit when cu%notfound;
pipe row(r);
end loop;
close cu;
end loop;
for p in
(
select table_name, partition_name
from user_tab_partitions
where table_name = vtabname
) loop
s := 'select ''Table "' || p.table_name || '", Partition "' ||
p.partition_name || '": count '' || count(*) from "' ||
dbms_assert.sql_object_name(vtabname) || '" partition("' ||
p.partition_name || '")';
open cu for s;
loop
fetch cu into r;
exit when cu%notfound;
pipe row(r);
end loop;
close cu;
end loop;
return;
end;
/
select * from table(count_('T1'))
/
raja
Thanks a lot. Let me check this out and if i have doubts, i will get
back to you
Jonathan Lewis,
Sorry for late reply.
1. What version of Oracle ?
10gR1 ( Datawarehousing )
2. What do you want your output to look like ?
Owner, table_name, partition_name, count
me, table1, part1, 1001
me, table1, part2, 99
me, table1, part3, 143
me, table2, part1, 786
me, table2, part2, 123
3. How generic do you want the query to be ?
Sorry, cant understand.
4. How often do you want to do it ?
Less frequent, but though if there is a query, thats would help a lot
for testing a scenario.
Thanks in Advance.
With Regards,
Raja.
Jonathan Lewis
Here's a tidy way of doing it if you really need it to be accurate.
http://jonathanlewis.wordpress.com/2009/11/25/counting/
(Otherwise, gather stats with a very small sample size on
just the table then query the user_tab_partitions (or equivalent)
view(s),
MBPP
wrote:
> "raja" <dextersu...@gmail.com> wrote in message
It is a long time I did this but I suppose there is a "partition
(name)" statement specific for this in selects. Try this, I am not
sure since what version it works:
select count(*) from t1 partition(part1);
select count(*) from t1 partition(part2);
select count(*) from t1 partition(part3);
select count(*) from t1 partition(part4);
select count(*) from t1 partition(part5);
Jonathan Lewis
"MBPP" <mpachec...@hotmail.com> wrote in message
news:ddb784b7-de4b-4829...@w19g2000yqk.googlegroups.com...
> > Sorry, cant understand.
>
>
> (name)" statement specific for this in selects. Try this, I am not
> sure since what version it works:
>
> select count(*) from t1 partition(part1);
> select count(*) from t1 partition(part2);
> select count(*) from t1 partition(part3);
> select count(*) from t1 partition(part4);
> select count(*) from t1 partition(part5);
This explains the meaning of my "generic" question fairly well.
The solution is only good for one table at one particular moment,
because if you want to change the table you have to find out all
the new partition names, and edit every line of code. Even if you
want to check the same table, you may have dropped and added
some partitions since last time.
Of course, Matthew supplied a solution using pl/sql that implemented
this specific method with a generic mechanism.