Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambriumusenet.nl!feed.tweaknews.nl!postnews.google.com!w19g2000yqk.googlegroups.com!not-for-mail From: MBPP Newsgroups: comp.databases.oracle.server Subject: Re: Table Partition Count Date: Sat, 28 Nov 2009 03:22:06 -0800 (PST) Organization: http://groups.google.com Lines: 60 Message-ID: References: <506a12f9-9aab-4108-81f6-59f34f6f7ac5@x25g2000prf.googlegroups.com> <2679cd5a-d56e-44f0-9a39-93253fbcfbf6@u1g2000pre.googlegroups.com> NNTP-Posting-Host: 189.32.217.44 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1259407326 6143 127.0.0.1 (28 Nov 2009 11:22:06 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Sat, 28 Nov 2009 11:22:06 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: w19g2000yqk.googlegroups.com; posting-host=189.32.217.44; posting-account=s7MOrgoAAABVO0kGpv_kx0V7G30lGRvL User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.1.5) Gecko/20091102 Firefox/3.5.5 (.NET CLR 3.5.30729),gzip(gfe),gzip(gfe) Xref: news.cambrium.nl On Nov 28, 12:00=A0am, "Jonathan Lewis" wrote: > "raja" wrote in message > > news:2679cd5a-d56e-44f0-9a39-93253fbcfbf6@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);