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;
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。