17认证网概念描述
在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
想了解更多干货，可通过下方扫码关注
可扫码添加上智启元官方客服微信👇
