[20241006]索引建立与虚拟列.txt

[20241006]索引建立与虚拟列.txt --//链接https://jonathanlewis.wordpress.com/2024/09/16/index-puzzle/给出一个例子，如果建立函数索引会建立隐含列，但是尝试多次建立索引时 --//会重复建立隐含列，重复到一定程度会出现ORA-01792错误。  $ 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. --//自己重复测试看看： 1.环境： SCOTT@book01p> @ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.测试： drop table t1;   create table t1(         n1 number(6,0),         n2 number(6,0) as (n1 + 1) virtual,         n3 number(6,0) ) /   prompt  ============================= prompt  An index that causes problems prompt  =============================   create index t1_i1 on t1(n1, nvl(n3,0), n2);   column column_name format a32 column data_default format a32   select         column_id, column_name,         hidden_column, virtual_column, user_generated, internal_column_id,         data_default from         user_tab_cols where         table_name = 'T1' order by         internal_column_id / SCOTT@book01p> column DATA_DEFAULT format a30 SCOTT@book01p> / 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) As you can see n2 is flagged as virtual (but not hidden) generated as "N1" + 1; and there's a fourth, system-generated, hidden, virtual column generated as NVL("N3",0). 正如你所看到的，n2被标记为虚拟的(但不是隐藏的)生成为"N1"+1；还有第四个由系统生成的、隐藏的虚拟列生成为NVL("N3"，0)。 As ever, things are more likely to break when you mix features. Here are a few more lines of code to run after creating the index and reporting the columns: 和以往一样，当你混合特性时，事情更有可能崩溃。以下是在创建索引和报告这些列之后要运行的另外几行代码： drop index t1_i1; create index t1_i1 on t1(n1, nvl(n3,0), n2);   drop index t1_i1; create index t1_i1 on t1(n1, nvl(n3,0), n2);   drop index t1_i1; create index t1_i1 on t1(n1, nvl(n3,0), n2);   drop index t1_i1; create index t1_i1 on t1(n1, nvl(n3,0), n2);   drop index t1_i1; create index t1_i1 on t1(n1, nvl(n3,0), n2); SCOTT@book01p> / 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)           SYS_NC00006$ YES YES NO                   6 NVL("N3",0)           SYS_NC00007$ YES YES NO                   7 NVL("N3",0)           SYS_NC00008$ YES YES NO                   8 NVL("N3",0)           SYS_NC00009$ YES YES NO                   9 NVL("N3",0) 9 rows selected. Oracle "forgets" to drop the system-generated virtual column, and generates a new virtual column every time the index is recreated. Since the columns are hidden columns you won't notice that something has gone wrong if you query the (more commonly used) view user_tab_columns – so there may well be a number of sites which have tables with huge numbers of hidden virtual columns, all quietly working their way towards unexpected ORA-01792 errors. Oracle"忘记"删除系统生成的虚拟列，并在每次重新创建索引时生成一个新的虚拟列。因为列隐藏列你不会注意到错了，如果你查询(更 常用)视图user_tab_columns——所以很可能有许多网站表与大量隐藏的虚拟列，所有悄悄地朝着意想不到的ORA-01792错误。 --//作者给出解决方案： In my case I found the following steps were sufficient to clear up the excess occurrences: 在我的案例中，我发现以下步骤足以清除多余的事件： drop the problem index (n1, nvl(n3,0), n2) create an index on just the problem expression (nvl(n3,0)) drop that index (which got rid of all the copies of the generated virtual columns) recreate the problem index. 删除问题索引(n1、nvl(n3、0)、n2) 仅对问题表达式(nvl(n3、0))创建一个索引 删除该索引(它将删除所生成的虚拟列的所有副本) 重新创建问题索引。 SCOTT@book01p> drop index t1_i1; Index dropped. SCOTT@book01p> create index t1_i3 on t1(nvl(n3,0)); Index created. SCOTT@book01p> drop index t1_i3; Index dropped. SCOTT@book01p> create index t1_i1 on t1(n1, nvl(n3,0), n2); Index created. SCOTT@book01p> / 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) --//不过只要重复上面的删除建立过程，问题还是会再现： SCOTT@book01p> drop index t1_i1; Index dropped. SCOTT@book01p> create index t1_i1 on t1(n1, nvl(n3,0), n2); Index created. SCOTT@book01p> / 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) --//你可以尝试如果建立索引字段顺序如下，该问题不存在。 SCOTT@book01p> create index t1_i1 on t1(n1, n2,nvl(n3,0)); Index created. SCOTT@book01p> drop index t1_i1; Index dropped. SCOTT@book01p> create index t1_i1 on t1(n1, n2,nvl(n3,0)); Index created. SCOTT@book01p> / 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) In more complicated cases (e.g. several indexes following this pattern, multiple indexes that include the same expression etc.) you may have to develop a more sophisticated approach. 在更复杂的情况下(例如，遵循这种模式的几个索引，包含相同表达式的多个索引等)。你可能需要开发一种更复杂的方法。