Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:tablen_spalten_zaehlen

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
prog:tablen_spalten_zaehlen [2018/03/01 19:19]
gpipperr [Zählen wieviele unterschiedliche Werte in den Tabellen eines Schemas stehen]
prog:tablen_spalten_zaehlen [2018/03/02 14:12] (aktuell)
gpipperr
Zeile 1: Zeile 1:
 +===== 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 ====
 +
 +<code sql>
 +-- 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
 +);
 +
 +</code>
 +
 +----
 +
 +
 +==== Analyse Funktion anlegen ====
 +
 +
 +Hier auf alle Spalten, um das schneller zu bekommen nur auf die Nullable Spalten einschränken!
 +<code sql column_count.sql>
 +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
 +   <<TAB_LOOP>>
 +   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));
 +        <<COL_LOOP>>
 +        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;
 +/
 +
 +</code>
 +
 +----
 +
 +==== Ausführen ====
 +
 +Starten mit:
 +
 +<code sql>
 +exec column_count
 +</code>
 +
 +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:
 +<code sql>
 +
 +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
 +
 +</code>
 +
 +
 +
  
"Autor: Gunther Pipperr"
prog/tablen_spalten_zaehlen.txt · Zuletzt geändert: 2018/03/02 14:12 von gpipperr