query to find various combinations search and counts in single query oracle

Question

Hi all i have come across a requirement to find out the counts for the columns (col1,col2,col3) across a specific schema (SCHEMA_A) ,but here is a catch i have to find out a combination of all the column in order to get the counts how many tables use VALID_FROM_DATE, VALID_FROM_DATE, TIMESTAMP ,below are more details

Database - Oracle 11g

Table searching in - DBA_TAB_COLUMNS

col1 - VALID_FROM_DATE

col2 - VALID_TO_DATE

col3 - TIMESTAMP

combination search along with counts across all the tables

combination 1 
VALID_FROM_DATE + VALID_TO_DATE + TIMESTAMP

combination 2
VALID_FROM_DATE + VALID_TO_DATE 

combination 3 
VALID_FROM_DATE + TIMESTAMP

combination 4 
VALID_TO_DATE + TIMESTAMP

combination 5 
VALID_FROM_DATE

combination 6 
VALID_TO_DATE

combination 7 
TIMESTAMP

Query - I am trying

select count(distinct table_name) from DBA_TAB_COLUMNS
where owner='SCHEMA_A'
and column_name in ('VALID_FROM_DATE','VALID_TO_DATE','TIMESTAMP')
order by table_name;

but how to achieve the above combinations (1-7) all in a single query to capture the counts also overall percentage ,is it possible to acheive in a simplest possible way one single query block

percentage - round(100*ratio_to_report(count(*)) over (), 2) percentage

any suggestions ?

Answer 1

I believe full outer joins will work here.

The subqueries here are being used to find out tables having that columns and the results is then being joined to provide us with all possible combination.

On the last step the combinations are to be counted.

select case when vfd.table_name is not null then 'VALID_FROM_DATE' else null end VALID_FROM_DATE, 
       case when vtd.table_name is not null then 'VALID_TO_DATE' else null end VALID_TO_DATE, 
       case when ts.table_name  is not null then 'TIMESTAMP' else null end TIMESTAMP_col,
       count(1) tables_cnt
  from            (select table_name from dba_tab_cols where column_name = 'VALID_FROM_DATE') vfd -- tables with 'VALID_FROM'
  full outer join (select table_name from dba_tab_cols where column_name = 'VALID_TO_DATE') vtd   -- tables with 'VALID_TO'
    on vfd.table_name = vtd.table_name
  full outer join (select table_name from dba_tab_cols where column_name = 'TIMESTAMP') ts        -- tables with 'TIMESTAMP'
    on vfd.table_name = ts.table_name
 where vfd.table_name is not null 
    or vtd.table_name is not null
    or ts.table_name is not null
 group by case when vfd.table_name is not null then 'VALID_FROM_DATE' else null end, 
          case when vtd.table_name is not null then 'VALID_TO_DATE' else null end, 
          case when ts.table_name  is not null then 'TIMESTAMP' else null end

The subqueries I've used here to achieve better readability only. In case if someone yells at you that sub-queries are wrong here, you may use the following version

select case when vfd.table_name is not null then 'VALID_FROM_DATE' else null end VALID_FROM_DATE, 
       case when vtd.table_name is not null then 'VALID_TO_DATE' else null end VALID_TO_DATE, 
       case when ts.table_name  is not null then 'TIMESTAMP' else null end TIMESTAMP_col,
       count(1) tables_cnt
  from user_tab_cols vfd -- tables with 'VALID_FROM'
  full outer join user_tab_cols vtd   -- tables with 'VALID_TO'
    on vfd.table_name = vtd.table_name
    and vfd.column_name = 'VALID_FROM_DATE'
    and vtd.column_name = 'VALID_TO_DATE'
  full outer join user_tab_cols ts        -- tables with 'TIMESTAMP'
    on vfd.table_name = ts.table_name
    and vfd.column_name = 'VALID_FROM_DATE'
    and ts.column_name = 'TIMESTAMP'
 where vfd.column_name = 'VALID_FROM_DATE' 
    or vtd.column_name = 'VALID_TO_DATE' 
    or ts.column_name = 'TIMESTAMP'
 group by case when vfd.table_name is not null then 'VALID_FROM_DATE' else null end, 
          case when vtd.table_name is not null then 'VALID_TO_DATE' else null end, 
          case when ts.table_name  is not null then 'TIMESTAMP' else null end;

In case of performance issue, adding an index on column_name of dba_tab_cols will PROBABLY help.