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.
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 pt_range -- Your table name goes here. 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(*).
