oracle 基于函数的索引

最新推荐文章于 2024-08-28 12:22:16 发布

feier7501

最新推荐文章于 2024-08-28 12:22:16 发布

阅读量581

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle

本文链接：https://blog.youkuaiyun.com/feier7501/article/details/23618585

oracle 专栏收录该内容

103 篇文章

订阅专栏

本文通过实例演示了如何使用基于函数的索引提高特定类型查询的效率，对比了普通索引与函数索引在不同场景下的表现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建数据：

SQL> create table tb_function
  2  (
  3      id integer primary key,
  4      user_name varchar2(20) not null
  5  );

表已创建。

SQL> BEGIN
  2    FOR I IN 1..10000 LOOP
  3      INSERT INTO tb_function (id, user_name) VALUES (I, I || 'abc');
  4    END LOOP;
  5    COMMIT;
  6  END;
  7  /

PL/SQL 过程已成功完成。

查询，查看执行计划：

SQL> set autotrace traceonly
SQL> set linesize 120
SQL> select * from tb_function where id = 1000;


执行计划
----------------------------------------------------------
Plan hash value: 3943311915

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    25 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_FUNCTION |     1 |    25 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C005718 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1000)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        377  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)
          1  rows processed

SQL>

但是对于下面的SQL，就不能用到索引了：

SQL> select * from tb_function where to_char(id) = '1000';


执行计划
----------------------------------------------------------
Plan hash value: 3870335670

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |    25 |     9  (12)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TB_FUNCTION |     1 |    25 |     9  (12)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_CHAR("ID")='1000')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         64  consistent gets
          0  physical reads
          0  redo size
        469  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

上面是全表扫描，对此，可以采用基于函数的索引：

SQL> create index idx_function_fb_id on tb_function(to_char(id));

索引已创建。

SQL> select * from tb_function where to_char(id) = '1000';


执行计划
----------------------------------------------------------
Plan hash value: 3448699231

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    25 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_FUNCTION        |     1 |    25 |     7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_FUNCTION_FB_ID |    40 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(TO_CHAR("ID")='1000')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
         28  recursive calls
          0  db block gets
         38  consistent gets
          4  physical reads
          0  redo size
        469  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>