【Oracle 11g】利用基于函数的索引只对部分行建立索引

Myth丶恋晨 2022-07-16 00:43 163阅读 0赞

基于函数的索引除了对使用内置函数（如UPPER、LOWER等）的查询显然有帮助外、还可以用来有选择的只对表中的某些行建立索引。

## 前期准备 ##

从这里可以下载big\_table.sql文件。  
[http://pan.baidu.com/s/1nvk3Ged][http_pan.baidu.com_s_1nvk3Ged]  
如不方便下载，可以直接从这里copy.

create table big_table
    as
    select rownum id, a.*
      from all_objects a
     where 1=0
    /
    alter table big_table nologging;
    
    declare
        l_cnt number;
        l_rows number := &1;
    begin
        insert /*+ append */
        into big_table
        select rownum, a.*
          from all_objects a
         where rownum <= &1;
    
        l_cnt := sql%rowcount;
    
        commit;
    
        while (l_cnt < l_rows)
        loop
            insert /*+ APPEND */ into big_table
            select rownum+l_cnt, 
                   OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
                   OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
                   TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
              from big_table
             where rownum <= l_rows-l_cnt;
            l_cnt := l_cnt + sql%rowcount;
            commit;
        end loop;
    end;
    /
    alter table big_table add constraint
    big_table_pk primary key(id);
    
    exec dbms_stats.gather_table_stats( user, 'BIG_TABLE', estimate_percent=> 1);

进入文件所在的目录，执行如下命令，创建一个1000 000行的表。

F:\BaiduYunDownload>sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.1.0 Production on 星期日 9月 25 17:36:10 2016
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    连接到:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> @big_table.sql 1000000
    
    表已创建。
    
    
    表已更改。
    
    原值    3:     l_rows number := &1;
    新值    3:     l_rows number := 1000000;
    原值    9:       where rownum <= &1;
    新值    9:       where rownum <= 1000000;
    
    PL/SQL 过程已成功完成。
    
    
    表已更改。
    
    
    PL/SQL 过程已成功完成。

## 实验开始 ##

更新temporary列，在此将Y变成N，以及把N变为Y：

SQL> update big_table set temporary = decode(temporary,'N','Y','N');
    
    已更新1000000行。
    
    SQL> commit;
    
    提交完成。

检查Y与N的比例

SQL> select temporary , cnt,
      2  round((ratio_to_report(cnt) over())*100,2) rtr
      3  from(
      4  select temporary, count(*) cnt
      5  from big_table
      6  group by temporary
      7  )
      8  /
    
    T        CNT        RTR
    - ---------- ----------
    Y     997548      99.75
    N       2452        .25

如果对temporary使用传统索引，会发现索引有1 000 000个条目，占用差不多14MB的空间，其高度为3：

SQL> create index processed_flag_idx
      2  on big_table(temporary);
    
    索引已创建。
    
    SQL> analyze index processed_flag_idx
      2  validate structure;
    
    索引已分析
    
    SQL> select name, btree_space, lf_rows, height
      2  from index_stats;
    
    NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
    ------------------------------ ----------- ---------- ----------
    PROCESSED_FLAG_IDX                14536160    1000000          3

通过这个索引获取任何数据都会带来3个I/O才能到达子叶块。这个索引不仅很宽，还很高。要得到第一个未处理的记录，必须至少执行4个I/O（其中3个是对索引的I/O，另外一个是对表的I/O）。

### 怎么能让索引更小呢？而且更新期间开销更小呢？ ###

采用基于函数的索引，我们可以编写一个函数，如果不想对某个给定行家索引，则这个函数就返回NULL；而对想加索引的行则返回一个非NULL值。（B\*树索引对于完全为NULL的键没有对应的条目）  
例如，我们只对列值为N的记录感兴趣，所以只对这些记录加索引：

SQL> drop index processed_flag_idx;
    
    索引已删除。
    
    SQL> create index processed_flag_idx
      2  on big_table( case temporary when 'N' then 'N' end);
    
    索引已创建。
    
    SQL> analyze index processed_flag_idx
      2  validate structure;
    
    索引已分析
    
    SQL> select name, btree_space, lf_rows, height
      2  from index_stats;
    
    NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
    ------------------------------ ----------- ---------- ----------
    PROCESSED_FLAG_IDX                   48032       2452          2

这就有很大不同了，这个索引只有48K大小，而不是14MB。高度也有所降低，与先前那个更高的索引相比，使用这个索引能少执行一个I/O。

### 下面进行测试，通过使用函数索引进行查询。 ###

注意：函数索引，必须使用函数表达式，才能通过索引访问。如(case temporary when ‘N’ then ‘N’ end)=’N’.（中间的打印记录省略了）  
（关于autotrace，[参看官网][Link 1]）

SQL> set timing on;
    SQL> set autotrace on;
    SQL> select * from big_table where (case temporary when 'N' then 'N' end)='N'; 已用时间:  00: 00: 19.98
    
    执行计划 ----------------------------------------------------------
    Plan hash value: 64562289
    
    -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time | --------------------------------------------------------------------------------------------------
    
    |   0 | SELECT STATEMENT            |                    |  9991 |  1004K|   541   (0)| 00:00:07 |
    
    |   1 |  TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 9991 | 1004K| 541 (0)| 00:00:07 | |*  2 |   INDEX RANGE SCAN          | PROCESSED_FLAG_IDX |  2452 |       |     5   (0)| 00:00:01 |
    
    -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------
    
     2 - access(CASE "TEMPORARY" WHEN 'N' THEN 'N' END ='N')
    
    
    统计信息 ----------------------------------------------------------
     38 recursive calls
     0 db block gets
     842 consistent gets
     0 physical reads
     0 redo size
     154484 bytes sent via SQL*Net to client
     2208 bytes received via SQL*Net from client
     165 SQL*Net roundtrips to/from client
     0 sorts (memory)
     0 sorts (disk)
     2452 rows processed

可以看到，I/O总共842次。

### 如果没有使用函数索引访问呢？ ###

可以看到总I/O增加，共计15091 次。

SQL> select * from big_table where temporary='N'; 已用时间:  00: 00: 17.64
    
    执行计划 ----------------------------------------------------------
    Plan hash value: 3993303771
    
    ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |           |   499K|    49M|  4070   (1)| 00:00:49 |
    |* 1 | TABLE ACCESS FULL| BIG_TABLE | 499K| 49M| 4070 (1)| 00:00:49 | -------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id): ---------------------------------------------------
    
     1 - filter("TEMPORARY"='N')
    
    
    统计信息 ----------------------------------------------------------
     0 recursive calls
     0 db block gets
     15091 consistent gets
     0 physical reads
     0 redo size
     154484 bytes sent via SQL*Net to client
     2208 bytes received via SQL*Net from client
     165 SQL*Net roundtrips to/from client
     0 sorts (memory)
     0 sorts (disk)
     2452 rows processed

关于cost和执行时间，[asktom][]

--------------------

本文参考《Oracle\_Database\_9i10g11g编程艺术深入数据库体系结构》第2版，Thomas Kyte著，苏金国 王小振等译，422~424页

[http_pan.baidu.com_s_1nvk3Ged]: http://pan.baidu.com/s/1nvk3Ged
[Link 1]: https://docs.oracle.com/cd/B10500_01/server.920/a96533/autotrac.htm
[asktom]: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:313416745628