OceanBase 中的函数索引

概念描述

在OceanBase中不支持类似Oracle的降序索引(DESC)，但是支持某些函数索引。

测试验证

• 创建测试表和数据

drop table dbmt.tablea purge;
create table dbmt.tablea(id number, c varchar2(100), begdate date);
insert into dbmt.tablea select rownum,'test',sysdate from dual connect by rownum<200;
commit;
复制

在OceanBase中不支持类似Oracle的降序索引(DESC)

-- Server version: OceanBase 3.2.4.5 
obclient [SYS]> create index dbmt.ind_tablea on dbmt.tablea (id desc);
ORA-00600: internal error code, arguments: -4007, Not supported feature or function

-- Server version: OceanBase 4.2.1.2
obclient [SYS]> create index dbmt.ind_tablea on dbmt.tablea (id desc);
ORA-00600: internal error code, arguments: -4007, create desc index not supported
复制

在OceanBase中创建函数索引

obclient [SYS]> create index dbmt.ind_tablea on dbmt.tablea (mod(id, 10));
Query OK, 0 rows affected (0.445 sec)

-- 查看索引信息
[root@ocp zyl]# eoba ind2 dbmt.tablea
Display indexes where table or index name matches dbmt.tablea ....

+-------------+-------+------------+------------+------+-------------+------+
| tenant_name | owner | table_name | index_name | pos# | column_name | dsc  |
+-------------+-------+------------+------------+------+-------------+------+
| test        | DBMT  | TABLEA     | IND_TABLEA |    1 | SYS_NC19$   | NULL |
+-------------+-------+------------+------------+------+-------------+------+
+-------------+-------+------------+------------+-----------------------+------+--------+------+--------+------------+
| tenant_name | owner | table_name | index_name | idxtype               | uniq | status | part | degree | visibility |
+-------------+-------+------------+------------+-----------------------+------+--------+------+--------+------------+
| test        | DBMT  | TABLEA     | IND_TABLEA | FUNCTION-BASED NORMAL | NO   | VALID  | NO   |      1 | VISIBLE    |
+-------------+-------+------------+------------+-----------------------+------+--------+------+--------+------------+
复制

知识扩展

• 测试函数对执行计划的影响时发现，OceanBase在where条件中对普通索引列使用函数后也可以走普通索引，这点与Oracle不同。
• 重新创建测试表和数据，在不创建任何索引的情况下，以下查询SQL走表的全扫描，数据过滤：filter([TABLEA.ID % 10 = 5])

obclient [SYS]> explain select id from dbmt.tablea where mod(id, 10) = 5\G
*************************** 1. row ***************************
Query Plan: 
=====================================
|ID|OPERATOR  |NAME  |EST. ROWS|COST|
-------------------------------------
|0 |TABLE SCAN|TABLEA|1        |82  |
=====================================

Outputs & filters:
-------------------------------------
  0 - output([TABLEA.ID]), filter([TABLEA.ID % 10 = 5]),
      access([TABLEA.ID]), partitions(p0)

1 row in set (0.006 sec)
复制

• 在表上创建一个id列上的普通索引，再次查看对普通索引列使用函数的执行计划走了普通索引的扫描，数据过滤：filter([TABLEA.ID % 10 = 5])

-- 创建普通索引
obclient [SYS]> create index dbmt.ind_tablea on dbmt.tablea (id);
Query OK, 0 rows affected (0.442 sec)

-- 查看执行计划
obclient [SYS]> explain select id from dbmt.tablea where mod(id, 10) = 5\G
*************************** 1. row ***************************
Query Plan: =================================================
|ID|OPERATOR  |NAME              |EST. ROWS|COST|
-------------------------------------------------
|0 |TABLE SCAN|TABLEA(IND_TABLEA)|1        |82  |
=================================================

Outputs & filters:
-------------------------------------
  0 - output([TABLEA.ID]), filter([TABLEA.ID % 10 = 5]),
      access([TABLEA.ID]), partitions(p0)

1 row in set (0.003 sec)
复制

• 这一点与Oracle不同，Oracle对普通索引字段使用了函数后不会走这个普通索引的扫描

-- 创建普通索引
SYS@test1> create index dbmt.ind_tablea on dbmt.tablea (id);

Index created.

-- 查看执行计划
SYS@test1> set autotrace traceonly
SYS@test1> select id from dbmt.tablea where mod(id, 10) = 5;

20 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2728879802

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLEA |     1 |    13 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter(MOD("ID",10)=5)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
        752  bytes sent via SQL*Net to client
        410  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed
复制

• OceanBase上对表同时创建函数索引后，再次查看使用函数的执行计划走了函数索引的扫描，数据过滤与普通索引不同：filter(nil)

-- 创建函数索引
obclient [SYS]> create index dbmt.ind_tablea_mod on dbmt.tablea (mod(id, 10));
Query OK, 0 rows affected (0.445 sec)

-- 查看执行计划
obclient [SYS]> explain select id from dbmt.tablea where mod(id, 10) = 5\G
*************************** 1. row ***************************
Query Plan: =====================================================
|ID|OPERATOR  |NAME                  |EST. ROWS|COST|
-----------------------------------------------------
|0 |TABLE SCAN|TABLEA(IND_TABLEA_MOD)|20       |114 |
=====================================================

Outputs & filters:
-------------------------------------
  0 - output([TABLEA.ID]), filter(nil),
      access([TABLEA.ID]), partitions(p0)

1 row in set (0.007 sec)
复制

• Oracle 上创建函数索引后，执行计划的表现

-- 创建函数索引
SYS@test1> create index dbmt.ind_tablea_mod on dbmt.tablea (mod(id, 10));

Index created.

--查看执行计划
SYS@tbcsf1>select id from dbmt.tablea where mod(id, 10) = 5;

20 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1793602806

------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |   410 | 10660 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TABLEA         |   410 | 10660 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IND_TABLEA_MOD |   164 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   2 - access(MOD("ID",10)=5)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        752  bytes sent via SQL*Net to client
        410  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed
复制