Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:orcle_drop_column

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

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

Für das Script tab_space siehe hier ⇒ 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.

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

Mehr zu Chained Rows

Diese Website verwendet Cookies. Durch die Nutzung der Website stimmen Sie dem Speichern von Cookies auf Ihrem Computer zu. Außerdem bestätigen Sie, dass Sie unsere Datenschutzbestimmungen gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website.Weitere Information
prog/orcle_drop_column.txt · Zuletzt geändert: 2015/03/16 16:55 von gpipperr