===== Zählen wieviele unterschiedliche Werte in den Tabellen eines Schemas stehen =====
**Aufgabe**: Es sollen alle Spalten in den Tabellen eines Schemas gefunden werden in denen gar KEINE Werte stehen!
Im ersten Ansatz kann auch die Tabelle "USER_TAB_COLS" ausgewertet werden, es kommen ja nur alle Spalten in Betracht die überhaupt NULL Werte enthalten dürfen, die also NULLABLE sind.
Die Statisik auf den Tabellen kann auch einen Hinweis dazugeben, allerdings müsste diese recht neu sein und die Tabelle zu 100% gerade analysiert haben.
In einem laufenden System wäre das aber schlecht hier die Statisiken plötzlich nur für diese Anforderung anders anzulegen.
Da hilft wohl dann nur einfach zählen, wieviele Zeilen es gibt, wieviel verschiedene Werte es in jeder Spalte gibt und wieviele davon NULL sind. Fall eine Spalte gefunden wird in der nur NULL Werte sind diese expliziet kennzeichnen.
----
==== Hilfstabelle anlegen ====
-- Create the result table
create table TAB_COL_COUNT
( schema varchar2(32) -- Schema Name of the table
, table_name varchar2(32) -- Table Name
, total_count number(11) -- Total Number of Rows
, column_name varchar2(32) -- Name of the column
, column_count number(11) -- Distinct values with out null
, null_count number(11) -- Values with null
, column_null varchar2(1) -- If all is null set to Y for anlayse
);
----
==== Analyse Funktion anlegen ====
Hier auf alle Spalten, um das schneller zu bekommen nur auf die Nullable Spalten einschränken!
create or replace procedure column_count
is
--------------------------
-- first create the table
-- drop table TAB_COL_COUNT;
-- create table TAB_COL_COUNT ( schema varchar2(32), table_name varchar2(32), column_name varchar2(32),column_count number(11),column_null varchar2(1),total_count number(11),null_count number(11));
------------------------
-- get all Tables of the user
cursor c_all_tables
is select table_name from user_tables
-- if you need only some tables
-- where table_name in ('TT')
;
-- get all columns with possible null values and not long or raw
cursor c_all_cols(p_tab_name varchar2)
is select column_name,data_type from user_tab_cols
where table_name=p_tab_name
and data_type not in ('LONG','RAW')
and nullable='Y'
;
v_count number(11);
v_null_count number(11);
v_total_count number(11);
-- Normal query template
v_count_template varchar2(512):='select count(werte),sum(null_count) from ( select ##COL_NAME## as werte ,sum(decode(##COL_NAME##,null,1,0)) as null_count from ##TABNAME## group by ##COL_NAME##,decode(##COL_NAME##,null,1,0))';
-- SQL Template to handle CLOB/BLOB we only use the first 10 char to check
v_count_template_lob varchar2(512):='select count(1),sum(null_count) from ( select count(1),UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(##COL_NAME##,1,10)) ,sum(decode(UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(##COL_NAME##,1,10)),null,1,0)) as null_count from ##TABNAME## group by UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(##COL_NAME##,1,10)),decode(UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(##COL_NAME##,1,10)),null,1,0)) ';
-- Total Count
v_total_count_template varchar2(512):='select count(1) from ##TABNAME##';
v_sql varchar2(512);
-- timing variables
v_start_time TIMESTAMP:=CURRENT_TIMESTAMP;
v_time_dif INTERVAL DAY TO SECOND;
v_Seconds NUMBER ;
begin
dbms_output.put_line('-- ----- Start --------------- ');
--clear result table for each run
execute immediate 'TRUNCATE TABLE TAB_COL_COUNT';
-- Loop over all tables of the user with this procdure
<>
for rec in c_all_tables
loop
v_sql:=replace(v_total_count_template,'##TABNAME##',rec.table_name);
execute immediate v_sql into v_total_count;
dbms_output.put_line('-- Info :: Count table :: '|| rec.table_name ||' Total count ::'||to_char(v_total_count));
<>
for colrec in c_all_cols(p_tab_name => rec.table_name)
loop
v_count:=0;
v_null_count:=0;
-- use right sql template to handle binary
if colrec.data_type not in ('CLOB','BLOB') then
v_sql:=replace(replace(v_count_template,'##TABNAME##',rec.table_name),'##COL_NAME##',colrec.column_name);
else
v_sql:=replace(replace(v_count_template_lob,'##TABNAME##',rec.table_name),'##COL_NAME##',colrec.column_name);
end if;
begin
-- only if values in the table
if v_total_count > 0 then
execute immediate v_sql into v_count,v_null_count;
end if;
-- remeber results
insert into TAB_COL_COUNT ( schema, table_name,column_name,column_count, column_null,total_count,null_count)
values ( user, rec.table_name,colrec.column_name, v_count,decode(v_count,'0','Y','N'),v_total_count,v_null_count);
exception
when others then
dbms_output.put_line('-- Error :: '||SQLERRM);
dbms_output.put_line('-- Error :: execute SQL '||v_sql);
-- remeber the column but set values t0 -1 to identifiy trouble columns
insert into TAB_COL_COUNT ( schema, table_name,column_name,column_count, column_null,total_count,null_count)
values
( user, rec.table_name,colrec.column_name, -1,'X',v_total_count,-1);
end;
end loop COL_LOOP;
end loop TAB_LOOP;
commit;
-- get the timinig
v_time_dif:=CURRENT_TIMESTAMP-v_start_time;
SELECT EXTRACT( DAY FROM v_time_dif ) * 86400
+ EXTRACT( HOUR FROM v_time_dif ) * 3600
+ EXTRACT( MINUTE FROM v_time_dif ) * 60
+ EXTRACT( SECOND FROM v_time_dif )
INTO
v_Seconds
FROM DUAL ;
dbms_output.put_line('-- ----- Finsh --------------- ');
dbms_output.put_line('-- Info :: the run need '|| to_char(v_Seconds,'999G990D999'));
end;
/
----
==== Ausführen ====
Starten mit:
exec column_count
Das ganze kann jetzt natürlich noch weiter ausgebaut werden.
Interssant wäre es auch nun eine 100% Statistik auf die Tabellen anzulegen und zu vergleichen ob sich hier Unterschiede ergeben.
In einem größeren Schema kann das jetzt auch etwas dauern!
----
====Auswerten====
Zeige alle Spalten die Null werden können und nun tatsächlich keine Werte enthalten, obwohl die Tabelle selber Datensätze enthält:
select t.table_name
,t.column_name
from TAB_COL_COUNT t
inner join user_tab_cols c on ( t.table_name=c.table_name)
where c.nullable='Y'
and t.column_null='Y'
and t.TOTAL_COUNT > 0