Overview

This topic describes the support for function partitioning key-based routing and how to use and verify this routing feature.

Background information

As a distributed database, OceanBase Database may store different tables and even different partitions of each table on different servers. To read and write data, the database must first locate the table or partition to which the data belongs. Although every OBServer node supports routing and forwarding and can locally execute SQL queries, if SQL queries are not forwarded to appropriate nodes, the performance and overall throughput of the database will be severely undermined.

As a high-performance reverse proxy server for OceanBase Database, OceanBase Database Proxy (ODP) supports routing and forwarding as its core feature. The more functions that ODP can parse, the more possible that SQL queries can be forwarded to the most appropriate OBServer nodes for execution. This decreases the occurrences of slow queries and improves user experience.

Considerations

• Function partitioning key-based routing is a new feature provided by ODP V4.2.3. To use this feature, make sure that the ODP you are using is V4.2.3 or later.

• Functions that are not supported by this feature cannot be parsed by ODP to implement accurate routing, which leads to random forwarding to OBServer nodes, but can still properly work in OceanBase Database.

• ODP calculates partitioning keys to accurately route user requests, and incorrect routing does not affect the correctness of request execution.

• The SQL parser of ODP can parse SQL queries only in a simple way. In some scenarios, such as nested queries, the SQL parser may calculate the partitioning key inaccurately. We recommend that you refer to the following examples in actual use of the functions. For more information, see other topics in this chapter. You can also validate whether an SQL query can be accurately routed by referring to Validation method.

Examples

This section provides simple examples of using each function in different scenarios. For more information about how to use each function, see the corresponding topics in this chapter.

Partitioning key scenario

1. Create a table named test1.

   create table test1 (
     c1 int,
     c2 int
   ) partition by hash(c1) partitions 3;
   

2. Query data where c1 is 1 in the test1 table.

   select * from test1 where c1 = nvl(null, 1);
   

Default value scenario

1. Create a table named test2 and set the default value of the c1 column to nvl(null,12.3).

   create table test2(
    c1 int default nvl(null,12.3),
    c2 int
   ) partition by hash(c1) partitions 3;
   

2. Insert data into the test2 table.

   Because the c1 column has a default value, you only need to insert values into the c2 column.

   insert into test2 (c2) values (2);
   

Generated column scenario

Create a table named test3 and set the values of the c2 column to be automatically generated by the substr(c1, 1, 4) expression.

create table test3(
  c1 varchar2(20) default 'aaaaaaaaaaaa',
  c2 varchar2(20) GENERATED ALWAYS AS (substr(c1, 1, 4))
) partition by hash(c2) partitions 3;

Support

ODP supports function partitioning key-based routing for partitioning keys, default values, and generated columns.

Partitioning keys and default values

This section describes the functions supported in partitioning keys and default values. ODP supports elementary arithmetic operations, including +, -, *, and /. For more information, see Basic function usage. ODP also supports string functions, numeric calculation functions, and date calculation functions.

String functions

The following table describes ODP's support for string functions in different modes. For more information, see String functions.

Numeric calculation functions

The following table describes ODP's support for numeric calculation functions in different modes. For more information, see Numeric calculation functions.

Date calculation functions

The following table describes ODP's support for date calculation functions in different modes. For more information, see Date calculation functions.

Generated columns

Currently, ODP supports only the SUBSTR function for generated columns. Take note that:

• Generated columns and corresponding source columns must be of a string type, such as varchar and text.

• Currently, the SUBSTR function in generated columns does not support the calculation of multi-char characters.

Basic function usage

Currently, ODP allows you to nest functions with elementary arithmetic operations in partitioning keys and default values. In this case, you need to check whether the functions are supported.

Here are some examples:

• Partitioning keys

  Assume that the database has a table named test1. The c1 column is the partitioning key of the table. ODP can accurately route the SQL query that retrieves data based on a condition from the test1 table.

  select * from test1 where c1 = ((((-(2)*(2-4)) +mod(mod((1+(3*2)),2),1)) -(4/2))+ mod(1,2));
  

• Default values

  Create a table named test2 and use a function to define the default value of the c1 column.

  CREATE TABLE test2(
    c1 number default mod(5,2)+1,
    c2 int
  )
  PARTITION BY HASH (c1) PATITIONS 3;
  

Validation method

This section describes how to validate whether an SQL query can be accurately routed.

You can use the routing diagnostics feature of ODP V4.2.2 or later to check whether ODP can correctly route an SQL query. Run the explain route <your_sql>; command and check whether Route Prompts in the output contains WARN. If it does not contain WARN, the partition routing is accurate. For more information about the routing diagnostics feature, see Routing diagnostics.

Take an unsupported function as an example. The output for incorrect routing is as follows:

Trans Current Query:"explain route select * from t1 where c1=sin(1)"

Route Prompts
-----------------
> ROUTE_INFO
  [INFO] Will route to partition server or ldc
> RESOLVE_TOKEN
  [WARN] Not support expr func(OB_PROXY_EXPR_TYPE_NONE)

Route Plan
-----------------
> SQL_PARSE:{cmd:"COM_QUERY", table:"t1"}
> ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"}
> LOCATION_CACHE_LOOKUP:{mode:"oceanbase"}
  > TABLE_ENTRY_LOOKUP_DONE:{table:"t1", table_id:500010, partition_num:3, table_type:"USER TABLE", entry_from_remote:false}
  > PARTITION_ID_CALC_START:{}
    > EXPR_PARSE:{col_val:"c1=sin"}
    > RESOLVE_EXPR:{error:-5055, part_range:"(MIN ; MAX)always true"}
      > RESOLVE_TOKEN:{error:-5055, token_type:"TOKEN_FUNC", token:"sin"}
  > PARTITION_ID_CALC_DONE:{error:-5055, partition_id:-1, level:1, partitions:"(p-1)"}
> ROUTE_INFO:{route_info_type:"USE_CACHED_SESSION", svr_addr:"10.10.10.1:21102"}
> CONGESTION_CONTROL:{svr_addr:"10.10.10.1:21102"}

The output for correct routing is as follows:

Trans Current Query:"explain route select * from t1 where c1=mod(5,2)"

Route Prompts
-----------------
> ROUTE_INFO
  [INFO] Will route to partition server or ldc

Route Plan
-----------------
> SQL_PARSE:{cmd:"COM_QUERY", table:"t1"}
> 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, part_expr:"c1"}
  > TABLE_ENTRY_LOOKUP_DONE:{table:"t1", table_id:500010, partition_num:3, table_type:"USER TABLE"}
  > PARTITION_ID_CALC_START:{}
    > EXPR_PARSE:{col_val:"c1=mod"}
    > RESOLVE_EXPR:{part_range:"[1 ; 1]"}
      > RESOLVE_TOKEN:{token_type:"TOKEN_FUNC", resolve:"DECIMAL:1", token:"mod", expr_type:"OB_PROXY_EXPR_TYPE_FUNC_MOD"}
    > CALC_PARTITION_ID:{part_description:"partition by hash(INT<binary>) partitions 3"}
  > PARTITION_ID_CALC_DONE:{partition_id:200003, level:1, partitions:"(p1)"}
  > PARTITION_ENTRY_LOOKUP_DONE:{leader:"10.10.10.1:21100"}
> ROUTE_POLICY:{chosen_route_type:"ROUTE_TYPE_LEADER"}
> CONGESTION_CONTROL:{svr_addr:"10.10.10.1:21100"}