Background
Before ODP V4.3.4, SQL such as the following could not derive the partition key from column equalities; the partition key and value had to appear explicitly:
-- Partition key of table t1 is C1
select * from t1 where C1 = C2 and C2 = 1
Starting with ODP V4.3.5, ODP derives equalities in SQL and uses them for partition calculation.
Limits
At most 1024 derived equalities per SQL statement.
Derivation supports
=,<=>, and the first value ofINexpressions—not greater/less relations.Very complex SQL may not fully derive.
If SQL length exceeds request_buffer_length, the portion beyond the buffer may not participate in derivation.
Usage
Applies to partitioned tables of any partition type; enabled by default with no extra configuration. It is independent of multi-column partition routing, default-partition routing, generated-column routing, partition function routing, partition name routing, and partition hints; they generally do not interfere. SQL that needs multiple features together should still route and execute correctly.
Examples
Example 1: ordinary SQL with equality derivation
Create a hash-partitioned table:
obclient[test]> create table t1_hash( -> c1 int, -> c2 int, -> c3 int -> ) partition by hash (c1) partitions 4;Run
EXPLAIN ROUTE:obclient[test]> explain route select * from t1_hash where c1 = c2 and c2 = c3 and c3 = 1\GSample output:
*************************** 1. row *************************** Route Plan: Trans Current Query:"explain route select * from t1_hash where c1 = c2 and c2 = c3 and c3 = 1" Route Prompts ----------------- > ROUTE_INFO [INFO] Will do table partition location lookup to decide which OBServer to route to > ROUTE_POLICY [INFO] Will route to table's partition leader replica(127.0.0.1:2881) using route policy PRIMARY_ZONE_FIRST because query for STRONG read Route Plan ----------------- > SQL_PARSE:{cmd:"OB_MYSQL_COM_QUERY", table:"t1_hash"} > ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"} > LOCATION_CACHE_LOOKUP:{mode:"oceanbase"} > TABLE_ENTRY_LOOKUP_DONE:{table:"t1_hash", table_id:"500038", table_type:"USER TABLE", partition_num:4, entry_from_remote:false} > PARTITION_ID_CALC_START:{} > EXPR_PARSE:{col_val:"c1=,c2=,c3=1"} > RESOLVE_TOKEN:{token_type:"TOKEN_INT_VAL", resolve:"BIGINT:1", token:"1"} > CALC_PARTITION_ID:{part_description:"partition by hash(INT<binary>) partitions 4"} > PARTITION_ID_CALC_DONE:{partition_id:200014, level:1, partitions:"(p1)"} > PARTITION_ENTRY_LOOKUP_DONE:{leader:"127.0.0.1:2881", entry_from_remote:false} > ROUTE_POLICY:{route_policy:"", chosen_route_type:"ROUTE_TYPE_LEADER", type:"FULL"}
Example 2: generated column with equality derivation
Create the table:
obclient[test]> create table gen3( -> c1 int, -> c2 VARCHAR(20), -> c3 VARCHAR(20), -> c4 VARCHAR(20) GENERATED ALWAYS AS (substr(c3, 2)) VIRTUAL -> ) partition by list columns(c4, c1) ( -> partition p0 values in (('ab',1), ('ab',2)), -> partition p1 values in (('cd',1), ('cd',2), ('cd',null)), -> partition p2 values in(default) -> );EXPLAIN ROUTE:obclient[test]> explain route select * from gen3 where c4 = c2 and c2 = 'ab' and c1 = a and a = 1\GSample output:
*************************** 1. row *************************** Route Plan: Trans Current Query:"explain route select * from gen3 where c4 = c2 and c2 = 'ab' and c1 = a and a = 1" Route Prompts ----------------- > ROUTE_INFO [INFO] Will do table partition location lookup to decide which OBServer to route to > ROUTE_POLICY [INFO] Will route to table's partition leader replica(127.0.0.1:2881) using route policy PRIMARY_ZONE_FIRST because query for STRONG read Route Plan ----------------- > SQL_PARSE:{cmd:"OB_MYSQL_COM_QUERY", table:"gen3"} > ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"} > LOCATION_CACHE_LOOKUP:{mode:"oceanbase"} > TABLE_ENTRY_LOOKUP_DONE:{table:"gen3", table_id:"500049", table_type:"USER TABLE", partition_num:3, entry_from_remote:false} > PARTITION_ID_CALC_START:{} > EXPR_PARSE:{col_val:"c4=,c2=ab,c1=,a=1"} > RESOLVE_TOKEN:{token_type:"TOKEN_STR_VAL", resolve:"VARCHAR:ab<utf8mb4_general_ci>", token:"ab"} > RESOLVE_TOKEN:{token_type:"TOKEN_INT_VAL", resolve:"BIGINT:1", token:"1"} > CALC_PARTITION_ID:{part_description:"partition by list (P500050[(VARCHAR:cd<utf8mb4_general_ci>, NULL:NULL), (VARCHAR:cd<utf8mb4_general_ci>, BIGINT:1), (VARCHAR:cd<utf8mb4_general_ci>, BIGINT:2)], P500051[(VARCHAR:ab<utf8mb4_general_ci>, BIGINT:1), (VARCHAR:ab<utf8mb4_general_ci>, BIGINT:2)], P500052[(EXT:maxvalue)])"} > PARTITION_ID_CALC_DONE:{partition_id:200022, level:1, partitions:"(p1)"} > PARTITION_ENTRY_LOOKUP_DONE:{leader:"127.0.0.1:2881", entry_from_remote:false} > ROUTE_POLICY:{route_policy:"", chosen_route_type:"ROUTE_TYPE_LEADER", type:"FULL"}
Example 3: partition expression with equality derivation
Create the table:
obclient[test]> create table partition_test1 (c1 int, c2 int, c3 int) partition by hash(c1 + c2) partitions 8 subpartition by hash(c1 + c2 + 10 + c2 * c1) subpartitions 8;EXPLAIN ROUTE:obclient[test]> explain route select * from partition_test1 where c1 = 4 and c2 = c3 and c3 = 4\GSample output:
*************************** 1. row *************************** Route Plan: Trans Current Query:"explain route select * from partition_test1 where c1 = 4 and c2 = c3 and c3 = 4" Route Prompts ----------------- > ROUTE_INFO [INFO] Will do table partition location lookup to decide which OBServer to route to > ROUTE_POLICY [INFO] Will route to table's partition leader replica(127.0.0.1:2881) using route policy PRIMARY_ZONE_FIRST because query for STRONG read Route Plan ----------------- > SQL_PARSE:{cmd:"OB_MYSQL_COM_QUERY", table:"partition_test1"} > ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"} > LOCATION_CACHE_LOOKUP:{mode:"oceanbase"} > TABLE_ENTRY_LOOKUP_START:{} > FETCH_TABLE_RELATED_DATA:{part_level:2, first part_expr:"c1 + c2", sub_part_expr:"c1 + c2 + 10 + c2 * c1"} > TABLE_ENTRY_LOOKUP_DONE:{table:"partition_test1", table_id:"500053", table_type:"USER TABLE", partition_num:64} > PARTITION_ID_CALC_START:{} > EXPR_PARSE:{col_val:"c1=4,c2=,c3=4"} > RESOLVE_TOKEN:{token_type:"TOKEN_INT_VAL", resolve:"BIGINT:4", token:"4"} > RESOLVE_TOKEN:{token_type:"TOKEN_INT_VAL", resolve:"BIGINT:4", token:"4"} > RESOLVE_TOKEN:{token_type:"TOKEN_NONE", resolve:"BIGINT:8", part_key_func:"OB_PROXY_EXPR_TYPE_FUNC_ADD"} > RESOLVE_TOKEN:{token_type:"TOKEN_NONE", resolve:"BIGINT:34", part_key_func:"OB_PROXY_EXPR_TYPE_FUNC_ADD"} > CALC_PARTITION_ID:{part_description:"partition by hash(INT<binary>) partitions 8 subpartition by hash(INT<binary>) partitions 8"} > PARTITION_ID_CALC_DONE:{partition_id:200026, level:2, partitions:"(p0sp2)"} > PARTITION_ENTRY_LOOKUP_DONE:{leader:"127.0.0.1:2881"} > ROUTE_POLICY:{route_policy:"", chosen_route_type:"ROUTE_TYPE_LEADER", type:"FULL"}
Example 4: datetime with equality derivation
Create the table:
obclient[test]> create table t_timestamp_range1(c1 DATE, c2 int, c3 int) partition by range(to_days(c1)) -> ( -> partition p0 values less than (to_days('2024-04-01 00:00:00')), -> partition p1 values less than (to_days('2024-08-01 00:00:00')), -> partition p2 values less than (to_days('2024-12-01 00:00:00')) -> );EXPLAIN ROUTE:obclient[test]> explain route select * from t_timestamp_range1 where c1 = c2 and c2 = '2024-07-30 00:00:00'\GSample output:
*************************** 1. row *************************** Route Plan: Trans Current Query:"explain route select * from t_timestamp_range1 where c1 = c2 and c2 = '2024-07-30 00:00:00'" Route Prompts ----------------- > ROUTE_INFO [INFO] Will do table partition location lookup to decide which OBServer to route to > ROUTE_POLICY [INFO] Will route to table's partition leader replica(127.0.0.1:2881) using route policy PRIMARY_ZONE_FIRST because query for STRONG read Route Plan ----------------- > SQL_PARSE:{cmd:"OB_MYSQL_COM_QUERY", table:"t_timestamp_range1"} > ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"} > LOCATION_CACHE_LOOKUP:{mode:"oceanbase"} > TABLE_ENTRY_LOOKUP_START:{} > FETCH_TABLE_RELATED_DATA:{part_level:1, first part_expr:"to_days(c1)"} > TABLE_ENTRY_LOOKUP_DONE:{table:"t_timestamp_range1", table_id:"500144", table_type:"USER TABLE", partition_num:3} > PARTITION_ID_CALC_START:{} > EXPR_PARSE:{col_val:"c1=,c2=2024-07-30 00:00:00"} > RESOLVE_TOKEN:{token_type:"TOKEN_STR_VAL", resolve:"VARCHAR:2024-07-30 00:00:00<utf8mb4_general_ci>", token:"2024-07-30 00:00:00"} > RESOLVE_TOKEN:{token_type:"TOKEN_NONE", resolve:"BIGINT:739462", part_key_func:"OB_PROXY_EXPR_TYPE_FUNC_TO_DAYS"} > CALC_PARTITION_ID:{part_description:"partition by range (P500145[BIGINT:739342], P500146[BIGINT:739464], P500147[BIGINT:739586])"} > PARTITION_ID_CALC_DONE:{partition_id:200105, level:1, partitions:"(p1)"} > PARTITION_ENTRY_LOOKUP_DONE:{leader:"127.0.0.1:2881"} > ROUTE_POLICY:{route_policy:"", chosen_route_type:"ROUTE_TYPE_LEADER", type:"FULL"}
Example 5: multi-table join with equality derivation
Create tables:
-- Partitioned table tsh1 obclient[test]> create table if not exists tsh1 (c1 int,c2 varchar(10),c3 int) partition by hash(c1) partitions 4; -- Partitioned table tsh2 obclient[test]> create table if not exists tsh2 (c1 int,c2 varchar(10),c3 int) partition by hash(c1) partitions 4; -- Partitioned table tsh3 obclient[test]> create table if not exists tsh3 (c1 int,c2 varchar(10),c3 int) partition by hash(c1) partitions 4;EXPLAIN ROUTE:obclient[test]> explain route select a.c1 from tsh1 a ,tsh2 b where a.c1 = b.c1 and a.c2 in ('a','s','d','f','g','h') and b.c2 = 'a' and b.c1 = 1\GSample output:
*************************** 1. row *************************** Route Plan: Trans Current Query:"explain route select a.c1 from tsh1 a ,tsh2 b where a.c1 = b.c1 and a.c2 in ('a','s','d','f','g','h') and b.c2 = 'a' and b.c1 = 1" Route Prompts ----------------- > ROUTE_INFO [INFO] Will do table partition location lookup to decide which OBServer to route to > ROUTE_POLICY [INFO] Will route to table's partition leader replica(127.0.0.1:2881) using route policy PRIMARY_ZONE_FIRST because query for STRONG read Route Plan ----------------- > SQL_PARSE:{cmd:"OB_MYSQL_COM_QUERY", table:"tsh1"} > ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"} > LOCATION_CACHE_LOOKUP:{mode:"oceanbase"} > TABLE_ENTRY_LOOKUP_START:{} > FETCH_TABLE_RELATED_DATA:{part_level:1, first part_expr:"c1"} > TABLE_ENTRY_LOOKUP_DONE:{table:"tsh1", table_id:"500148", table_type:"USER TABLE", partition_num:4} > PARTITION_ID_CALC_START:{} > EXPR_PARSE:{col_val:"c1=,c2=a,c2=a,c1=1"} > RESOLVE_TOKEN:{token_type:"TOKEN_STR_VAL", resolve:"VARCHAR:a<utf8mb4_general_ci>", token:"a"} > RESOLVE_TOKEN:{token_type:"TOKEN_STR_VAL", resolve:"VARCHAR:a<utf8mb4_general_ci>", token:"a"} > RESOLVE_TOKEN:{token_type:"TOKEN_INT_VAL", resolve:"BIGINT:1", token:"1"} > CALC_PARTITION_ID:{part_description:"partition by hash(INT<binary>) partitions 4"} > PARTITION_ID_CALC_DONE:{partition_id:200108, level:1, partitions:"(p1)"} > PARTITION_ENTRY_LOOKUP_DONE:{leader:"127.0.0.1:2881"} > ROUTE_POLICY:{route_policy:"", chosen_route_type:"ROUTE_TYPE_LEADER", type:"FULL"}