Oracle Database 23c Free - Wide Tables

Oracle Database 23c Free – Developer Release is the first release of the next-generation Oracle Database, allowing developers a head-start on building applications with innovative 23c features that simplify development of modern data-driven apps. The entire  feature set of Oracle Database 23c is planned to be generally available within the next 12 months.

Oracle Database 23c Free introduces the ability to have wide tables. Tables and views can now have up to 4096 columns by setting the new initialization parameter MAX_COLUMNS to EXTENDED. Previously the limit was 1000 columns.

No application code has to change to take advantage of wide tables and there is no change in syntax for commands like CREATE TABLE and ALTER TABLE. However, one can imagine that actually creating a table with 4000 columns could result in some very long character strings and quite a bit of typing. The great thing about Oracle Database is that there are tools to make this easier.

I have posted an example using PL/SQL procedures to make the creation, insert and query of a very wide table with 4000 columns. The following shows an example of querying 10 columns of a 4000 column table:

SQL> select c1915,c940,c238,c2424,c862,c578,c2797,c2090,c3189,c3168 from WIDE_COL_TAB;

     C1915       C940       C238      C2424       C862       C578      C2797      C2090      C3189      C3168
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
      1916        941        239       2425        863        579       2798       2091       3190       3169
      1915        940        238       2424        862        578       2797       2090       3189       3168
      1919        944        242       2428        866        582       2801       2094       3193       3172
      1917        942        240       2426        864        580       2799       2092       3191       3170
      1918        943        241       2427        865        581       2800       2093       3192       3171
      1922        947        245       2431        869        585       2804       2097       3196       3175
      1921        946        244       2430        868        584       2803       2096       3195       3174
      1920        945        243       2429        867        583       2802       2095       3194       3173
      1923        948        246       2432        870        586       2805       2098       3197       3176
      1924        949        247       2433        871        587       2806       2099       3198       3177

10 rows selected.

SQL>

Clearly not everyone is going to need very wide tables, but there are applications where having the ability to have tables wider than 1000 columns can make development of those applications simpler.

Note: Updates based on some comments on the original post

1) Currently you need to be using a 23c client to access tables that have more than 1000 columns. See MOS Note 2947033.1, 23c New Feature - Increased RDBMS Table/View Column Limit to 4096

2) Several comments about why you might need more than 1000 columns. Two of the biggest reasons are when migrating from legacy applications to Oracle with tables that have more than 1000 columns, and applications that want to denormalize into wider tables to simplify or avoid joins.