OB中的分键是否还需要创建索引

概述

OB中的表实际也是索引有序结构（与ORACLE中索引组织表类似），要求必须有主键，如果创建表时显示指定了主键，则要求分区键是主键的子集，如果没有显示指定分区键，则也会隐士创建 分区键+__pk_increment 组成的主键,总之分区键一定会包含在主键中，是索引的一部分。某些场景不需要再单独创建索引。

示例

没有显示指定主键的分区表

create table tparta(intime date,id number,c varchar2(100))
partition by range(intime)
(
partition part_20240706 values less than(to_date('2024-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition part_20240707 values less than(to_date('2024-07-08 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition part_20240708 values less than(to_date('2024-07-09 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition part_20240709 values less than(to_date('2024-07-10 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition part_20240710 values less than(to_date('2024-07-11 00:00:00','yyyy-mm-dd hh24:mi:ss'))
);

insert into tparta select to_date('2024-07-11 00:00:00','yyyy-mm-dd hh24:mi:ss')-rownum/(3600*24),rownum,
'test'||rownum from dual connect by rownum<=3600*24*5;

commit;



obclient [SYS]> create table tparta(intime date,id number,c varchar2(100))
    -> partition by range(intime)
    -> (
    -> partition part_20240706 values less than(to_date('2024-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss')),
    -> partition part_20240707 values less than(to_date('2024-07-08 00:00:00','yyyy-mm-dd hh24:mi:ss')),
    -> partition part_20240708 values less than(to_date('2024-07-09 00:00:00','yyyy-mm-dd hh24:mi:ss')),
    -> partition part_20240709 values less than(to_date('2024-07-10 00:00:00','yyyy-mm-dd hh24:mi:ss')),
    -> partition part_20240710 values less than(to_date('2024-07-11 00:00:00','yyyy-mm-dd hh24:mi:ss'))
    -> );
Query OK, 0 rows affected (0.135 sec)

obclient [SYS]> insert into tparta select to_date('2024-07-11 00:00:00','yyyy-mm-dd hh24:mi:ss')-rownum/(3600*24),rownum,
    -> 'test'||rownum from dual connect by rownum<=3600*24*5;
Query OK, 432000 rows affected (7.802 sec)
Records: 432000  Duplicates: 0  Warnings: 0

obclient [SYS]> commit;


alter system major freeze;



MySQL [oceanbase]> select tenant_id,table_id,table_name from __all_virtual_table where table_name='tparta';
+-----------+------------------+------------+
| tenant_id | table_id         | table_name |
+-----------+------------------+------------+
|      1001 | 1100611139454084 | TPARTA     |
+-----------+------------------+------------+
1 row in set (0.008 sec)

select column_id,column_name,rowkey_position,partition_key_position from __all_virtual_column where table_id=1100611139454084 order by rowkey_position;


MySQL [oceanbase]> select column_id,column_name,rowkey_position,partition_key_position from __all_virtual_column where table_id=1100611139454084 order by rowkey_position;
+-----------+----------------+-----------------+------------------------+
| column_id | column_name    | rowkey_position | partition_key_position |
+-----------+----------------+-----------------+------------------------+
|        17 | ID             |               0 |                      0 |
|        18 | C              |               0 |                      0 |
|        16 | INTIME         |               1 |                      1 |<<<
|         1 | __pk_increment |               2 |                      0 |<<<
+-----------+----------------+-----------------+------------------------+
4 rows in set (0.054 sec)



explain extended_noaddr
select * from tparta where INTIME>=to_date('2024-07-11 00:00:00','yyyy-mm-dd hh24:mi:ss')-1/1440\G

obclient [SYS]> explain extended_noaddr
    -> select * from tparta where INTIME>=to_date('2024-07-11 00:00:00','yyyy-mm-dd hh24:mi:ss')-1/1440\G
*************************** 1. row ***************************
Query Plan: =====================================
|ID|OPERATOR  |NAME  |EST. ROWS|COST|
-------------------------------------
|0 |TABLE SCAN|TPARTA|60       |47  |
=====================================

Outputs & filters:
-------------------------------------
  0 - output([TPARTA.INTIME], [TPARTA.ID], [TPARTA.C]), filter(nil),
      access([TPARTA.INTIME], [TPARTA.ID], [TPARTA.C]), partitions(p4),
      is_index_back=false,
      range_key([TPARTA.INTIME], [TPARTA.__pk_increment]), range(2024-07-10 23:59:00,MIN ; NULL,MIN),
      range_cond([TPARTA.INTIME >= ?])

1 row in set (0.004 sec)

按intime查询1分钟的数据，可以看到可以正常利用主键，range(2024-07-10 23:59:00,MIN ; NULL,MIN)