缩小oracle中的列

Question

假设我有一个具有以下定义的表，

create table dummy (col1 number(9) not null)

此 dummy.col1 中的所有值都是 7 位长。现在我想使用 alter 命令将该列的长度从 9 - 7 减少。 Oracle 给我错误，要修改的列必须为空以降低精度或比例。有道理。

我想问一下有什么办法可以减少列大小吗？

• 我无法删除该列中的值。
• 我无法将值从该列复制到另一列，因为它有数万亿数据。

Answer 1

列大小与数据的物理存储方式无关（它们是可变长度），

例如，如果存储在数字（38）中，数字（2）中的“23”将占用完全相同的空间，

这纯粹是对列中可以存储的最大数量，因此您可以在列上添加一个约束：

ALTER TABLE dummy ADD 
CONSTRAINT c1
CHECK (col1 < 9999999)
ENABLE
VALIDATE;

如果您希望它运行得更快一点，请将 VALIDATE 更改为 NOVALIDATE 显然这会不检查现有数据的有效性。

Answer 2

凯文的回答非常好。

唯一的其他方法是

重命名现有列，
使用旧名称和新大小创建一个新列，
发出更新语句来填充新字段（您说不能这样做）
然后删除重命名的列。

您确定无法在周末找到一些休息时间来执行这项任务吗？

Answer 3

解决方案 #1

我的下面的解决方案保留了原始的列顺序。
我发现这很重要，特别是如果存在预装 SQL 语句
那里（中间层，客户端层）指向您隐式的数据库
选择。

即

SELECT * 
FROM tableName
WHERE ...; 

INSERT INTO copyTableName(column1,column2,column3,...)  
   SELECT * 
   FROM tableName
   WHERE ...;

：

生成 DDL 为
1. 包含您要调整大小的列的表
2. 引用该表的所有关系约束、索引、检查约束、触发器。
3、引用本表主键的其他表的所有外键。

确保每个表引用对象 DDL 都是独立的，与
创建表 DDL。

您将得到类似的内容，

/* 1. The table containing the column you intend to resize */
CREATE TABLE tableName
(
  column1 TYPE(size) [DEFAULT value] [NOT] NULL,
  column2 TYPE(size) [DEFAULT value] [NOT] NULL,
  column3 TYPE(size) [DEFAULT value] [NOT] NULL,
  ...
)
TABLESPACE tsName
[OPTIONS];

/* 2. All the relationship constraints, indexes, check constraints, triggers that reference that table. */
CREATE INDEX indexName ON tableName
(column1)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;

CREATE INDEX compositeIndexName ON tableName
(column1,column2,...)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;

CREATE UNIQUE INDEX pkName ON tableName
(column2)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;

ALTER TABLE tableName ADD (
  CHECK (column4 IS NOT NULL));

ALTER TABLE tableName ADD (
  CONSTRAINT pkName
 PRIMARY KEY
 (column2)
    USING INDEX 
    TABLESPACE INDX);

ALTER TABLE tableName ADD (
  CONSTRAINT fkName
 FOREIGN KEY (column2) 
 REFERENCES otherTable (column2));

/* 3. All the foreign keys of other tables that reference the primary key of this table. */
ALTER TABLE otherTableName ADD (
  CONSTRAINT otherTableFkName
 FOREIGN KEY (otherTableColumn2) 
 REFERENCES tableName (column1));

仅复制 CREATE TABLE 语句，更改表名称，然后
减小要修改的列的大小：

CREATE TABLE tableName_YYYYMMDD
(
  column1 TYPE(size)         [DEFAULT value] [NOT] NULL,
  column2 TYPE(reducedSize)  [DEFAULT value] [NOT] NULL,
  column3 TYPE(size)         [DEFAULT value] [NOT] NULL,
  ...
)
TABLESPACE tsName
[OPTIONS];

将 tableName 中的数据插入 tableName_YYYYMMDD：

INSERT /* APPEND */ INTO tableName_YYYYMMDD(
  column1 ,
  column2 ,
  column3 ,
  ...     )
    SELECT
      column1 ,
      column2 ,
      column3 ,
      ...     
    FROM tableName;
COMMIT;

删除引用原始表的所有对象。
另外，删除引用 tableName 主键 pkName 的所有外键。
别担心，您已经保存了 DDL，因此您可以重新创建它们。
请注意，我在将数据复制出 tableName 后删除了索引。
我这样做是因为也许其中一个索引将在
位于 SELECT 之上，以便操作更快地完成。

DROP INDEX indexName                                        ;
DROP INDEX compositeIndexName                               ;
DROP UNIQUE INDEX pkName                                    ;
ALTER TABLE tableName DROP CONSTRAINT pkName                ;
ALTER TABLE tableName DROP CONSTRAINT fkName                ;
ALTER TABLE otherTableName DROP CONSTRAINT otherTableFkName ;

删除原始表。

DROP TABLE tableName;

重命名新表。

ALTER TABLE tableName_YYYYMMDD RENAME TO tableName;

从您之前保存的 DDL 语句重新创建所有引用对象。

/* 2. All the relationship constraints, indexes, check constraints, triggers that reference that table. */
CREATE INDEX indexName ON tableName
(column1)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;

CREATE INDEX compositeIndexName ON tableName
(column1,column2,...)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;

CREATE UNIQUE INDEX pkName ON tableName
(column2)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;

ALTER TABLE tableName ADD (
  CHECK (column4 IS NOT NULL));

ALTER TABLE tableName ADD (
  CONSTRAINT pkName
 PRIMARY KEY
 (column2)
    USING INDEX 
    TABLESPACE INDX);

ALTER TABLE tableName ADD (
  CONSTRAINT fkName
 FOREIGN KEY (column2) 
 REFERENCES otherTable (column2));

/* 3. All the foreign keys of other tables that reference the primary key of this table. */
ALTER TABLE otherTableName ADD (
  CONSTRAINT otherTableFkName
 FOREIGN KEY (otherTableColumn2) 
 REFERENCES tableName (column1));

解决方案 #2

保持列顺序，但不重建可能包含列 2 的非唯一使用的 PK 索引。

ALTER TABLE tableName ADD (column2Copy TYPE(reducedSize));

UPDATE      tableName SET column2Copy = column2;

ALTER TABLE tableName MODIFY (column2 TYPE(size) NULL);

ALTER TABLE tableName DROP CONSTRAINT pkName;

DROP INDEX  pkName;

UPDATE      tableName SET column2 = null;

ALTER TABLE tableName MODIFY (column2 TYPE(reducedSize));

UPDATE      tableName SET column2 = column2Copy;

ALTER TABLE tableName DROP COLUMN column2Copy;

CREATE UNIQUE INDEX pkName ON tableName
(column2)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;

ALTER TABLE tableName ADD (
  CONSTRAINT pkName
 PRIMARY KEY
 (column2)
    USING INDEX 
    TABLESPACE INDX);

COMMIT;