Counting

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