Combining UNION, count(*) and rowid in SQL, aka determine blocks used by 2 tables

Question

I have 2 tables MOTHER and CHILD. I want to determine how many blocks they use together. Code I usually use to determine this for just one table

select count(count(dbms_rowid.rowid_block_number(rowid))) 
from MOTHER group by dbms_rowid.rowid_block_number(rowid);

but since I need both tables and they might be using the same blocks I tried experimenting. So I read that UNION might solve this but I have problems using it according to the tutorials

select tem.dbms_rowid.rowid_block_number(rowid), count(*) from
(select dbms_rowid.rowid_block_number(rowid) from MOTHER 
union
select dbms_rowid.rowid_block_number(rowid) from CHILD ) AS tem
group by dbms_rowid.rowid_block_number(rowid);

it doesn't work and shows an error at the AS tem part. It says syntax error. message:

ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:
*Action: Error at Line: 18 Column: 58

Answer 1

There are several mistakes in the query.

The first one, which causes the immediate error, is the keyword AS. In Oracle (different from other database products, from what I understand), the keyword AS is optional for column aliases, and prohibited for table/subquery aliases. Delete the word AS before the alias tem and that error is fixed.

Then, in the outer select you still want to select from dbms_rowid - but that is not possible, you already gave an alias to the subquery, so you can only select from it, not from the tables referenced in the two branches of union.

Here is one way to correct it:

select   rowid_block_number, count(*) as cnt    -- DON'T USE THIS QUERY! (SEE BELOW)
from     
         ( 
           select dbms_rowid.rowid_block_number(rowid) as rowid_block_number from MOTHER 
           union
           select dbms_rowid.rowid_block_number(rowid) from CHILD
         )
group by rowid_block_number
;

Notice that I did away with the alias for the subquery - it is not needed. Also, I gave an alias to the column resulting from the application of rowid_block_number(rowid) - you can't call it as is in the outer query.

Now, this is syntactically correct, but the logic is non-existent. For your first count, I have no idea why you group by, and select counts twice. You should simply

select count(distinct dbms_rowid.rowid_block_number(rowid)) as block_count
from   MOTHER;

and if you must do this for two different tables:

select count(distinct rowid_block_number) as block_count
from   
       ( 
         select dbms_rowid.rowid_block_number(rowid) as rowid_block_number from MOTHER
         union all
         select dbms_rowid.rowid_block_number(rowid) from CHILD
       )
;

or, if you will use union instead of union all, that will already eliminate duplicates, so just select count(rowid_block_number) from the subquery. (That is: you do need some form of "distinct", but you only need it once. Either count distinct in the outer query, or use union as you were doing, instead of union all, in the subquery, but then you don't need "distinct" in the outer query.)