莫名其妙的错误
近日在创建索引时突然报出 ORA-01792 错误，根据错误提示，显示表上的列数量超过了1000，但是显然这个表上并没有这么多的列。
[oracle@myora19c ~]$ oerr ora 01792
01792, 00000, "maximum number of columns in a table or view is 1000"
// *Cause: An attempt was made to create a table or view with more than 1000
// columns, or to add more columns to a table or view which pushes
// it over the maximum allowable limit of 1000. Note that unused
// columns in the table are counted toward the 1000 column limit.
// *Action: If the error is a result of a CREATE command, then reduce the
// number of columns in the command and resubmit. If the error is
// a result of an ALTER TABLE command, then there are two options:
// 1) If the table contained unused columns, remove them by executing
// ALTER TABLE DROP UNUSED COLUMNS before adding new columns;
// 2) Reduce the number of columns in the command and resubmit.
这个问题是在什么情况下发生的呢？为了模拟这个现象，我们简单创建了一个表，并在上面创建了索引。
注意：我们创建的测试表并不是普通的表，这个表带有虚拟列，并且创建的索引也是一个函数索引。
drop table test1;
create table test1(n1 number(6,0),n2 number(6,0) as (n1 + 1) virtual,n3 number(6,0)
);
create index test1_i1 on test1(n1, nvl(n3,0), n2);
通过以下的查询可以看到，这个时候表有有两个虚拟列：N2 和 SYS_NC00004 。其中 N 2 是表定义的时候创建的， S Y S N C 00004 。其中 N2 是表定义的时候创建的，SYS_NC00004 。其中N2是表定义的时候创建的，SYSNC00004 是由函数索引系统自动创建出来的。一切看起来没有什么异常！
select column_id,column_name,hidden_column,virtual_column,user_generated,internal_column_id,data_defaultfrom user_tab_colswhere table_name = 'TEST1'order by internal_column_id;COLUMN_ID COLUMN_NAME HID VIR USE INTERNAL_COLUMN_ID DATA_DEFAULT
---------- ------------------------------ --- --- --- ------------------ ------------------------------1 N1 NO NO YES 12 N2 NO YES YES 2 "N1"+13 N3 NO NO YES 3SYS_NC00004$ YES YES NO 4 NVL("N3",0)
可是当我们尝试删除索引后却发现，对应的虚拟列并没有同步删除，更为诡异的是，如果这时再去创建一个新的索引，又会多出一个虚拟列。
SQL> drop index test1_i1;
SQL> @col_info;COLUMN_ID COLUMN_NAME HID VIR USE INTERNAL_COLUMN_ID DATA_DEFAULT
---------- ------------------------------ --- --- --- ------------------ ------------------------------1 N1 NO NO YES 12 N2 NO YES YES 2 "N1"+13 N3 NO NO YES 3SYS_NC00004$ YES YES NO 4 NVL("N3",0)SQL> create index test1_i1 on test1(n1, nvl(n3,0), n2);
SQL> @col_info;COLUMN_ID COLUMN_NAME HID VIR USE INTERNAL_COLUMN_ID DATA_DEFAULT
---------- ------------------------------ --- --- --- ------------------ ------------------------------1 N1 NO NO YES 12 N2 NO YES YES 2 "N1"+13 N3 NO NO YES 3SYS_NC00004$ YES YES NO 4 NVL("N3",0)SYS_NC00005$ YES YES NO 5 NVL("N3",0)
无解的问题
这下问题就比较严重了，每创建和删除一次函数索引，这个表上就会多出一个虚拟列，重复这个过程直到表列数增长到1000，就会报出 ORA-01792 错误。
SQL> @col_info;COLUMN_ID COLUMN_NAME HID VIR USE INTERNAL_COLUMN_ID DATA_DEFAULT
---------- ------------------------------ --- --- --- ------------------ ------------------------------SYS_NC00994$ YES YES NO 994 NVL("N3",0)SYS_NC00995$ YES YES NO 995 NVL("N3",0)SYS_NC00996$ YES YES NO 996 NVL("N3",0)SYS_NC00997$ YES YES NO 997 NVL("N3",0)SYS_NC00998$ YES YES NO 998 NVL("N3",0)SYS_NC00999$ YES YES NO 999 NVL("N3",0)SYS_NC01000$ YES YES NO 1000 NVL("N3",0)1000 rows selected.SQL> create index test_i1 on test1(n1, nvl(n3,0), n2);
create index test_i1 on test1(n1, nvl(n3,0), n2)*
ERROR at line 1:
ORA-01792: maximum number of columns in a table or view is 1000
到底是Oracle设计上的疏漏，在删除索引时”忘了“同步删除由索引创建出来的虚拟列呢？还是有意为之有其更深层次的思考？带着这个疑问我翻阅了相关的文档。
首先，Oracle 的虚拟列是独立于表单独存在的，如果我们创建另一个索引同样包含 nvl(n3,0) 函数，此时系统不会再创建新的虚拟列，而是会复用之前已创建的。因此并不能简单的归结为谁创建的虚拟列就一定要由谁来删除，还涉及到其他对象的引用问题。
SQL> create index test1_i2 on test1(n2, nvl(n3,0), n1);
SQL> @col_info;COLUMN_ID COLUMN_NAME HID VIR USE INTERNAL_COLUMN_ID DATA_DEFAULT
---------- ------------------------------ --- --- --- ------------------ ------------------------------1 N1 NO NO YES 12 N2 NO YES YES 2 "N1"+13 N3 NO NO YES 3SYS_NC00004$ YES YES NO 4 NVL("N3",0)SYS_NC00005$ YES YES NO 5 NVL("N3",0)
其次，当所有的引用索引都删除之后对应的虚拟列仍然没有被删除，这肯定不是一个预期行为。更何况 Oracle 并没有提供方法来手工删除系统自动生成的列，那么这些残留的列该如何删除呢？
MOS 中关于 ORA-01792 报错的案例很多，这些案例中 Oracle 也明确创建函数索引时会同步创建出新的虚拟列，他们认为这个是一个预期的行为。也许 Oracle 在设计的时候考虑到引用的问题，但是没有考虑到用户可能会频繁的删除和重建，从而可能会使得表的列数量超过上限。
还好还有解决方案
最后给出大神 Jonathan Lewis 提供的 Workaround：
- 删除掉引发问题的索引，drop index test1_i2;
- 仅针对需要的列创建函数索引，create index test1_n3_i1 on test1(nvl(n3,0));
- 删除掉刚创建的索引，drop index test1_n3_i1;
- 再次重建想要创建的索引，create index test1_i2 on test1(n2, nvl(n3,0), n1);
写在最后
数据库是一个复杂的系统，设计的时候经常会考虑到其中一点而忽略了其他，实验室中的测试也难以覆盖所有的场景，这个时候需要有更多的用户使用，在使用不断中积累和完善。所以很多时候我们不是缺技术缺能力，而是缺少提问的人。这个问题中，Oracle 认为创建函数索引会连带创建虚拟列是一个预期行为，但如果有人追问，为什么函数索引删除了虚拟列没有删除呢？，我想这个问题肯定早就被暴露和修复了。
国产数据库的发展也是同理，在国家ZC大力支持下不少的数据库产品获得了很好的发展机会，虽然在上线初期出现这样或者那样的问题，但随着用户数量和使用场景的增多，产品也在这个过程中快速完善和发展起来，从可用到好用、从追赶到看齐，相信我们的数据库产业也会有崛起的一天。
