Count Rows in Partition Where…

Just an illustration of a SQL / XML utility to count rows in a partition.

In my case, my requirement was to count rows in all subpartitions where there is data which might not belong in that partition (due to NOVALIDATE partition exchange). My thoughts were that I wanted the equivalent of a NESTED LOOP – for each partition… do XYZ and was happy for this to run for a while in the background without consuming significant parallel resources.

Partition keys here are not nullable.

with s as
(select /*+ */ 
        sp.table_owner, sp.table_name, sp.subpartition_name
 ,      sn.snapshot_id, sn.version
 from   dba_tab_subpartitions sp 
 join   my_snaphot_table sn on (sn.subpartition_name = sp.subpartition_name)
 where  sp.table_owner = :owner 
 and    sp.table_name  = :table_name)
select s.*, x.cnt
from   s
 ,      xmltable('for $i in /ROWSET/ROW/CNT
                  return $i'
                 passing xmltype(
                          dbms_xmlgen.getxml
                          ('select /*+ parallel(2) */ count(*) cnt '
                         ||'from '||s.table_owner||'.'||s.table_name||' SUBPARTITION ('||s.subpartition_name||') '
                         ||'where (snapshot_id, snapshot_version) != (('''||s.snapshot_id||''', '||s.version||'))'
                           ))
                 columns cnt number path '/') x
where x.cnt > 0;