如何在压缩表中删除列? [英] How to drop columns in a compressed table?
本文介绍了如何在压缩表中删除列?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
压缩表后,无法再删除列:
After compressing a table, it's not possible to drop a column any more:
CREATE TABLE foo (p NUMBER, i NUMBER, j NUMBER) COMPRESS BASIC;
ALTER TABLE foo DROP COLUMN j;
ORA-39726: unsupported add/drop column operation on compressed tables
如果使用高级压缩,则是可能的:
It is possible if advance compression is used:
CREATE TABLE foo (p NUMBER, i NUMBER, j NUMBER) COMPRESS FOR OLTP;
ALTER TABLE foo DROP COLUMN j;
Table FOO altered.
但是,该列并没有真正被删除,只是被隐藏了:
However, the column is not really dropped, just hidden:
SELECT column_name, data_type, hidden_column
FROM user_tab_cols WHERE table_name = 'FOO';
COLUMN_NAME DATA_TYPE HIDDEN_COLUMN
P NUMBER NO
I NUMBER NO
SYS_C00002_18030204:09:26$ NUMBER YES
这在交换分区时导致错误:
This causes an error when partitions are exchanged:
CREATE TABLE par (p NUMBER, i NUMBER)
PARTITION BY LIST(p)(
PARTITION p1 VALUES(1),
PARTITION p2 VALUES(2)
);
ALTER TABLE par EXCHANGE PARTITION p1 WITH TABLE foo;
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
如何删除隐藏的列?我尝试过
How can the hidden column be removed? I tried
ALTER TABLE foo DROP UNUSED COLUMNS;
但这没有帮助:
SELECT column_name, data_type, hidden_column
FROM user_tab_cols WHERE table_name = 'BAR';
COLUMN_NAME DATA_TYPE HIDDEN_COLUMN
P NUMBER NO
I NUMBER NO
SYS_C00002_18030204:09:26$ NUMBER YES
推荐答案
Oracle支持文档1987500.1 如何在压缩表中删除列" 具有解决方案:首先需要对表进行解压缩,删除的列,然后可以再次压缩:
Oracle support document 1987500.1 "How to Drop Columns in Compressed Tables" had the solution: the table needs to be uncompressed first, the the columns removed, then it can be compressed again:
ALTER TABLE foo MOVE NOCOMPRESS;
ALTER TABLE foo DROP UNUSED COLUMNS;
ALTER TABLE foo MOVE COMPRESS FOR OLTP;
SELECT column_name, data_type, hidden_column
FROM user_tab_cols WHERE table_name = 'FOO';
COLUMN_NAME DATA_TYPE HIDDEN_COLUMN
P NUMBER NO
I NUMBER NO
ALTER TABLE par EXCHANGE PARTITION p1 WITH TABLE foo;
Table PAR altered.
如果表为空,则压缩/重新压缩很快.
N.B. The compression/recompression is fast if the table is empty.
这篇关于如何在压缩表中删除列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文