Oracle Scratchpad

November 25, 2009


Filed under: Oracle,Partitioning — Jonathan Lewis @ 7:10 pm BST Nov 25,2009

In one of those little coincidences that seem to happen so often a question came up on the server news group that has an answer in a blog note I’d written just a few of days earlier . The question was simply asking for a way of counting the number of rows in each partition of a partitioned table.

Of course, there are lots of ways of doing this, depending on how much time you want to write the code, how efficient you want the code to be, how generic you want the code to be, and so on; but here’s a little script that I wrote in about five minutes based on the ‘partition marker’ that appeared in recent versions of Oracle.

rem     Script: pt_count.sql
rem     Dated:  Nov 2009
rem     Author: Jonathan Lewis
rem     Simple script to count the number of rows in each partition
rem     of a partitioned table.  This is not going to be fast if the
rem     table is very large.
rem     The number of objects in the schema will also affect the
rem     performance since the query includes a call to instantiate
rem     a complete list of them.
rem     Change the following define to supply the target table name

define m_tab_name = pt_range

		/*+ no_merge */
		object_type like 'TABLE%'
	and	subobject_name is not null
	)	obj,
		/*+ no_merge */
		dbms_mview.pmarker(rowid)	data_object_id,
		count(*) 			row_count
		&m_tab_name       -- this is where your table-name goes.
	group by
	)	rct
	rct.data_object_id = obj.data_object_id
order by

It’s very simple, and also happens to demonstrate a good use of the /*+ no_merge */ hint. (I could have used subquery factoring with the /*+ materialize */ hint to similar effect).

My partitioned table is called pt_range  and I’ve had to include its name in the query. The query is then written in two halves; the first half finds the names and data_object_ids of all the partitions (or subpartitions) of tables in the user’s schema (and I could have put in a restriction on object_name at that point, of course). The second half counts rows by data_object_id (which is what the dbms_mview.pmarker(rowid) function returns. Then it’s simply a question of joining the two result sets (using an outer join if you want to list partitions with no rows) and reporting the desired columns in the order you like.

I’ve used the no_merge hint to allow Oracle to do the aggregation early – at the most efficient point possible; the alternative would be to do the join and then aggregate on all the other columns reported, but would be a waste of resources. The no_merge hint is probably redundant in the query against the user_objects view – but for a quick hack it’s quite sensible to block the optimizer from trying to get too clever with the code you’re writing.

If you don’t approve of using the dbms_mview.pmarker() function (because of the reliance on the undocumented fact that it returns the data_object_id) you could use the function which also returns the data_object_id (despite its name) for a given rowid.

The code isn’t fast, though – so you might want to go one better and trying use a substr() on the rowid to extract the encoded data_object_id, aggregate on that, and then write your own function to turn the encoded form into a decimal form.

Bear in mind, though, whatever you do Oracle will have to scan the entire table and call whichever function you apply on every single row, and then aggregate across the entire result set. So even though the code looks quite clean and simple, it’s not efficient.

If efficiency combined with flexibility is important you might be better off writing a (pipelined) pl/sql function that takes a table-name as an input and loops through each partition in turn with a simple count(*).

Update – Feb 2016

It is a measure of how rarely I feel the need to do an accurate count of rows per partition that I didn’t realise until a few days ago that when you are using interval partitioning the order in which the object_id is generated need not be the same as the order in which the partition high_value needs to be seen (Oracle will create partitions when needed, but the object_id is generated by a sequence). So I had to enhance the code to include the partition and subpartition positions to report the segments by “data order”. (Given that the the generated names don’t look particularly meaningful I’m not sure that this will be generally useful, though).


  1. Respected Sir,

    Good Day

    while reading your fabulous book “cost based oracle”

    In chapter 13 on pg 383 in the second last paragraph you said

    “for every row in t1, Oracle has located the first relevant row in the second sorted
    data set, and scanned from there to the end of the sorted data set. That’s
    10,000 rows checked for the first row in t1; 9998 rows checked for the second row in t1;
    9996 rows checked for the third row in t1 and so on”

    and here you are talking about the access predicate (t2.n2>=t1.n2-1)

    Now the the column n2 in both t1 and t2 have pattern like 0,0,1,1,2,2 ……1000,1000 ….

    As per access predicate first two row in t1.n2 is -1, -1 (n2-1) hence we are doing 10,000 for the first two rows in t2
    then there is 0, 0 (n2-1) for the third and fourth rows for t1.n2 hence again we are doing 10,000 for the third and fourth rows..

    should not be oracle is doing 10,000 check in t2 for the first four rows in t1;
    9998 for the 5th and 6th rows; 9996 for the 7th and 8th rows ……

    Am I missing something?


    Comment by Henish — November 26, 2009 @ 3:32 pm BST Nov 26,2009 | Reply

    • Henish,

      You’re right. Thanks for pointing out the error; I don’t know how I managed to report this incorrectly, but I’ve added some notes to the Errata page for this chapter.

      (Coincidentally, someone else sent me an email a couple of days ago with a list of errors and typos – including this one, and they included a pl/sql block to demonstrate arithmetic as well.)

      Comment by Jonathan Lewis — November 30, 2009 @ 10:04 am BST Nov 30,2009 | Reply

  2. Thanks Sir

    Comment by Henish — December 1, 2009 @ 3:10 pm BST Dec 1,2009 | Reply

  3. […] 13-How to get row count of each partition of a partitioned table? Jonathan Lewis-Counting […]

    Pingback by Blogroll Report 20/11/2009-27/11/2009 « Coskan’s Approach to Oracle — December 18, 2009 @ 12:15 am BST Dec 18,2009 | Reply

  4. I am trying to find the row count in each partition and the query return zero. I sure have partitions and data in each partition. What am I doing wrong ?

    SQL> define m_tab_name = FLEXFIN.GWTB_MSG_IN_HISTORY
    select obj.object_name, obj.subobject_name, rct.row_count
    from ( select /*+ no_merge */ object_name,subobject_name,data_object_id
        from       user_objects
    SQL>   2      where        object_type like 'TABLE%'
      3    4    5      and subobject_name is not null
        )   obj,
      6      (
        select /*+ no_merge */ dbms_mview.pmarker(rowid) data_object_id, count(*) row_count from FLEXFIN.GWTB_MSG_IN_HISTORY
        group by
        )   rct
      7    8    9   10  where     rct.data_object_id = obj.data_object_id order by obj.object_name, obj.subobject_name
     11  ;
     12   13
    no rows selected
    Elapsed: 00:07:25.60
    SQL> select table_owner, table_name, partition_name from dba_tab_partitions where table_name='GWTB_MSG_IN_HISTORY';

    Comment by sjbabu — January 4, 2017 @ 3:57 pm BST Jan 4,2017 | Reply

    • Are you connected as user FLEXFIN ?

      The first part of the query is against user_objects not dba_objects (which means that if you’re not connected as the owning user then the relevant data_object_id values won’t appear in that part of the query).

      Comment by Jonathan Lewis — January 4, 2017 @ 5:59 pm BST Jan 4,2017 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Powered by