基于函数的索引

最新推荐文章于 2023-05-15 17:31:38 发布

cmoaai42662

最新推荐文章于 2023-05-15 17:31:38 发布

阅读量486

点赞数

基于函数的索引，一旦函数变更，需要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';

SQL> alter function ix compile;

函数已更改。

SQL> select INDEX_NAME,INDEX_TYPE,STATUS,funcidx_status from user_indexes where index_name = 'X1';

SQL> set autotrace on
SQL> select * from x where ix(y)='xxxxx123'; --------------变更函数之后，仍能检索出错误数据

X Y
---------- ------------------------------

123 xxxxx123

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';

SQL> set autotrace off
SQL> alter index x1 rebuild; --------------------变更函数之后，rebuild索引

索引已更改。

SQL> set autotrace on
SQL> select * from x where 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