Oracle 23ai新特性：增加表的最大列数（宽表）

一、引言

在数据库应用中，有时会遇到需要在表中定义大量列的情况。Oracle 23ai 引入了MAX_COLUMNS初始化参数，使得表中最多可容纳 4096 列，这种包含大量列的表被称为宽表。本文将详细介绍该功能相关的问题、解决方案以及使用时的注意事项。

二、问题描述

2.1 默认列数限制

Oracle 数据库默认情况下，一张表允许的最大列数为 1000。通过以下代码可以创建一个包含 1000 列（列名为 “COLn”，n 从 1 到 1000）的表T1：

conn sys/oracle@pdb1 as sysdba

declare
  l_col_count number := 1000;
  l_str       clob;
begin
  execute immediate 'drop table if exists t1 purge';

  l_str := 'create table t1 (';
  for i in 1.. l_col_count loop
    l_str := l_str || 'col' || to_char(i) || ' number, ';
  end loop;
  l_str := substr(l_str, 1, length(l_str)-2);
  l_str := l_str || ')';

  execute immediate l_str;
end;
/

desc t1

2.2 超出默认列数的错误

当尝试创建一个包含 1001 列的表时，会出现错误：

declare
  l_col_count number := 1001;
  l_str       clob;
begin
  execute immediate 'drop table if exists t1 purge';

  l_str := 'create table t1 (';
  for i in 1.. l_col_count loop
    l_str := l_str || 'col' || to_char(i) || ' number, ';
  end loop;
  l_str := substr(l_str, 1, length(l_str)-2);
  l_str := l_str || ')';

  execute immediate l_str;
end;
/

declare
*
ERROR at line 1:
ORA-01792: maximum number of columns in a table or view is 1000
ORA-06512: at line 14

三、问题解决

3.1 查看当前参数值

MAX_COLUMNS初始化参数控制着表的最大列数，其默认值为 “STANDARD”。可以通过以下方式查看：

conn sys/oracle@pdb1 as sysdba

show parameters max_columns

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_columns                          string      STANDARD
SQL> 

3.2 查看官方解释

使用 MAX_COLUMNS 启用或禁用数据库的宽表。

Property	Description
Parameter type	String
Syntax	MAX_COLUMNS = { STANDARD | EXTENDED }
Default value	STANDARD
Modifiable	No
Modifiable in a PDB	Yes
Basic	No
Oracle RAC	Multiple instances must use the same value.

说明：  

• 要启用宽表，请将此参数设置为 EXTENDED。通过此设置，数据库表或视图中允许的最大列数为 4096。  

      COMPATIBLE 初始化参数必须设置为 23.0.0.0 或更高才能设置 MAX_COLUMNS = 

      EXTENDED。  

• 要禁用宽表，请将此参数设置为 STANDARD。通过此设置，数据库表或视图中允许的最大列数为 1000。这与 Oracle Database 23ai 之前版本中的行为相匹配。

• PDB级别可以修改

可以随时将 MAX_COLUMNS 的值从 STANDARD 更改为 EXTENDED。但是，仅当数据库中的所有表和视图包含 1000 或更少的列时，才可以将 MAX_COLUMNS 的值从 EXTENDED 更改为 STANDARD。

注意：此参数从 Oracle Database 23ai 开始可用。Oracle Database 23ai 客户端（例如 SQL*Plus、OCI、JDBC-OCI、非托管 ODP.NET 和开源驱动程序）支持增加的列限制 4096。较旧的客户端版本（Oracle Database 23ai 之前的版本）不支持增加的列限制，并且无法访问表或视图中超过 1000 列。

3.3 设置参数值为EXTENDED

要增加表的最大列数，需要将MAX_COLUMNS值设置为 EXTENDED，并重启 PDB（可插拔数据库）。具体操作如下：

conn sys/oracle@pdb1 as sysdba

alter system set max_columns=EXTENDED scope=spfile;
shutdown immediate;
startup;

3.4 创建宽表

设置完成后，就可以创建包含最多 4096 列的表了。例如：

conn sys/oracle@pdb1 as sysdba


declare
  l_col_count number := 4096;
  l_str       clob;
begin
  execute immediate 'drop table if exists t1 purge';

  l_str := 'create table t1 (';
  for i in 1.. l_col_count loop
    l_str := l_str || 'col' || to_char(i) || ' number, ';
  end loop;
  l_str := substr(l_str, 1, length(l_str)-2);
  l_str := l_str || ')';

  execute immediate l_str;
end;
/

desc t1

3.5 参数值的限制与恢复

（一）限制

如果已经存在一个或多个列数超过 1000 的表，就不能将MAX_COLUMNS参数恢复为 “STANDARD”。例如：

conn sys/oracle@pdb1 as sysdba

alter system set max_columns=STANDARD scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-60471: max_columns can not be set to STANDARD as there are one or more
objects with more than 1000 columns

（二）恢复方法

只有在删除这些宽表后，才能将参数值重置为 “STANDARD”。例如：

conn sys/oracle@pdb1 as sysdba

drop table if exists t1 purge;

alter system set max_columns=STANDARD scope=spfile;
shutdown immediate;
startup;

四、注意事项

（一）适用场景

虽然大多数情况下 1000 列已经足够，但在某些特定的业务场景中，更多的列数可能会更适用。

（二）参数设置级别

MAX_COLUMNS参数只能在系统级别设置，不能在会话级别设置，但可以限制在特定的 PDB 中。

（三）RAC 集群要求

在 RAC（Real Application Clusters）集群中，所有实例的MAX_COLUMNS参数必须相同。

（四）数据存储影响

大量的列可能会导致行链接（即使是在干净插入时），并且根据行的生命周期，还可能增加行迁移。

（五）参数生效方式

“scope=memory” 选项已被禁止，必须重启数据库以使每个子系统都能一致地看到新设置。

（六）客户端兼容性

必须使用兼容的客户端（23ai 或更高版本）才能使用此功能。

（七）虚拟列计算

4096 列的限制包括虚拟列。

（八）与 HCC 压缩的关系

当使用宽表与 HCC（Hybrid Columnar Compression）时，必须使用 HCC Archive Low 压缩，而不是默认的 Query High 压缩。

五、总结

Oracle 23ai 的MAX_COLUMNS参数为需要创建宽表的场景提供了支持，但在使用过程中需要注意上述提到的各种限制和注意事项，以确保数据库的正常运行和数据的有效管理。