Oracle 11g cannot create function indexes with duplicate Columns

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!