=====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 => [[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.
-- 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 ===
* 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