莫名其妙的错误

近日在创建索引时突然报出 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$。其中N2是表定义的时候创建的，SY{S}_{N}C00004$ 是由函数索引系统自动创建出来的。一切看起来没有什么异常！

select column_id,
       column_name,
       hidden_column,
       virtual_column,
       user_generated,
       internal_column_id,
       data_default
  from user_tab_cols
 where 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                  1
         2 N2                             NO  YES YES                  2 "N1"+1
         3 N3                             NO  NO  YES                  3
           SYS_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                  1
         2 N2                             NO  YES YES                  2 "N1"+1
         3 N3                             NO  NO  YES                  3
           SYS_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                  1
         2 N2                             NO  YES YES                  2 "N1"+1
         3 N3                             NO  NO  YES                  3
           SYS_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                  1
         2 N2                             NO  YES YES                  2 "N1"+1
         3 N3                             NO  NO  YES                  3
           SYS_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大力支持下不少的数据库产品获得了很好的发展机会，虽然在上线初期出现这样或者那样的问题，但随着用户数量和使用场景的增多，产品也在这个过程中快速完善和发展起来，从可用到好用、从追赶到看齐，相信我们的数据库产业也会有崛起的一天。