SQL> create table tt as select *from t;

表已创建。

SQL> select *from tt;

ID NAME
---------- ----------
1 abc
2 bcd
3 cde
123 test
321 testabc

SQL> insert into tt values(null,null);

已创建 1 行。

SQL> insert into tt values(null,null);

已创建 1 行。

SQL> commit;

提交完成。

SQL> create index idx_tt_id on tt(id desc);

索引已创建。
--================================
Ascending unique indexes allow multiple NULL values. However, in descending unique indexes, multiple NULL values are treated as duplicate values and therefore are not permitted.
--================================
SQL> create unique index idx_tt_name on tt(name desc);
create unique index idx_tt_name on tt(name desc)
*
第 1 行出现错误:
ORA-01452: 无法 CREATE UNIQUE INDEX; 找到重复的关键字
SQL> create or replace function fun_test(p_a varchar2)
2 return varchar2
3 is
4 begin
5 return upper(p_a);
6 end;
7 /

函数已创建。

SQL> DROP INDEX IDX_TT_NAME;

索引已删除。

SQL> create index idx_tt_name on tt(fun_test(name));
create index idx_tt_name on tt(fun_test(name))
*
第 1 行出现错误:
ORA-30553: 函数不能确定
SQL> create or replace function fun_test(p_a varchar2)
2 return varchar2 deterministic
3 is
4 begin
5 return upper(p_a);
6 end;
7 /

函数已创建。
--使用自定义函数创建函数index时需要加上关键字deterministic，否则无法创建。

SQL> create index idx_tt_name on tt(fun_test(name));

索引已创建。

SQL> set autotrace on
SQL> select * from tt where fun_test(name)='ABC';

ID NAME
---------- ----------
1 abc

执行计划
----------------------------------------------------------
Plan hash value: 3830807321

--------------------------------------------------------------------------------

-----------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

Time |

--------------------------------------------------------------------------------

-----------

| 0 | SELECT STATEMENT | | 1 | 7 | 1 (0)|

00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TT | 1 | 7 | 1 (0)|

00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_TT_NAME | 1 | | 1 (0)|

00:00:01 |

--------------------------------------------------------------------------------

-----------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("SYS"."FUN_TEST"("NAME")='ABC')

统计信息
----------------------------------------------------------
63 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
460 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SET AUTOTRACE OFF
SQL> select index_name,status from dba_indexes where index_name='IDX_TT_NAME';

INDEX_NAME STATUS
------------------------------ --------
IDX_TT_NAME VALID

SQL> drop function fun_test;

函数已删除。
--函数被drop之后，index的status不变
SQL> select index_name,status from dba_indexes where index_name='IDX_TT_NAME';

INDEX_NAME STATUS
------------------------------ --------
IDX_TT_NAME VALID
--optimizer在使用函数index时如果函数被drop此时会提示错误
SQL> set autotrace on
SQL> select * from tt where fun_test(name)='ABC';
select * from tt where fun_test(name)='ABC'
*
第 1 行出现错误:
ORA-00904: "FUN_TEST": 标识符无效

SQL> set autotrace off
SQL> select index_name,status from dba_indexes where index_name='IDX_TT_NAME';

INDEX_NAME STATUS
------------------------------ --------
IDX_TT_NAME VALID
--函数被drop之后FUNCIDX_STATUS会变成disable，正常是enable
SQL> select index_name,status,funcidx_status from dba_indexes where index_name='
IDX_TT_NAME';

INDEX_NAME STATUS FUNCIDX_
------------------------------ -------- --------
IDX_TT_NAME VALID DISABLED
SQL> create or replace function fun_test(p_a varchar2)
2 return varchar2 deterministic
3 is
4 begin
5 return upper(p_a);
6 end;
7 /

函数已创建。
--====================================
If the function on which the index is based becomes invalid or is dropped, then Oracle Database marks the index DISABLED. Queries on a DISABLED index fail if the optimizer chooses to use the index. DML operations on a DISABLED index fail unless the index is also marked UNUSABLE and the parameter SKIP_UNUSABLE_INDEXES is set to true. Please refer to ALTER SESSION for more information on this parameter.
--====================================
SQL> select index_name,status,funcidx_status from dba_indexes where index_name='
IDX_TT_NAME';

INDEX_NAME STATUS FUNCIDX_
------------------------------ -------- --------
IDX_TT_NAME VALID DISABLED

SQL> alter index idx_tt_name rebuild;

索引已更改。

SQL> select index_name,status,funcidx_status from dba_indexes where index_name='
IDX_TT_NAME';

INDEX_NAME STATUS FUNCIDX_
------------------------------ -------- --------
IDX_TT_NAME VALID ENABLED

SQL> drop function fun_test;

函数已删除。

SQL> select index_name,status,funcidx_status from dba_indexes where index_name='
IDX_TT_NAME';

INDEX_NAME STATUS FUNCIDX_
------------------------------ -------- --------
IDX_TT_NAME VALID DISABLED

SQL> insert into tt values(8,'b');
insert into tt values(8,'b')
*
第 1 行出现错误:
ORA-30554: 基于函数的索引SYS.IDX_TT_NAME被禁用

SQL> show parameter skip

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean TRUE
SQL> alter table tt move;

表已更改。

SQL> select index_name,status,funcidx_status from dba_indexes where index_name='
IDX_TT_NAME';

INDEX_NAME STATUS FUNCIDX_
------------------------------ -------- --------
IDX_TT_NAME UNUSABLE DISABLED

SQL> insert into tt values(8,'b');

已创建 1 行。

SQL> commit;

提交完成。

SQL>