prog:tablen_spalten_zaehlen
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende Überarbeitung | ||
prog:tablen_spalten_zaehlen [2018/03/01 19:16] – [Zählen wieviele unterschiedliche Werte in den Tabellen eines Schemas stehen] gpipperr | 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**: | ||
+ | |||
+ | |||
+ | Im ersten Ansatz kann auch die Tabelle " | ||
+ | |||
+ | 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) | ||
+ | , table_name varchar2(32) | ||
+ | , total_count number(11) | ||
+ | , column_name varchar2(32) -- Name of the column | ||
+ | , column_count number(11) | ||
+ | , null_count number(11) | ||
+ | , column_null varchar2(1) | ||
+ | ); | ||
+ | |||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | ==== 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), | ||
+ | ------------------------ | ||
+ | | ||
+ | -- 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 (' | ||
+ | ; | ||
+ | | ||
+ | -- get all columns with possible null values and not long or raw | ||
+ | cursor c_all_cols(p_tab_name varchar2) | ||
+ | is select column_name, | ||
+ | where table_name=p_tab_name | ||
+ | and data_type not in (' | ||
+ | and nullable=' | ||
+ | ; | ||
+ | |||
+ | v_count number(11); | ||
+ | v_null_count number(11); | ||
+ | v_total_count number(11); | ||
+ | -- Normal query template | ||
+ | v_count_template varchar2(512): | ||
+ | -- SQL Template to handle CLOB/BLOB we only use the first 10 char to check | ||
+ | v_count_template_lob varchar2(512): | ||
+ | -- Total Count | ||
+ | v_total_count_template varchar2(512): | ||
+ | v_sql varchar2(512); | ||
+ | | ||
+ | -- timing variables | ||
+ | v_start_time TIMESTAMP: | ||
+ | v_time_dif INTERVAL DAY TO SECOND; | ||
+ | v_Seconds NUMBER ; | ||
+ | begin | ||
+ | |||
+ | | ||
+ | |||
+ | | ||
+ | | ||
+ | |||
+ | -- Loop over all tables of the user with this procdure | ||
+ | << | ||
+ | for rec in c_all_tables | ||
+ | loop | ||
+ | |||
+ | v_sql: | ||
+ | execute immediate v_sql into v_total_count; | ||
+ | dbms_output.put_line(' | ||
+ | << | ||
+ | for colrec in c_all_cols(p_tab_name => rec.table_name) | ||
+ | loop | ||
+ | |||
+ | v_count:=0; | ||
+ | v_null_count: | ||
+ | | ||
+ | -- use right sql template to handle binary | ||
+ | if colrec.data_type not in (' | ||
+ | v_sql: | ||
+ | else | ||
+ | v_sql: | ||
+ | end if; | ||
+ | | ||
+ | begin | ||
+ | -- only if values in the table | ||
+ | if v_total_count > 0 then | ||
+ | execute immediate v_sql into v_count, | ||
+ | end if; | ||
+ | -- remeber results | ||
+ | insert into TAB_COL_COUNT ( schema, table_name, | ||
+ | values ( user, rec.table_name, | ||
+ | exception | ||
+ | when others then | ||
+ | dbms_output.put_line(' | ||
+ | dbms_output.put_line(' | ||
+ | -- remeber the column but set values t0 -1 to identifiy trouble columns | ||
+ | insert into TAB_COL_COUNT ( schema, table_name, | ||
+ | values | ||
+ | ( user, rec.table_name, | ||
+ | end; | ||
+ | |||
+ | end loop COL_LOOP; | ||
+ | | ||
+ | end loop TAB_LOOP; | ||
+ | | ||
+ | commit; | ||
+ | | ||
+ | -- get the timinig | ||
+ | v_time_dif: | ||
+ | |||
+ | 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(' | ||
+ | dbms_output.put_line(' | ||
+ | end; | ||
+ | / | ||
+ | |||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Ausführen ==== | ||
+ | |||
+ | Starten mit: | ||
+ | |||
+ | <code sql> | ||
+ | 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: | ||
+ | <code sql> | ||
+ | |||
+ | select t.table_name | ||
+ | , | ||
+ | from TAB_COL_COUNT t | ||
+ | inner join user_tab_cols c on ( t.table_name=c.table_name) | ||
+ | where c.nullable=' | ||
+ | and t.column_null=' | ||
+ | and t.TOTAL_COUNT > 0 | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | |||
prog/tablen_spalten_zaehlen.txt · Zuletzt geändert: 2018/03/02 14:12 von gpipperr