增加列:
oltp:可以增加一列指定默认值并且为not null的的列,如果指定默认值,但是可为空是不支持oltp压缩方式的If a default value is specified for an added column, then the column must beNOT
NULL
. Added nullable columns with default values are not supported.basic:你不能给增加的列指定默认值
删除列:oltp:oltp 压缩方式可以支持列的删除列,但是为了避免长时间的解压缩和再压缩 可以设置 列的unusedDROP
COLUMN
is supported, but internally the database sets the columnUNUSED
to avoid long-running decompression andrecompression operations.
basic:不支持删除列 Dropping a column is not supported.basic方式不能指定默认值:
SQL> select table_name,blocks,compress_for From dba_tables where table_name='T3';
TABLE_NAME BLOCKS COMPRESS_FOR
------------------------------ ---------- ------------
T3 302 BASIC
SQL> ALTER TABLE T3 ADD X NUMBER;
表已更改。
SQL> ALTER TABLE T3 ADD X1 NUMBER DEFAULT 0;
ALTER TABLE T3 ADD X1 NUMBER DEFAULT 0
*
第 1 行出现错误:
ORA-39726: 不支持对压缩表执行添加/删除列操作
oltp方式下,新加的列为空,那么不支持oltp压缩方式SQL> select table_name,blocks,compress_for From dba_tables where table_name in ('T');TABLE_NAME BLOCKS COMPRESS_FOR------------------------------ ---------- ------------
T 762 OLTPSQL> alter table t add x number default 1;表已更改。SQL> insert into t (empno,ename,job,mgr,hiredate,sal,comm,deptno,x) values(7369,'smith','clerk',7902,sysdate,777,null,20,null);已创建 1 行。SQL> select *from t where ename='smith';EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO X---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ----------7369 smith clerk 7902 11-4月 -14 777 20SQL> update t1 set comm=null; 已更新196608行。 SQL> commit; 提交完成。SQL> analyze table t compute statistics;表已分析。SQL> select table_name,blocks,compress_for From dba_tables where table_name in ('T');TABLE_NAME BLOCKS COMPRESS_FOR------------------------------ ---------- ------------T 1392 OLTP我们可以看到t表在update 某一列是null后,全部解压缩,从上面我们可以看到update某列值为空,那么此行数据就会解压缩
实验目的,给test表增加一列空值,插入到t2表中,那么t2表中也会有一列是空值,测试插入oltp压缩表的数据有一列是空值,是否此行不适用oltp压缩SQL> select table_name,blocks,compress_for From dba_tables where table_name in ('T2');TABLE_NAME BLOCKS COMPRESS_FOR------------------------------ ---------- ------------T2 762 OLTPSQL> alter table test add x number;表已更改。SQL> truncate table t2;表被截断。SQL> alter table t2 add x number;表已更改。SQL> insert into t2 select *From test;已创建196608行。SQL> commit;提交完成。SQL> analyze table t2 compute statistics;表已分析。SQL> select table_name,blocks,compress_for From dba_tables where table_name in ('T2');TABLE_NAME BLOCKS COMPRESS_FOR------------------------------ ---------- ------------T2 496 OLTP
结论: 可以看到t2表是压缩,插入的数据中有一列是空值,不影响压缩效果,但是update某一列的为空值,那么此列就会解压缩
basic方式不支持删除列SQL> select table_name,blocks,compress_for From dba_tables where table_name in ('T3'); TABLE_NAME BLOCKS COMPRESS_FOR------------------------------ ---------- ------------T3 302 BASICSQL> alter table t3 drop column x;alter table t3 drop column x*第 1 行出现错误:ORA-39726: 不支持对压缩表执行添加/删除列操作
压缩表列的修改
最新推荐文章于 2021-04-05 20:35:30 发布