银行数据库
对一个表添加一个列后，以前的记录在page页内的存储方式？
假设新建一个表 create test1 (c1 int)
插入三个记录，值分别为1，2，3。简单假设在page上的0~3字节保存了记录1，4~7字节保持记录2，8~11字节保存记录3.
之后增加一列c2：alter table add column c2 int
这样每条记录占用的量应该由4字节增加到8字节，我想问一下，在增加一列后，以前的记录是自动reorg扩到现在的大小，比如说第一个记录从占有0~3字节变为占0~7字节，还是在先前的每条记录处定义一个指针，在这个page的空闲处存放新添加的列？
插入三个记录，值分别为1，2，3。简单假设在page上的0~3字节保存了记录1，4~7字节保持记录2，8~11字节保存记录3.
之后增加一列c2：alter table add column c2 int
这样每条记录占用的量应该由4字节增加到8字节，我想问一下，在增加一列后，以前的记录是自动reorg扩到现在的大小，比如说第一个记录从占有0~3字节变为占0~7字节，还是在先前的每条记录处定义一个指针，在这个page的空闲处存放新添加的列？
9同行回答
回答得好！收起
浏览350
When a new column is added to an existing table, only the table description in the system catalog is ...stephensun123 发表于 2010-10-21 17:25
解释的真不赖！ 顶一个！When a new column is added to an existing table, only the table description in the system catalog is ...
stephensun123 发表于 2010-10-21 17:25
浏览336
When a new column is added to an existing table, only the table description in the system catalog is ...
stephensun123 发表于 2010-10-21 17:25
是否可以分享一下出处？收起
浏览341
When a new column is added to an existing table, only the table description in the system catalog is modified, so access time to the table is not affected immediately. Existing records are not physically altered until they are modified using an UPDATE statement. When retrieving an existing row from the table, a null or default value is provided for the new column, depending on how the new column was defined. Columns that are added after a table is created cannot be defined as NOT NULL: they must be defined as either NOT NULL WITH DEFAULT or as nullable.
So because the existing rows are not changed at the time of the ALTER, they are shorter than any new rows. They will stay like this until they are updated or the table is REORGed. When the update happened, if no enough space around the record, there will be a page reorg or even a overflow.
So reorg the table after that is a good idea.收起
So because the existing rows are not changed at the time of the ALTER, they are shorter than any new rows. They will stay like this until they are updated or the table is REORGed. When the update happened, if no enough space around the record, there will be a page reorg or even a overflow.
So reorg the table after that is a good idea.收起
浏览327