使用Oracle函数索引 提高查询效率
dingjun123@ORADB> CREATE OR REPLACE FUNCTION func_tt(x IN VARCHAR2)
? 2 ?RETURN VARCHAR2 DETERMINISTIC AS
? 3 ?BEGIN
? 4 ? ? ?DBMS_APPLICATION_INFO.set_client_info(USERENV('client_info')+1 );
? 5 ? ???RETURN 'a' || x;
? 6 ?END;
? 7 ?/
Function created.
--查询不对，函数应该运算结果'o1'应该没有行，但是因为索引没有被rebuild
dingjun123@ORADB> SELECT * FROM tt WHERE func_tt(NAME) = 'o1';
NAME
----------
1
1 row selected.
--强制全表扫描，正确
dingjun123@ORADB> SELECT/*+full(tt)*/ * FROM tt WHERE func_tt(NAME) = 'o1';
no rows selected
--rebuild索引后也正确
dingjun123@ORADB> alter index idx_tt rebuild;
Index altered.
dingjun123@ORADB> set autotrace traceonly
dingjun123@ORADB> SELECT * FROM tt WHERE func_tt(NAME) = 'o1';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 6977672
--------------------------------------------------------------------------------------
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
--------------------------------------------------------------------------------------
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? ?10 | 20090 | ? ? 2 ? (0)| 00:00:01 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| TT ? ? | ? ?10 | 20090 | ? ? 2 ? (0)| 00:00:01 |
|* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX_TT | ? ? 4 | ? ? ? | ? ? 1 ? (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
? ?2 - access("DINGJUN123"."FUNC_TT"("NAME")='o1')
? ? 在不得不使用函数索引来提高效率的时候，别忘记了，随时准备维护函数索引，而且别弄出奇奇怪怪的函数索引，导致乱七八糟的问题，那样就不好了！
?
