Global index table-based routing for strong-consistency reads

When you initiate a strong-consistency read request to the global index table, OceanBase Database Proxy (ODP) calculates the route by using the index value provided in the SQL statement as the partitioning key. For a partitioned table with a global index, the global index table-based routing feature of ODP can be used to improve the query efficiency when the index value is used as the query condition in read/write requests.

You can enable global index table-based routing for strong-consistency reads by using the following ODP parameters:

ALTER PROXYCONFIG SET server_routing_mode = 'oceanbase';
ALTER PROXYCONFIG SET proxy_primary_zone_name ='';
ALTER PROXYCONFIG SET enable_reroute = true;
ALTER PROXYCONFIG SET enable_index_route = true;

Example

1. Execute the following statements to create a primary table and a global index table.

   • Create a primary table

     obclient [test]> CREATE TABLE T0(C1 INT, C2 INT) PARTITION BY KEY(C1) PARTITIONS 8;
     

   • Create a global index table

     obclient [test]> CREATE INDEX T0_INDEX ON T0(C2) GLOBAL PARTITION BY RANGE(C2)  (PARTITION P0 VALUES LESS THAN (100), PARTITION P1 VALUES LESS THAN (200), PARTITION P2 VALUES LESS THAN (300));
     

2. Provide the index column value in the SQL statement to be executed. Here is a sample statement:

   obclient [test]> SELECT * FROM T0 WHERE C2=150;
   

   When you initiate an index-based query for the first time, the query is routed randomly. OceanBase Database returns the leader of the index table. ODP builds the mapping from the SQL statement to the index table name: SELECT * FROM T0 WHERE C2=150; -> T0_INDEX. When you initiate an index-based query again, ODP directly obtains the mapping of the T0_INDEX table to calculate the route.

3. View the routing process in the diagnostic logs.

   Notice

   You cannot use the EXPLAIN ROUTE command to view the routing process. This is because the SQL statement in EXPLAIN ROUTE will not be actually forwarded to the OBServer node, and therefore the mapping from SELECT * FROM T0 WHERE C2=150; to T0_INDEX cannot be queried.

   [2023-08-21 16:01:01.340924] [56910][Y0-00007FDF4FB404E0] [ROUTE]((*route_diagnosis=
   Trans Current Query:"SELECT * FROM T0 WHERE c2=150"
   
   Route Prompts
   > ROUTE_INFO
     [INFO] Will use global index table name as real table name to route
   
   
   Route Plan
   > SQL_PARSE:{cmd:"COM_QUERY", table:"__idx_500006_T0_index"}
   > ROUTE_INFO:{route_info_type:"USE_INDEX"}
   > LOCATION_CACHE_LOOKUP:{mode:"oceanbase"}
     > TABLE_ENTRY_LOOKUP_DONE:{table:"__idx_500006_T0_index", table_id:500018, partition_num:3, table_type:"USER INDEX", entry_from_remote:false}
     > PARTITION_ID_CALC_START:{}
       > EXPR_PARSE:{col_val:"c2=150"}
       > RESOLVE_EXPR:{part_range:"[150 ; 150]"}
         > RESOLVE_TOKEN:{token_type:"TOKEN_INT_VAL", resolve:{"BIGINT":150}, token:"150"}
       > CALC_PARTITION_ID:{part_description:"partition by range (P500015[{"BIGINT":100}], P500016[{"BIGINT":200}], P500017[{"BIGINT":300}])"}
     > PARTITION_ID_CALC_DONE:{partition_id:1152921504606846978, level:1, partitions:"(p1)"}
     > 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"}
   > HANDLE_RESPONSE:{is_parititon_hit:"false", state:"CONNECTION_ALIVE"}
   )