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.
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.
Andy Rivenes is a Senior Principal Product Manager at Oracle Corporation and is a Product Manager for Database In-Memory. Andy has been working with Oracle products since 1992 and spent a large part of his career managing Oracle databases for both custom and E-Business Suite applications. Andy now spends his time helping customers implement Database In-Memory and providing information about Database In-Memory through blogs, conferences, and customer visits.
Previous Post