function-index error exeplan!(函数索引需要重建)

接着上一篇实验
SQL> select * from testfun;
ID NAME
---------- ----------------------
1 anbob.com
2 anbob.com
3 weijar.com
SQL> create or replace function f_upp(p_name varchar2)
2  return varchar2 deterministic
3  is
4  begin
5    return upper(p_name);
6* end;
Function created.
SQL> create index idx_f_upp on testfun(f_upp(name));
Index created..
SQL> set autot on
SQL> select * from testfun where f_upp(name)='ANBOB.COM';
ID NAME
---------- ----------------------
1 anbob.com
2 anbob.com

Execution Plan
----------------------------------------------------------
Plan hash value: 3901563098
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    26 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTFUN   |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_F_UPP |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ZWZ"."F_UPP"("NAME")='ANBOB.COM')
Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
118  recursive calls
0  db block gets
21  consistent gets
0  physical reads
0  redo size
636  bytes sent via SQL*Net to client
492  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
2  rows processed
SQL> create or replace function f_upp(p_name varchar2)
2  return varchar2 deterministic
3  is
4  begin
5    return upper('www.'||p_name);
6  end;
7  /
Function created.
SQL> select * from testfun where f_upp(name)='WWW.ANBOB.COM';
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3901563098
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    26 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTFUN   |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_F_UPP |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ZWZ"."F_UPP"("NAME")='WWW.ANBOB.COM')
Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
64  recursive calls
0  db block gets
11  consistent gets
0  physical reads
0  redo size
381  bytes sent via SQL*Net to client
481  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
0  rows processed
SQL> select * from testfun where f_upp(name)='ANBOB.COM';
ID NAME
---------- ----------------------
1 anbob.com
2 anbob.com

Execution Plan
----------------------------------------------------------
Plan hash value: 3901563098
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    26 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTFUN   |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_F_UPP |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ZWZ"."F_UPP"("NAME")='ANBOB.COM')
Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
63  recursive calls
0  db block gets
14  consistent gets
0  physical reads
0  redo size
636  bytes sent via SQL*Net to client
492  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
2  rows processed
SQL> ALTER Index idx_f_upp rebuild;
Index altered.
SQL> select * from testfun where f_upp(name)='ANBOB.COM';
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3901563098
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    26 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTFUN   |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_F_UPP |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ZWZ"."F_UPP"("NAME")='ANBOB.COM')
Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
78  recursive calls
0  db block gets
14  consistent gets
0  physical reads
0  redo size
381  bytes sent via SQL*Net to client
481  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
0  rows processed
SQL> select * from testfun where f_upp(name)='WWW.ANBOB.COM';
ID NAME
---------- ----------------------
1 anbob.com
2 anbob.com

Execution Plan
----------------------------------------------------------
Plan hash value: 3901563098
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    26 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTFUN   |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_F_UPP |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ZWZ"."F_UPP"("NAME")='WWW.ANBOB.COM')
Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
63  recursive calls
0  db block gets
14  consistent gets
0  physical reads
0  redo size
636  bytes sent via SQL*Net to client
492  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
2  rows processed
The index can only be enabled if the signature of the function is same as  before (i.e when it was created). If the signature of the functions changes   then the index needs to be revalidated by using the rebuild option:
ALTER INDEX  REBUILD;