Table size reduced after "ALTER TABLE ADD COLUMN"

Database

Table size reduced after "ALTER TABLE ADD COLUMN"

Hello people,

We executed the following ALTER TABLE Statement on the CDR table of our CLDM:

"ALTER TABLE     DP_TAB.XXXX    ADD

DATA_VOLUME_TRANSFER DECIMAL(18,0) DEFAULT 0 COMPRESS 0,

ADD

NETWORK_EQUIP_INST_ID SMALLINT DEFAULT 0  COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ),

ADD

NETWORK_TYPE_CD SMALLINT DEFAULT 2 COMPRESS (1,2,3),

ADD

WEB_SITE_GRP_CD INTEGER DEFAULT 0  COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ,13 ,14 ,15 ,16 ,17 ,18 ,19 ,20 ,21 ,22 ,23 ,24 ,25 ,26 ,27 ,28 ,29 ,30 ,31 ,32 ,33 ,34 ,35 ,36 ,37 ,38 ,39 ,40 ,41 ,42 ,43 ,44 ,45 ,46 ,47 ,48 ,49 ,50 ,51 ,52 ,53 ,54 ,55 ,56 ,57 ,58 ,59 ,60 ,61 ,62 ,63 ,64 ,65 ,66 ,67 ,68 ,69 ,70 ,71 ,72 ,73 ,74 ,75 ,76 ,77 ,78 ,79 ,80 ,81 ,82 ,83 ,84 ,85 ,86 ,87 ,88 ,89 ,90 ,91 ,92 ,93 ,94 ,95 ,96 ,97 ,98 ,99 ,100 ,101 ,102 ,103 ,104 ,105 ,106 ,107 ,108 ,109 ,110 ,111 ,112 ,113 ,114 ,115 ,116 ,117 ,118 ,119 ,120 ,121 ,122 ,123 ,124 ,125 ,126 ,127 ,128 ,129 ,130 ,131 ,132 ,133 ,134 ,135 ,136 ,137 ,138 ,139 ,140 ,141 ,142 ,143 ,144 ,145 ,146 ,147 ,148 ,149 ,150 ,151 ,152 ,153 ,154 ,155 ,156 ,157 ,158 ,159 ,160 ,161 ,162 ,163 ,164 ,165 ,166 ,167 ,168 ,169 ,170 ,171 ,172 ,173 ,174 ,175 ,176 ,177 ,178 ,179 ,180 ,181 ,182 ,183 ,184 ,185 ,186 ,187 ,188 ,189 ,190 ,191 ,192 ,193 ,194 ,195 ,196 ,197 ,198 ,199 ,200 ,201 ,202 ,203 ,204 ,205 ,206 ,207 ,208 ,209 ,210 ,211 ,212 ,213 ,214 ,215 ,216 ,217 ,218 ,219 ,220 ,221 ,222 ,223 ,224 ,225 ,226 ,227 ,228 ,229 ,230 ,231 ,232 ,233 ,234 ,235 ,236 ,237 ,238 ,239 ,240 ,241 ,242 ,243 ,244 ,245 ,246 ,247 ,248 ,249 ,250 ,251 ,252 ,253 );"

Surprisingly, the table size after the ALTER TABLE execution was reduced. Can anyone explain this? We sought support from Teradata local team on this, but couldn't get a satisfactory answer.

Here are some auxiliary details:

We are on Teradata 12.

InfoKey

InfoData

LANGUAGE SUPPORT MODE

Standard

RELEASE

12.00.03.33

VERSION

12.00.03.33

Table size statistics:

CDR TABLE

EDW SIZE

Before Alter Table


2,895,515,862,528

After Alter Table


2,889,742,036,480

Difference


5,773,826,048

Would appreciate a comprehensive response.

Tags (1)
1 REPLY
Enthusiast

Re: Table size reduced after "ALTER TABLE ADD COLUMN"

Considering that the difference is less than .2 percent, it may be attributable to a reduction in data block fragmentation.