Selecting a Shard DB through the Shard SQL Hint

Shard SQL Hint

With the hints and configuration data included in a SQL hint statement, the CUBRID SHARD selects a shard DB that will process the requests from applications. The types of available SQL hints are as follows:

The terms are summarized as shown below: For more information on shard terms, see CUBRID SHARD Terminologies.

• shard key: A column to distinguish shard DBs. In general, this column exists in all or most tables in a shard DB and has a unique value.
• shard id: An identifier that can be used to logically distinguish shards. For example, when one DB is split into four shard DBs, there are four shard IDs.

For more information on the query process using hints and configuration information, see General Procedure of Executing Queries by Using Shard SQL Hint.

shard_key Hint

The shard_key hint is to specify the position of a bind or literal variable. This hint should be positioned in front of either of them.

Ex) Specifies the position of a bind variable. Executes the query in the shard DB corresponding to the student_no value that would be bound when executed.

SELECT name FROM student WHERE student_no = /*+ shard_key */ ?

Ex) Specifies the position of a literal value. Executes the query in the shard DB corresponding to the student_no value (the literal value) that is 123 when executed.

SELECT name FROM student WHERE student_no = /*+ shard_key */ 123

shard_val Hint

The shard_val hint is used when there is no shard column that can be used to identify the shard DB in the query. It sets the shard key column as the value of the shard_val hint. The shard_val hint can be positioned anywhere in an SQL statement.

Ex) When the shard key is not included in the student_no or in the query, the query is performed in the shard DB in which the shard key (student_no) is 123.

SELECT age FROM student WHERE name =? /*+ shard_val(123) */

shard_id Hint

Regardless of the shard key column value, the shard_id hint can be used when the user specifies a shard for query execution. The shard_id hint can be positioned anywhere in an SQL statement.

Ex) When the query is performed in shard DB #3, queries students whose value of age is greater than 17 in the shard DB #3.

SELECT * FROM student WHERE age > 17 /*+ shard_id(3) */

General Procedure of Executing Queries by Using Shard SQL Hint

Executing Queries

The following shows how a user-requested query is executed.

• An application makes a request for a query to the CUBRID SHARD through the JDBC interface. It adds the shard_key hint to the SQL statement to specify the shard DB from where the query will be executed.
• The SQL hint, like the example above, in the SQL statement, should be positioned in front of the bind variable or literal value of the column specified by the shard key.

The shard SQL hint configured by the bind variable is as follows:

The shard SQL hint specified in the literal value is as follows:

Select a Shard DB to Analyze and Perform a Query

Select a shard DB to analyze and perform the query by following the steps below:

• SQL queries received from users are rewritten in the format that is appropriate for internal processing.
• Select the shard DB that executed the query by using the SQL statement and hint requested by the user.
  • When the SQL hint is set in the bind variable, select the shard DB which will execute the query by using the result of hashing the value of the shard_key bind variable and the configuration information.
  • The hash function can be specified by the user. If not specified, the shard_key value is hashed by using the default hash function. Default hash functions are as follows:
    • When the shard_key is an integer

    • Default hash function (shard_key) = shard_key mod SHARD_KEY_MODULAR parameter (default value 256)

    • When the shard_key is a string

    • Default hash function (shard_key) = shard_key[0] mod  SHARD_KEY_MODULAR parameter (default value 256)

Note When the shard_key bind variable value is 100, "Default hash function (shard_key) = 100 % 256 = 100." Therefore, the shard DB #1 (the hash result is 100) will be selected and then the user request will be sent to the selected shard DB #1.

Return the Query Execution Result

Return the query execution result as follows:

• Receives the query execution result from the shard DB #1 and then returns it to the requested application.