prog:orcle_drop_column
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende Überarbeitung | |||
prog:orcle_drop_column [2015/03/16 16:55] – [Beispiel 2 - Spalte hinzufügen und dann löschen] gpipperr | prog: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** " | ||
+ | |||
+ | 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, | ||
+ | |||
+ | |||
+ | ==== Beispiel 1 - Spalten löschen ==== | ||
+ | |||
+ | <code sql> | ||
+ | -- create table | ||
+ | create table gpi.col_t (id number,a1 varchar2(20), | ||
+ | |||
+ | -- insert data | ||
+ | declare | ||
+ | | ||
+ | begin | ||
+ | select nvl(max(id), | ||
+ | for i in v_start..100000 | ||
+ | loop | ||
+ | insert into gpi.col_t (id ,a1, a2, a3, a4) | ||
+ | values ( i | ||
+ | , | ||
+ | , | ||
+ | , | ||
+ | , | ||
+ | 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 | ||
+ | -------------------- ---------- ------------- ------------ --------- ---------- | ||
+ | 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 | ||
+ | Info -- Total Count of blocks that are full in the segment | ||
+ | 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 | ||
+ | Info -- Used total_bytes | ||
+ | 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 | ||
+ | -------------------- ---------- ------------- ------------ --------- ---------- | ||
+ | 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 | ||
+ | Info -- Total Count of blocks that are full in the segment | ||
+ | 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 | ||
+ | Info -- Used total_bytes | ||
+ | Info -- Unused block :0 | ||
+ | |||
+ | |||
+ | </ | ||
+ | |||
+ | Für das Script tab_space siehe hier => [[http:// | ||
+ | |||
+ | ==== 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), | ||
+ | |||
+ | |||
+ | -- fill with data | ||
+ | declare | ||
+ | | ||
+ | begin | ||
+ | select nvl(max(id), | ||
+ | | ||
+ | for i in v_start..1570000 | ||
+ | loop | ||
+ | insert into gpi.col_t (id ,a1, a2, a3, a4) | ||
+ | values ( i | ||
+ | , | ||
+ | , | ||
+ | , | ||
+ | , | ||
+ | if mod(i, | ||
+ | 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 | ||
+ | -------------------- ---------- ------------- ------------ --------- ---------- | ||
+ | COL_T GPI | ||
+ | |||
+ | |||
+ | Info -- Call dbms_space.space_usage for table ( Type:TABLE ) ::COL_T | ||
+ | Info ------------------------------------------------------------------ | ||
+ | Info -- Total Count of blocks that are unformatted | ||
+ | Info -- Total Count of blocks that are full in the segment | ||
+ | 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 | ||
+ | Info -- Used total_bytes | ||
+ | |||
+ | |||
+ | -- 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 | ||
+ | | ||
+ | | ||
+ | begin | ||
+ | select nvl(min(id), | ||
+ | for i in v_start..v_end | ||
+ | loop | ||
+ | update gpi.col_t set a5 = dbms_random.string(' | ||
+ | if mod(i, | ||
+ | | ||
+ | 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 | ||
+ | -------------------- ---------- ------------- ------------ --------- ---------- | ||
+ | COL_T GPI | ||
+ | |||
+ | Info -- Call dbms_space.space_usage for table ( Type:TABLE ) ::COL_T | ||
+ | Info ------------------------------------------------------------------ | ||
+ | Info -- Total Count of blocks that are unformatted | ||
+ | Info -- Total Count of blocks that are full in the segment | ||
+ | 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 | ||
+ | Info -- Used total_bytes | ||
+ | |||
+ | -- check for chained rows | ||
+ | |||
+ | | ||
+ | |||
+ | |||
+ | OK - No Chained rows | ||
+ | |||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | === Mehr zu Chained Rows === | ||
+ | |||
+ | * http:// | ||
+ | * https:// | ||
+ | |||
+ |
prog/orcle_drop_column.txt · Zuletzt geändert: 2015/03/16 16:55 von gpipperr