基于函数的索引
基于函数的索引，一旦函数变更，需要rebuild索引，否则索引信息还是以前的信息，查询的结果不正确。
测试过程如下：
SQL> create table x (x number,y varchar2(30));
表已创建。
SQL> insert into x
2 select rownum,'xxxxx'||rownum
3 from all_objects
4 where rownum<10000;
已创建9999行。
SQL> commit;
提交完成。
SQL> create or replace function ix(p varchar2) return varchar2 deterministic
2 IS
3 begin
4 return p;
5 end;
6 /
函数已创建。
SQL> create index x1 on x(ix(y));
索引已创建。
SQL> analyze table x compute statistics;
表已分析。
SQL> set autotrace on
SQL> select * from x where ix(y) = 'xxxxx123';
X Y
---------- ------------------------------
123 xxxxx123
执行计划
----------------------------------------------------------
Plan hash value: 1631723157
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| X | 1 | 12 | 2 (0)| 00:00:
01 |
|* 2 | INDEX RANGE SCAN | X1 | 1 | | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SCOTT"."IX"("Y")='xxxxx123')
统计信息
----------------------------------------------------------
40 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
462 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> select INDEX_NAME,INDEX_TYPE,STATUS,funcidx_status from user_indexes where index_name = 'X1';
INDEX_NAME INDEX_TYPE STATUS FUNCIDX_
------------------------------ --------------------------- -------- --------
X1 FUNCTION-BASED NORMAL VALID ENABLED
SQL> set autotrace off
SQL> create or replace function ix(p varchar2) return varchar2 deterministic
2 is
3 begin
4 return p||'abcde'; --------------------变更函数
5 end;
6 /
函数已创建。
SQL> select INDEX_NAME,INDEX_TYPE,STATUS,funcidx_status from user_indexes where index_name = 'X1';
INDEX_NAME INDEX_TYPE STATUS FUNCIDX_
------------------------------ --------------------------- -------- --------
X1 FUNCTION-BASED NORMAL VALID ENABLED
SQL> alter function ix compile;
函数已更改。
SQL> select INDEX_NAME,INDEX_TYPE,STATUS,funcidx_status from user_indexes where index_name = 'X1';
INDEX_NAME INDEX_TYPE STATUS FUNCIDX_
------------------------------ --------------------------- -------- --------
X1 FUNCTION-BASED NORMAL VALID ENABLED
SQL> set autotrace on
SQL> select * from x where ix(y)='xxxxx123'; --------------变更函数之后，仍能检索出错误数据
X Y
---------- ------------------------------
123 xxxxx123
执行计划
----------------------------------------------------------
Plan hash value: 1631723157
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| X | 1 | 12 | 2 (0)| 00:00:
01 |
|* 2 | INDEX RANGE SCAN | X1 | 1 | | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SCOTT"."IX"("Y")='xxxxx123')
统计信息
----------------------------------------------------------
40 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
462 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> select /*+ full(x) */ * from x
2 where ix(y) = 'xxxxx123'; -------------变更函数之后，不扫描索引，结果正确
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2941724873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 13 (47)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| X | 1 | 12 | 13 (47)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("IX"("Y")='xxxxx123')
统计信息
----------------------------------------------------------
56 recursive calls
0 db block gets
55 consistent gets
0 physical reads
0 redo size
318 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select INDEX_NAME,INDEX_TYPE,STATUS,funcidx_status from user_indexes where index_name = 'X1';
INDEX_NAME INDEX_TYPE STATUS FUNCIDX_
------------------------------ --------------------------- -------- --------
X1 FUNCTION-BASED NORMAL VALID ENABLED
SQL> set autotrace off
SQL> alter index x1 rebuild; --------------------变更函数之后，rebuild索引
索引已更改。
SQL> set autotrace on
SQL> select * from x where ix(y)='xxxxx123'; --------------------结果正确
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 1631723157
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| X | 1 | 12 | 2 (0)| 00:00:
01 |
|* 2 | INDEX RANGE SCAN | X1 | 1 | | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SCOTT"."IX"("Y")='xxxxx123')
统计信息
----------------------------------------------------------
54 recursive calls
0 db block gets
9 consistent gets
4 physical reads
0 redo size
318 bytes sent via SQL*Net to client
374 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 x where ix(y)='xxxxx123abcde'; --------------------结果正确
X Y
---------- ------------------------------
123 xxxxx123
执行计划
----------------------------------------------------------
Plan hash value: 1631723157
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| X | 1 | 12 | 2 (0)| 00:00:
01 |
|* 2 | INDEX RANGE SCAN | X1 | 1 | | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SCOTT"."IX"("Y")='xxxxx123abcde')
统计信息
----------------------------------------------------------
40 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
462 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> spool off
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10037372/viewspace-663339/ ，如需转载，请注明出处，否则将追究法律责任。
