12.18.5 Rules used to allocate back-end servers that search shared tables

This subsection explains the rules that are used for allocating back-end servers that search shared tables. The allocation rules vary between cases in which only shared tables are specified in a single SQL statement, and cases in which multiple tables that contain shared tables are specified in a single SQL statement. The following subsections explain allocation rules separately for each case.

Organization of this subsection
(1) When all tables specified in a single SQL statement are shared tables
(2) When multiple tables that contain shared tables are specified in a single SQL statement

(1) When all tables specified in a single SQL statement are shared tables

When all tables specified in a single SQL statement are shared tables, the allocation of back-end servers that search shared tables is determined by conditions such as what kind of search is performed by the immediately preceding SQL statement in the same transaction.

The following table lists the rules used for allocating back-end servers that search shared tables (when all tables specified in a single SQL statement are shared tables). Item 1 has the highest priority.

Table 12-11 Rules used for allocating back-end servers that search shared tables (when all tables specified in a single SQL statement are shared tables)

ItemSearch conditionsBack-end server allocated
1One of the following conditions is met:
  • A LOCK TABLE statement that specifies IN EXCLUSIVE MODE is executed on the shared table to be searched.
  • A search is performed using a FOR UPDATE clause.
HiRDB allocates an updatable back-end server.
2Shared tables#2 are used in different SQL statements in the same transaction (or across multiple transactions#1).From among the shared tables that were searched in the same transaction, HiRDB allocates the back-end server used by the SQL statement that most recently accessed a shared table.
For an example of how the back-end servers are allocated in this case, see Example 1.
3Row-partitioned tables#3 are used in different SQL statements in the same transaction (or across multiple transactions#1), and there is a restriction#4 that partitioning column searches are performed by only one back-end server.From among the row-partitioned tables for which partitioning column searches are restricted to being performed by one back-end server in the same transaction, HiRDB randomly allocates a back-end server from among the back-end servers used by the SQL statement that most recently accessed a row-partitioned table.
For an example of how the back-end servers are allocated in this case, see Example 2.
4Unpartitioned tables#5 are used in different SQL statements in the same transaction (or across multiple transactions#1).From among the unpartitioned tables searched in the same transaction, HiRDB randomly allocates a back-end server from among the back-end servers used by the SQL statement that most recently accessed an unpartitioned table.
For an example of how the back-end servers are allocated in this case, see Example 3.
5Row-partitioned tables are used in different SQL statements in the same transaction (or across multiple transactions#1).From among the row-partitioned table searched in the same transaction, HiRDB randomly allocates a back-end server from among the back-end servers used by the SQL statement that most recently accessed a row-partitioned table.
For an example of the how back-end servers are allocated in this case, see Example 4.
6There is a back-end server in the same unit as the connected front-end server.HiRDB randomly allocates a back-end server from among the back-end servers in the same unit as the front-end server.
For an example of the how back-end servers are allocated in this case, see Example 5.
7Cases other than search conditions 1 through 6HiRDB randomly allocates a back-end server.
For an example of the how back-end servers are allocated in this case, see Example 6.
#1
Refers to use of the BES connection holding facility, holdable cursor, and local buffers in AP units.
#2
Exceptions are shared tables on which a LOCK TABLE statement that specifies IN EXCLUSIVE MODE is executed, and shared tables that perform searches using FOR UPDATE clauses.
#3
Exceptions are flexible hash partitions and partitions in a single back-end server.
#4
This is a condition (predicate or a predicate on which an OR is executed) that specifies only the column of a single table in the search conditions.
#5
Includes shared tables on which a LOCK TABLE statement that specifies IN EXCLUSIVE MODE is executed, and shared tables that perform searches using FOR UPDATE clauses.

(a) Example 1

[Figure]

Explanation
  1. In SQL1, the back-end server used to access-shared table S1 is BES2.
  2. In SQL2, the back-end servers used to access row-partitioned table D are BES1, BES2, and BES3.
  3. When shared table S2 is accessed in SQL3 immediately after SQL1 and SQL2, BES2, which was used to access shared table S1, is allocated.
(b) Example 2

[Figure]

Explanation
  1. In SQL1, row-partitioned table D1 and row-partitioned table D2 are searched with the restriction that only one back-end server can search in a partitioning column. The back-end server used to access row-partitioned table D1 is BES2, and the back-end server used to access row-partitioned table D2 is BES3.
  2. In SQL2, the back-end server used to access unpartitioned table N is BES1.
  3. When shared table S is accessed in SQL3 immediately after SQL1 and SQL2, BES2 is randomly selected from among the back-end servers that were used to access row-partitioned tables D1 and D2, and is allocated (since allocation is random, it might be BES3 in some cases).
(c) Example 3

[Figure]

Explanation
  1. In SQL1, unpartitioned table N1 and unpartitioned table N2 are searched. The back-end server used to access unpartitioned table N1 is BES2, and the back-end server used to access unpartitioned table N2 is BES3.
  2. In SQL2, the back-end servers used to access row-partitioned table D are BES1, BES2, and BES3.
  3. When shared table S is accessed in SQL3 immediately after SQL1 and SQL2, BES2 is randomly selected from among the back-end servers used to access unpartitioned tables N1 and N2, and is allocated. (since allocation is random, it might be BES3 in some cases).
(d) Example 4

[Figure]

Explanation
  1. In SQL1, row-partitioned table D1 and row-partitioned table D2 are searched. The back-end servers that are used to access row-partitioned table D1 are BES1 and BES2, and the back-end servers that are used to access row-partitioned table D2 are BES2 and BES3.
  2. In SQL2, the back-end servers that are used to access row-partitioned table D3 are BES1 and BES2.
  3. When shared table S is accessed in SQL3 immediately after SQL1 and SQL2, BES2 is randomly selected from among the back-end servers used to access row-partitioned table D3, and is allocated (since allocation is random, it might be BES1 in some cases).
(e) Example 5

[Figure]

Explanation
When shared table S is accessed immediately after a transaction starts, the back-end server BES2 in the same unit as the front-end server is allocated.
(f) Example 6

[Figure]

Explanation
When shared table S is accessed immediately after a transaction starts, the randomly selected back-end server BES2 is allocated (since allocation is random, it might be BES1 or BES3 in some cases).

(2) When multiple tables that contain shared tables are specified in a single SQL statement

When multiple tables that include a shared table are specified in a single SQL statement, allocation of the back-end servers that search the shared table is determined by conditions such as the sort of search that is performed in that single SQL statement. When both shared and unshared tables are included in a single SQL statement, HiRDB allocates a back-end server to the unshared table before allocating a back-end server to the shared table.

The following table lists the rules used for allocating back-end servers that search shared tables (when multiple tables that contain shared tables are specified in a single SQL statement). Item 1 has the highest priority.

Table 12-12 Rules used for allocating back-end servers that search shared tables (when multiple tables that contain shared tables are specified in a single SQL statement)

ItemSearch conditionsBack-end server allocated
1One of the following conditions is met:
  • A LOCK TABLE statement that specifies IN EXCLUSIVE MODE is executed on the shared table to be searched.
  • A search is performed using a FOR UPDATE clause.
HiRDB allocates an updatable back-end server.
2Shared tables#1 are used in the same SQL statement.When multiple shared tables are used in a single SQL statement, HiRDB allocates the same back-end server. When all the tables are shared tables, HiRDB allocates according to Table 12-11 Rules used for allocating back-end servers that search shared tables (when all tables specified in a single SQL statement are shared tables).
For an example of how back-end servers are allocated in this case, see Example 7.
3Row-partitioned tables#2 are used in a single SQL statement, and there is a restriction#3 to perform searches on only one back-end server in a partitioning column.HiRDB randomly allocates a back-end server from among the back-end servers used to access the row-partitioned tables being used in a single SQL statement.
For an example of how back-end servers are allocated in this case, see Example 8.
4Unpartitioned tables#4 are used in the same SQL statement.HiRDB randomly allocates a back-end server from among the back-end servers used to access the unpartitioned tables being used in a single SQL statement.
For an example of how back-end servers are allocated in this case, see Example 9.
5Row-partitioned tables are used in the same SQL statement.HiRDB randomly allocates a back-end server from among the back-end servers used to access the row-partitioned tables being used in a single SQL statement.
For an example of how back-end servers are allocated in this case, see Example 10.
#1
Exceptions are shared tables that execute LOCK TABLE statements that specify IN EXCLUSIVE MODE, and shared tables that perform searches using FOR UPDATE clauses.
#2
Exceptions are flexible hash partitions and partitions in a single back-end server.
#3
This is a condition (predicate or a predicate on which an OR is executed) that specifies only the column of a single table in the search conditions.
#4
Includes shared tables on which a LOCK TABLE statement that specifies IN EXCLUSIVE MODE is executed, and shared tables that perform searches using FOR UPDATE clauses.

(a) Example 7

[Figure]

Explanation
In the same SQL statement, shared tables S1 and S2 are searched.
  1. The back-end server used to access shared table S1 is BES2.
  2. When shared table S2 is accessed immediately after step 1, back-end server BES2, which was used to access shared table S1, is allocated.
(b) Example 8

[Figure]

Explanation
In the same SQL statement, row-partitioned table D1, row-partitioned table D2, and shared table S are searched.
  1. Row-partitioned table D1 is searched with the restriction that only one back-end server can search in a partitioning column. The back-end server used to access row-partitioned table D1 is BES2.
  2. Row-partitioned table D2 is searched with the restriction that only one back-end server can search in a partitioning column. The back-end server used to access row-partitioned table D2 is BES3.
  3. When shared table S is accessed immediately after steps 1 and 2, BES2 is randomly selected from among the back-end servers that were used to access row-partitioned tables D1 and D2, and is allocated (since allocation is random, it might be BES3 in some cases).
(c) Example 9

[Figure]

Explanation
In the same SQL statement, unpartitioned table N1, unpartitioned table N2, and shared table S are searched.
  1. The back-end server used to access unpartitioned table N1 is BES2.
  2. The back-end server used to access unpartitioned table N2 is BES3.
  3. When shared table S is accessed immediately after steps 1 and 2, BES2 is randomly selected from among the back-end servers used to access unpartitioned tables N1 and N2, and is allocated (since allocation is random, it might be BES3 in some cases).
(d) Example 10

[Figure]

Explanation
In the same SQL statement, row-partitioned table D1, row-partitioned table D2, and shared table S are searched.
  1. The back-end servers used to access row-partitioned table D1 are BES1 and BES2.
  2. The back-end servers used to access row-partitioned table D2 are BES2 and BES3.
  3. When shared table S is accessed immediately after steps 1 and 2, BES2 is randomly selected from among the back-end servers used to access row-partitioned tables D1 and D2, and is allocated (since allocation is random, it might be BES1 or BES3 in some cases).