Re: Table Partition Count

From: MBPP <mpacheco_brazil_at_hotmail.com>
Date: Sat, 28 Nov 2009 03:22:06 -0800 (PST)
Message-ID: <ddb784b7-de4b-4829-8997-4f95bb59683c_at_w19g2000yqk.googlegroups.com>

On Nov 28, 12:00 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "raja" <dextersu..._at_gmail.com> wrote in message
>
> news:2679cd5a-d56e-44f0-9a39-93253fbcfbf6_at_u1g2000pre.googlegroups.com...
>
>
>
> > Matthew,
> > 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.
>
> 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),
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com

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);

Received on Sat Nov 28 2009 - 05:22:06 CST