Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
=====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