Have a few days leave and come back to this?
Thanks for the lambasting of my SQL skills Daniel. I don't really see
the inference you draw. Then you post some rather simple SQL using inline
views, UNIONS outerjoins and WITH clauses - inferring i would not
be able to understand them. In fact to me your first query seems so
much shorter, simpler and easy to understand as:

SELECT a.num_rows, index_name
FROM user_tables a
LEFT OUTER JOIN user_indexes b USING (table_name);

Perhaps you need some SQL training in simplifing your SQL and saving
your employees money - but since consultants seem to change by the
hour :)

I also don't advocate re-work (converting existing queries) unless
there is some need - such as a performance problem.