Adding Table ColumnsTo add a column to an existing table, use the ALTER TABLE...ADD statement.The following statement alters the hr.admin_emp table to add a new column named bonus:ALTER TABLE hr.admin_emp ADD (bonus NUMBER (7...
显示全部Adding Table Columns
To add a column to an existing table, use the ALTER TABLE...ADD statement.
The following statement alters the hr.admin_emp table to add a new column named bonus:
ALTER TABLE hr.admin_emp
ADD (bonus NUMBER (7,2));
If a new column is added to a table, the column is initially NULL unless you specify the DEFAULT clause. When you specify a default value, the database immediately updates each row with the default value. Note that this can take some time, and that during the update, there is an exclusive DML lock on the table. For some types of tables (for example, tables without LOB columns), if you specify both a NOT NULL constraint and a default value, the database can optimize the column add operation and greatly reduce the amount of time that the table is locked for DML.
You can add a column with a NOT NULL constraint only if the table does not contain any rows, or you specify a default value.
意思就是如果添加的新列,没有指定not null,它默认的是null,这样当你指定了default值时,每一条记录
的默认值都会被更改,会占用大量的DML锁,所以效率很低
如果该列not null,且有默认值,在11g中不在需要存储这个默认在所有的记录中,该默认值单独保存在数据字典里,在使用时,在从数据字典中调用,从而减少了DDL操作的时间,也减少了空间的使用。
收起