Introduction
Before I explain why I needed the feature I didn't think I needed, lets discuss what Wide tables are and how to use them.
Wide Tables
In previous versions of Oracle tables and views were limited to 1000 columns. In Oracle 23c, you can increase this to 4096 if required.
To do this you need to change an initialization parameter. The initialization parameter is called MAX_COLUMNS and the Oracle documentation for this parameter can be found here
Setting the Parameter
sqlplus / as sysdba
SQL> alter session set container=MyPDB;
SQL> alter system set max_columns=EXTENDED scope=spfile;
SQL> shutdown immediate;
SQL> startup;
NOTE: This parameter requires a restart.
Restrictions
The following restrictions apply…
- You cannot reset the parameter to its default value (STANDARD) until all views or tables with columns greater than 1000 have been dropped. An ORA-60471 error is thrown when resetting this parameter if objects exits with more than 1000 columns.
- In a RAC environment all instances must have the same value.
- Having such a large number of columns will almost certainly cause row chaining
- You need to be using a 23c client to use this feature.
Use Case
As alluded to above, I wasn't sure I would ever use this feature. After all, 1000 columns is a lot of columns. Furthermore, when you start using this number of columns in a table row chaining is a high possibility, which in turn can cause some performance related issues. So my advice to developers when using so many columns is normally; Please don't.
However, in a case of "do as I say, not as I do", I did actually find a purpose for this feature, albiet rather unusual.
A company I work for uses a rather neat replication product. Its cheap, has a really good interface and easy to use. But it has an odd in-built limitation. When replicating tables it builds a "mapping" table, which maps columns in the replicated table. This mapping table will contain a column for each column in the table being replicated and a mapping column. This means, the maximum columns in a table being replicated is fixed at 50% of the Oracle maximum column limit. So prior to 23c, this was 500 columns. Now don't get me wrong, 500 columns is still a lot of columns. But is not totally unusual, especially in data warehouses. This particular company had several tables with more than 500 columns and as a result were excluding some of the columns from the replication stream. Now they don't have too. Well at least until they create a table with 2049 columns in it. Please no!!! :)
Published 15th August 2023
