This topic describes how to configure and use partitioned table-based routing for strong-consistency reads.
Background information
In OceanBase Database, partitions are the basic unit for data storage. When you create a table, the mapping between the table and its partitions is automatically created. A non-partitioned table has only one partition. A partitioned table has multiple partitions.
At present, OceanBase Database does not support partition merging or splitting. Assume that a client requests the data in the P0 partition of a table and the P0 partition is located on OBServer0 and OBServer2. If OceanBase Database Proxy (ODP) routes the request to OBServer1 but the data of the P0 partition does not exist on this node, ODP will reroute the request to OBServer0 or OBServer2. This way, a remote plan is generated. If ODP keeps the mapping from the P0 partition to OBServer0 and OBServer2, a request for data in this partition will be routed to OBServer0 or OBServer 2 where the data resides, thereby avoiding a remote plan.
In addition to the OBServer nodes where the target partition resides, information about the leader and followers of the partition is also required for routing. In OceanBase Database, each partition has a leader and one or more followers, which are distributed on different OBServer nodes. Assume that OBServer0 is the leader and OBServer2 is a follower. If a strong-consistency read request is routed to the follower, the follower will forward the request to the leader. This way, a remote plan is generated. ODP can distinguish weak-consistency read requests from strong-consistency read requests. It forwards strong-consistency read requests to the leader to avoid remote plans.
Note
OceanBase Database supports three table-based routing types: local plan, remote plan, and distributed plan. Both the local plan and remote plan types refer to routing based on a single partition. ODP aims to eliminate remote plans and use local plans for routing. If many SQL queries are routed in a table by using remote plans, the ODP has a problem in routing. You can query the plan_type field in the oceanbase.GV$OB_SQL_AUDIT view to check the table-based routing type. For more information about table-based routing, see Table-based routing of ODP in OceanBase Database documentation.
Usage rules
When the client initiates a strong-consistency read request to a table, the partitioning key, partitioning key expression, or partition name can be provided in the SQL statement. ODP will parse the partitioning key value into the partition ID, find the location of the corresponding replica, and route the statement to the replica based on the partition ID.
The partitioning key is provided in the SQL statement. For example, if the partitioning key of the T0 table is C1, the query statement can be
SELECT * FROM T0 WHERE C1 = xxxx;.The partitioning key expression is provided in the SQL statement. For example, if the expression for the partitioning key of the T0 table is ABS(xxxx), the query statement can be
SELECT * FROM T0 WHERE C1 = ABS(xxxx);.The partition name is provided in the SQL statement. For example, if the T0 table has the P0, P1, and P2 partitions and the SP0, SP1, and SP2 subpartitions, the query statement can be
INSERT INTO T0 PARTITION(P0SSP2) VALUES(xxxx);.
Notice
The syntax for specifying the partition names is
SELECT/UPDATE/INSERT ... table_name PARTITION(partition_name[Ssubpartition_name]). In a partitioned table, partitions are named as P0, P1, and P2 by default, and subpartitions are named as SP0, SP1, and SP2 by default.When you specify a partition name for routing, if you do not specify subpartition names, delete the prefix character
Sfor identifying subpartitions from the statement.
This topic provides three examples to describe how to use partitioned-table based routing for strong-consistency reads.
Example 1: Provide the partitioning key in the SQL statement
Log on to OceanBase Database and execute the following statement to create a partitioned table:
obclient [test]> CREATE TABLE T0(C1 INT) PARTITION BY HASH(C1) PARTITIONS 8;Provide the partitioning key in the SQL statement to be executed.
obclient [test]> SELECT /* +READ_CONSISTENCY(STRONG) */ * FROM T0 WHERE C1=123;Execute the following statement to query the routing process in ODP:
obclient [test]> EXPLAIN ROUTE SELECT /* +READ_CONSISTENCY(STRONG) */ * FROM T0 WHERE C1=123\GThe following return result shows that ODP routes the SQL statement to the leader of the partition.
*************************** 1. row *************************** ... Route Plan ----------------- > SQL_PARSE:{cmd:"COM_QUERY", table:"T0"} > ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"} > LOCATION_CACHE_LOOKUP:{mode:"oceanbase"} > TABLE_ENTRY_LOOKUP_DONE:{table:"T0", table_id:500084, partition_num:8, table_type:"USER TABLE", entry_from_remote:false} > PARTITION_ID_CALC_START:{} > EXPR_PARSE:{col_val:"C1=123"} > RESOLVE_EXPR:{part_range:"[123 ; 123]"} > RESOLVE_TOKEN:{token_type:"TOKEN_INT_VAL", resolve:{"BIGINT":123}, token:"123"} > CALC_PARTITION_ID:{part_description:"partition by hash(INT(binary)) partitions 8"} > PARTITION_ID_CALC_DONE:{partition_id:200065, level:1, partitions:"(p3)"} > PARTITION_ENTRY_LOOKUP_DONE:{leader:"10.10.10.1:50109", entry_from_remote:false} > ROUTE_POLICY:{chosen_route_type:"ROUTE_TYPE_LEADER"} > CONGESTION_CONTROL:{svr_addr:"10.10.10.1:50109"}
Example 2: Provide the partitioning key expression in the SQL statement
Log on to OceanBase Database and execute the following statement to create a partitioned table:
obclient [test]> CREATE TABLE T0(C1 INT) PARTITION BY HASH(C1) PARTITIONS 8;Provide the partitioning key expression in the SQL statement to be executed.
obclient [test]> SELECT /* +READ_CONSISTENCY(STRONG) */ * FROM T0 WHERE C1=ABS(123);Execute the following statement to query the routing process in ODP:
obclient [test]> EXPLAIN ROUTE SELECT /* +READ_CONSISTENCY(STRONG) */ * FROM T0 WHERE C1=123\GThe following return result shows that ODP calculates the ABS() function and then routes the statement to the leader of the partition.
*************************** 1. row *************************** ... Route Plan ----------------- > SQL_PARSE:{cmd:"COM_QUERY", table:"T0"} > ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"} > LOCATION_CACHE_LOOKUP:{mode:"oceanbase"} > TABLE_ENTRY_LOOKUP_DONE:{table:"T0", table_id:500084, partition_num:8, table_type:"USER TABLE", entry_from_remote:false} > PARTITION_ID_CALC_START:{} > EXPR_PARSE:{col_val:"C1=123"} > RESOLVE_EXPR:{part_range:"[123 ; 123]"} > RESOLVE_TOKEN:{token_type:"TOKEN_INT_VAL", resolve:{"BIGINT":123}, token:"123"} > CALC_PARTITION_ID:{part_description:"partition by hash(INT(binary)) partitions 8"} > PARTITION_ID_CALC_DONE:{partition_id:200065, level:1, partitions:"(p3)"} > PARTITION_ENTRY_LOOKUP_DONE:{leader:"10.10.10.1:50109", entry_from_remote:false} > ROUTE_POLICY:{chosen_route_type:"ROUTE_TYPE_LEADER"} > CONGESTION_CONTROL:{svr_addr:"10.10.10.1:50109"}
Example 3: Provide the partition name in the SQL statement
Log on to OceanBase Database and execute the following statement to create a partitioned table:
obclient [test]> CREATE TABLE T0(C1 INT) PARTITION BY HASH(C1) PARTITIONS 8;If you do not specify the partition names when you create a partitioned table, the default partition names are used.
Provide the partition name in the SQL statement to be executed.
obclient [test]> SELECT /* +READ_CONSISTENCY(STRONG) */ * FROM T0 PARTITION(P1) WHERE C1=123;Execute the following statement to query the routing process in ODP:
obclient [test]> EXPLAIN ROUTE SELECT * FROM T0 PARTITION(p1) WHERE C1=123\GThe following return result shows that ODP routes the SQL statement to the P0 partition.
Trans Current Query:"EXPLAIN ROUTE SELECT * FROM T0 PARTITION(p1) WHERE C1=123" Route Prompts ----------------- > ROUTE_INFO [INFO] Will route to partition server or routed by route policy > PARTITION_ID_CALC_DONE [INFO] Will route to specified partition name(p1) Route Plan ----------------- > SQL_PARSE:{cmd:"COM_QUERY", table:"T0"} > ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"} > LOCATION_CACHE_LOOKUP:{mode:"oceanbase"} > TABLE_ENTRY_LOOKUP_DONE:{table:"T0", table_id:500084, partition_num:8, table_type:"USER TABLE", entry_from_remote:false} > PARTITION_ID_CALC_DONE:{partition_id:200063, level:1, part_name:"p1"} > PARTITION_ENTRY_LOOKUP_DONE:{leader:"10.10.10.3:50111"} > ROUTE_POLICY:{chosen_route_type:"ROUTE_TYPE_LEADER"} > CONGESTION_CONTROL:{svr_addr:"10.10.10.3:50111"}The
Will route to specified partition name(p1)information in the preceding return result indicates that ODP routes the request to the partition corresponding to the partition name specified in the statement.