Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:orcle_drop_column

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
prog:orcle_drop_column [2015/03/16 16:55] – [Beispiel 2 - Spalte hinzufügen und dann löschen] gpipperrprog:orcle_drop_column [2015/03/16 16:55] (aktuell) – [Mehr zu Chained Rows] gpipperr
Zeile 1: Zeile 1:
 +=====Eine Spalte von einer Tabelle entfernen - drop column=====
  
 +Eine Spalte in einer Tabelle kann mit **ALTER TABLE .. DROP COLUMN** wieder entfernt werden. 
 +
 +Ist die Tabelle leer oder recht klein kann diese auch durchaus durchgeführt werden.
 +
 +Hat die Tabelle aber eine gewisse Größe sollte ausreichend Laufzeit für die Operation eingeplant werden. Da beim Entfernen einer Spalte ALLE Zeilen einer Tabelle gelesen und bearbeitet werden müssen ist mit einer hohen Last auf der Datenbank zu rechnen.
 +
 +
 +Alternativ kann aber die Spalte mit **ALTER TABLE .. SET UNUSED** "ausgeblendet" werden. 
 +
 +Die Spalte kann nicht mehr verwendet werden und wird im Data Dictionary ausgeblendet. 
 +Eine neue Spalte kann sogar den zuvor vergebenen Namen weiter verwenden. 
 +
 +Wenn dann später Zeit ist, kann die Spalte mit **ALTER TABLE...DROP UNUSED COLUMNS** entfernt werden.
 +
 +Wird aber als nächstes ein **alter table .. drop column** durchgeführt, werden auch die anderen früher "versteckten" Spalte mit gelöscht!
 +
 +
 +==== Beispiel 1 - Spalten löschen ====
 +
 +<code sql>
 +-- create table
 +create table gpi.col_t (id number,a1 varchar2(20), a2 varchar(10), a3 number, a4 clob);
 +
 +-- insert data
 +declare
 + v_start number;
 +begin
 +  select nvl(max(id),0) into v_start from gpi.col_t;
 +  for i in v_start..100000
 +  loop
 +    insert into gpi.col_t (id ,a1, a2, a3, a4) 
 + values ( i
 +         ,dbms_random.string('A',20)
 + ,dbms_random.string('A',10)
 + ,dbms_random.random
 + ,dbms_random.string('A',100));
 +  end loop;  
 +end;
 +/  
 +
 +commit;
 +
 +-- retry some times
 +select count(*) from 
 +
 +    COUNT(*)
 +------------
 +      100015
 +      
 +-- check the size of the table (script tab_space)
 +
 +Space Usage of the table GPI.COL_T
 +
 +SEGMENT_NAME         OWNER            SIZE_MB    COUNT_BLK COUNT_EXT COUNT_PART
 +-------------------- ---------- ------------- ------------ --------- ----------
 +COL_T                GPI                32,00        4.096        47          1
 +      
 +
 +Info -- Call dbms_space.space_usage for table ( Type:TABLE ) ::COL_T
 +Info ------------------------------------------------------------------
 +Info -- Total Count of blocks that are unformatted              :0 |Bytes :0
 +Info -- Total Count of blocks that are full in the segment      :4000 |Bytes :32768000
 +Info --
 +Info -- Count of blocks that has at least 0  to 25%  free space :0 |Bytes :0
 +Info -- Count of blocks that has at least 25 to 50%  free space :1 |Bytes :8192
 +Info -- Count of blocks that has at least 50 to 75%  free space :0 |Bytes :0
 +Info -- Count of blocks that has at least 75 to 100% free space :23 |Bytes :188416
 +Info ------------------------------------------------------------------
 +Info -- Call dbms_space.UNUSED_SPACE for table  ( Type:TABLE ) ::COL_T
 +Info ------------------------------------------------------------------
 +Info -- Used total_blocks                           :4096
 +Info -- Used total_bytes                            :33554432
 +Info -- Unused block                                :0
 +
 +-- set the colum to unuse
 +
 +
 +ALTER TABLE gpi.col_t set UNUSED (a1,a3,a4);
 +
 +
 +-- check Space - nothing changed
 +
 +-- remove the column
 +
 +
 +ALTER TABLE gpi.col_t DROP UNUSED COLUMNS;
 +
 +-- check Space - more free blocks
 +
 +Space Usage of the table GPI.COL_T
 +
 +SEGMENT_NAME         OWNER            SIZE_MB    COUNT_BLK COUNT_EXT COUNT_PART
 +-------------------- ---------- ------------- ------------ --------- ----------
 +COL_T                GPI                32,00        4.096        47          1
 +
 +Info -- Call dbms_space.space_usage for table ( Type:TABLE ) ::COL_T
 +Info ------------------------------------------------------------------
 +Info -- Total Count of blocks that are unformatted              :0 |Bytes :0
 +Info -- Total Count of blocks that are full in the segment      :0 |Bytes :0
 +Info --
 +Info -- Count of blocks that has at least 0  to 25%  free space :0 |Bytes :0
 +Info -- Count of blocks that has at least 25 to 50%  free space :0 |Bytes :0
 +Info -- Count of blocks that has at least 50 to 75%  free space :0 |Bytes :0
 +Info -- Count of blocks that has at least 75 to 100% free space :4024 |Bytes :32964608
 +Info ------------------------------------------------------------------
 +Info -- Call dbms_space.UNUSED_SPACE for table  ( Type:TABLE ) ::COL_T
 +Info ------------------------------------------------------------------
 +Info -- Used total_blocks                           :4096
 +Info -- Used total_bytes                            :33554432
 +Info -- Unused block                                :0
 +
 +
 +</code>
 +
 +Für das Script tab_space siehe hier => [[http://orapowershell.codeplex.com/SourceControl/latest#sql/tab_space.sql|tab_space]]
 +
 +==== Beispiel 2 - Spalte hinzufügen und dann löschen ====
 +
 +Wird in einer Tabelle eine Spalte hinzugefügt und mit einem Wert gefüllt besteht die Gefahr von Chained rows.
 +
 +Unsere Test Tabelle von oben wird erneut mit 1570001 Einträgen angelegt und nachträglich dann eine Spalte mit 32Byte hinzugefügt.
 +
 +<code sql>
 +-- for the timing
 +set timing on
 +set time on
 +
 +-- 
 +drop table gpi.col_t;
 +
 +-- create table
 +create table gpi.col_t (id number,a1 varchar2(20), a2 varchar(10), a3 number, a4 clob);
 +
 +
 +-- fill with data
 +declare
 + v_start number;
 +begin
 +  select nvl(max(id),0) into v_start from gpi.col_t;
 +  
 +  for i in v_start..1570000
 +  loop
 +    insert into gpi.col_t (id ,a1, a2, a3, a4) 
 + values ( i
 +         ,dbms_random.string('A',20)
 + ,dbms_random.string('A',10)
 + ,dbms_random.random
 + ,dbms_random.string('A',100));
 +   if mod(i,10000)=0 then 
 +    commit; 
 +   end if;
 +  end loop;  
 +  commit;
 +end;
 +/  
 +
 +-- take round about 5 minutes
 +
 +-- check the acutal size
 +
 +Space Usage of the table GPI.COL_T
 +
 +SEGMENT_NAME         OWNER            SIZE_MB    COUNT_BLK COUNT_EXT COUNT_PART
 +-------------------- ---------- ------------- ------------ --------- ----------
 +COL_T                GPI               500,00       64.000       134          1
 +
 +
 +Info -- Call dbms_space.space_usage for table ( Type:TABLE ) ::COL_T
 +Info ------------------------------------------------------------------
 +Info -- Total Count of blocks that are unformatted              :508 |Bytes :4161536
 +Info -- Total Count of blocks that are full in the segment      :62800 |Bytes :514457600
 +Info --
 +Info -- Count of blocks that has at least 0  to 25%  free space :0 |Bytes :0
 +Info -- Count of blocks that has at least 25 to 50%  free space :0 |Bytes :0
 +Info -- Count of blocks that has at least 50 to 75%  free space :0 |Bytes :0
 +Info -- Count of blocks that has at least 75 to 100% free space :338 |Bytes :2768896
 +Info ------------------------------------------------------------------
 +Info -- Call dbms_space.UNUSED_SPACE for table  ( Type:TABLE ) ::COL_T
 +Info ------------------------------------------------------------------
 +Info -- Used total_blocks                           :64000
 +Info -- Used total_bytes                            :524288000
 +
 +
 +-- add new column 
 +alter table gpi.col_t add (a5 varchar2(32));
 +
 +-- needs no time!
 +
 +-- update data
 +
 +-- helper index
 +create unique index gpi.col_t_id_idx on gpi.col_t(id);
 +Abgelaufen: 00:00:08.12
 +
 +-- update the data
 +
 +declare
 + v_start number;
 + v_end   number;
 +begin
 +  select nvl(min(id),0),nvl(max(id),0) into v_start,v_end from gpi.col_t;
 +  for i in v_start..v_end
 +  loop
 +    update gpi.col_t set a5 = dbms_random.string('A',32) where id=i;
 +    if mod(i,10000)=0 then 
 +     commit; 
 +    end if;
 +  end loop;  
 +  commit;
 +end;
 +/  
 +
 +
 +Abgelaufen: 00:07:35.78
 +
 +-- check the new size
 +-- we will add 1570001 *32 bytes = 50.240.032 Byte ~ 50MB new Data 
 +
 +Space Usage of the table GPI.COL_T
 +
 +SEGMENT_NAME         OWNER            SIZE_MB    COUNT_BLK COUNT_EXT COUNT_PART
 +-------------------- ---------- ------------- ------------ --------- ----------
 +COL_T                GPI               500,00       64.000       134          1
 +
 +Info -- Call dbms_space.space_usage for table ( Type:TABLE ) ::COL_T
 +Info ------------------------------------------------------------------
 +Info -- Total Count of blocks that are unformatted              :508 |Bytes :4161536
 +Info -- Total Count of blocks that are full in the segment      :62800 |Bytes :514457600
 +Info --
 +Info -- Count of blocks that has at least 0  to 25%  free space :0 |Bytes :0
 +Info -- Count of blocks that has at least 25 to 50%  free space :0 |Bytes :0
 +Info -- Count of blocks that has at least 50 to 75%  free space :0 |Bytes :0
 +Info -- Count of blocks that has at least 75 to 100% free space :338 |Bytes :2768896
 +Info ------------------------------------------------------------------
 +Info -- Call dbms_space.UNUSED_SPACE for table  ( Type:TABLE ) ::COL_T
 +Info ------------------------------------------------------------------
 +Info -- Used total_blocks                           :64000
 +Info -- Used total_bytes                            :524288000
 +
 +-- check for chained rows
 +
 + @analyse_changed_rows.sql
 +
 +
 +OK - No Chained rows
 +
 +
 +</code>
 +
 +
 +=== Mehr zu Chained Rows ===
 +
 +  * http://blog.tanelpoder.com/2009/11/04/detect-chained-and-migrated-rows-in-oracle/
 +  * https://docs.oracle.com/cd/E18283_01/server.112/e17120/general002.htm#i1006369
 +
 + 
prog/orcle_drop_column.txt · Zuletzt geändert: 2015/03/16 16:55 von gpipperr