基於函式index的一點簡單測試！

基於函式index的一點簡單測試！[@more@]

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>