Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:tablen_spalten_zaehlen

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!

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;
/

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
Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
prog/tablen_spalten_zaehlen.txt · Zuletzt geändert: 2018/03/02 14:12 von Gunther Pippèrr