Oracle Scratchpad

November 25, 2009

Counting

Filed under: 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 comp.databases.oracle. 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
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
rem     The number of objects in the schema will also affect the
rem     also affect performance since the query includes a call
rem     to instantiate a complete list of them.
rem
rem     Change the following define to supply the target table name
rem

define m_tab_name = pt_range

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

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 dbms_rowid.rowid_object() 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(*).

5 Comments »

  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?

    Thanks

    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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,873 other followers