Oracle索引梳理系列（六）- Oracle索引種類之函式索引

風靈使發表於2019-04-06
Oracle索引函式

函式索引

1.1 概述

在實際應用中，當條件列使用函式運算進行資料匹配時，即使該列建立了索引，索引也不會被使用。

如下示例，其中在owner列上建立一個普通b-tree索引,觀 察兩種查詢方式（不使用UPPER函式及使用UPPER函式）的執行計劃的區別。

--檢視錶上的資料分佈情況，可以確定，對於索引列`owner`，針對`scott`以及`bi`的普通查詢，一定會使用索引。
Yumiko@Sunny >select owner,count(*) from test01 group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
SCOTT                                  11
BI                                      8
SYS                                 22909


--為owner列建立普通b-tree索引
Yumiko@Sunny >create index btree_owner on test01(owner);
Index created.



--驗證建立的索引
Yumiko@Sunny >select a.INDEX_NAME INDEX_NAME,b.INDEX_TYPE INDEX_TYPE,a.TABLE_NAME TABLE_NAME,COLUMN_NAME,STATUS
  2  from user_ind_columns a,user_indexes b
  3  where a.INDEX_NAME=b.INDEX_NAME and a.table_name='TEST01';

INDEX_NAME      INDEX_TYPE      TABLE_NAME      COLUMN_NAME          STATUS
--------------- --------------- --------------- -------------------- --------
BTREE_OWNER     NORMAL          TEST01          OWNER                VALID



--利用索引列，針對列值為BI，進行普通查詢
--與預想一樣，這裡用到了索引掃描
Yumiko@Sunny >select * from test01 where owner='BI';
8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 725909888
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    92 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST01      |     1 |    92 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | BTREE_OWNER |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='BI')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
         16  physical reads
          0  redo size
       2010  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed



--清空buffer_cache緩衝區，避免影響後續操作對於物理讀的觀察。
Yumiko@Sunny >alter system flush buffer_cache;
System altered.



--使用UPPER函式進行條件過濾，並觀察執行計劃
--通過執行計劃，可以明顯看出，未使用索引掃描，進而導致大量的物理讀操作。
Yumiko@Sunny >select * from test01 where UPPER(owner)='BI';
8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 262542483
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   229 | 21068 |   158   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST01 |   229 | 21068 |   158   (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("OWNER")='BI')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        697  consistent gets
        692  physical reads
          0  redo size
       1583  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

通過上面的示例可以看到，即使條件列建立了索引，當索引列上使用函式進行條件匹配，執行計劃將不會選擇索引掃描。

1.2 函式索引介紹

為了避免由於在條件匹配時引入函式，導致執行計劃不再使用索引，oracle提供了基於函式的索引，進而解決上述問題，提高訪問效率。

需要注意的是：
在使用函式索引時，SQL語句中的條件表示式必須與函式索引的表示式完全一致，空格、關鍵字大小寫的可以忽略。如果不完全一致，則無法利用函式索引。

1.3 函式索引示例

緊接上面的例子，這裡針對上面示例中，條件出現的函式運算UPPER(owner)建立函式索引。

--在索引列上建立函式索引
Yumiko@Sunny >create index func_owner on test01(UPPER(owner));
Index created.


--檢視並驗證建立的函式索引
--需要注意的，由於此索引是基於函式建立的，因此columns一列無法顯示真正的列名，可以通過user_ind_expressions檢視檢視
Yumiko@Sunny >select a.INDEX_NAME INDEX_NAME,b.INDEX_TYPE INDEX_TYPE,a.TABLE_NAME TABLE_NAME,COLUMN_NAME,STATUS
  2  from user_ind_columns a,user_indexes b
  3  where a.INDEX_NAME=b.INDEX_NAME and a.table_name='TEST01';

INDEX_NAME      INDEX_TYPE                TABLE_NAME      COLUMN_NAME          STATUS
--------------- ------------------------- --------------- -------------------- --------
FUNC_OWNER      FUNCTION-BASED NORMAL     TEST01          SYS_NC00014$         VALID
BTREE_OWNER     NORMAL                    TEST01          OWNER                VALID



Yumiko@Sunny >select * from user_ind_expressions where INDEX_NAME='FUNC_OWNER';

INDEX_NAME      TABLE_NAME      COLUMN_EXPRESSION              COLUMN_POSITION
--------------- --------------- ------------------------------ ---------------
FUNC_OWNER      TEST01          UPPER("OWNER")                               1

--開啟會話追蹤
Yumiko@Sunny >set autotrace trace;

--清空buffer_cache緩衝區
Yumiko@Sunny >alter system flush buffer_cache;
System altered.


--再次使用UPPER函式進行條件查詢，此時執行計劃使用索引掃描，進而物理讀明顯降低。
Yumiko@Sunny >select * from test01 where upper(owner)='BI';
8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 939299437
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |   229 | 21068 |    19   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST01     |   229 | 21068 |    19   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FUNC_OWNER |    92 |       |    16   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(UPPER("OWNER")='BI')

Statistics
----------------------------------------------------------
         32  recursive calls
          0  db block gets
          8  consistent gets
         19  physical reads
          0  redo size
       1583  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

通過上面的示例可以看到，由於建立了函式索引，執行計劃重新選擇了索引掃描，物理讀（physical reads）明顯降低。

1.4 常用的oracle索引檢視

較為重要的oracle索引檢視如下：

dba_indexes
user_indexes
dba_ind_columns
user_indexes
dba_expressions
user_expressions

其中：
dba_indexes與user_indexes檢視，主要涵蓋了索引的引數、狀態以及關聯的表資訊，但不包含具體的列資訊。
dba_ind_columns與user_ind_columns檢視，主要涉及具體的索引列的資訊。
dba_expressions與user_expressions檢視，主要針對函式索引，可以檢視具體的函式資訊。

oracle函式索引

oracle中建立函式索引即是 你用到了什麼函式就建什麼函式索引，比如substr

select * from table where 1=1 and substr(field,0,2) in ('01')

建立索引的語句就是

create index   indexname on table(substr(fileld,0,2)) online nologging;

相關文章