After migrating the data of a test system from Oracle 10 Gb to 11 GB, I heard the developers say that they failed to execute an SQL statement to create an index, but it can be executed normally in 10 Gb. This index is a function index with duplicate columns. It may be incorrect and this index is not useful. Therefore, you have never paid attention to this index. I felt quite interesting, so I tried it myself:
1. First, let's look at the 11g situation:
[Oracle @ instsvr1 ~] $ SQL
SQL * Plus: Release11.2.0.2.0Production on Fri Mar 16 11:29:33 2012
Copyright (c) 1982,201 0, Oracle. All rights reserved.
Connected:
Oracle Database 11g Enterprise Edition Release11.2.0.2.0-64bitProduction
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
And Real Application Testing options
Srcbdb @ SYS> conn vip
Enter password:
Connected.
Non-function indexes cannot be created if duplicate columns exist:
Srcbdb @ VIP>Create index lzb on T_SYS_PARAM (param_id, PARAM_ID );
Create index lzb on T_SYS_PARAM (param_id, PARAM_ID)
*
ERROR at line 1:
ORA-00957: duplicate column name
In 11g, even function indexes, duplicate columns are not allowed to be created:
Srcbdb @ VIP>Create index lzb on t_sys_param (nvl (PARAM_NAME, '*'), nvl (PARAM_NAME ,'*'));
Create index lzb on t_sys_param (nvl (PARAM_NAME, '*'), nvl (PARAM_NAME ,'*'))
*
ERROR at line 1:
ORA-54015: Duplicate column expression was specified
2. Let's look at the 10 Gb situation.
[Oracle @ dctest1 ~] $ SQL
SQL * Plus: Release 10.2.0.4.0-Production on Mon Mar 19 19:32:20 2012
Copyright (c) 1982,200 7, Oracle. All Rights Reserved.
Connected:
Oracle Database 10g Enterprise Edition Release10.2.0.4.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Dgut @ SYS> conn vip
Enter password:
Connected.
In 10 Gb, non-function indexes cannot be created if duplicate columns exist:
Dgut @ VIP>Create index lzb on T_SYS_PARAM (param_id, PARAM_ID );
Create index lzb on T_SYS_PARAM (param_id, PARAM_ID)
*
ERROR at line 1:
ORA-00957: duplicate column name
Finally, let's look at the Magic scene. In 10 Gb, if it is a function index, even if it has duplicate columns, the index can still be created successfully:
Dgut @ VIP>Create index lzb on t_sys_param (nvl (PARAM_NAME, '*'), nvl (PARAM_NAME ,'*'));
Index created.
Summary: Although creating a function index with duplicate columns is meaningless or even worse, the difference between the use of 11g and 10g is quite interesting and recorded. Only 11g is more rigorous, haha!