[20180212]函式索引問題.txt

[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更加進步.