[20180212]函数索引问题.txt
--//11g下,如果函数索引,字段出现重复,出现ORA-54015: Duplicate column expression was specified.
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table empx as select * from emp;
SCOTT@book> create index if_empx_x on empx(upper(ename),Upper(ename));
create index if_empx_x on empx(upper(ename),Upper(ename))
*
ERROR at line 1:
ORA-54015: Duplicate column expression was specified
SCOTT@book> create index ix_empx_x on empx(ename,ename);
create index ix_empx_x on empx(ename,ename)
*
ERROR at line 1:
ORA-00957: duplicate column name
$ oerr ora 54015
54015, 0000, "Duplicate column expression was specified"
// *Cause: Expression of the virtual column being added/created conflicts
// with an existing/previously specified functional index expression
// or virtual column expression
// *Action: Change the expression of the virtual column os there are no
// duplicate expressions
--//10g,ok:
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
create table empx as select * from emp;
SCOTT@test> create index if_empx_x on empx(upper(ename),Upper(ename));
Index created.
SCOTT@test> create index ix_empx_x on empx(ename,ename);
create index ix_empx_x on empx(ename,ename)
*
ERROR at line 1:
ORA-00957: duplicate column name
--//这样错误很少见,至少说明11g更加进步.
