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.
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)
Item | Search conditions | Back-end server allocated |
---|---|---|
1 | One of the following conditions is met:
| HiRDB allocates an updatable back-end server. |
2 | Shared 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. |
3 | Row-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. |
4 | Unpartitioned 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. |
5 | Row-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. |
6 | There 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. |
7 | Cases other than search conditions 1 through 6 | HiRDB randomly allocates a back-end server. For an example of the how back-end servers are allocated in this case, see Example 6. |
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)
Item | Search conditions | Back-end server allocated |
---|---|---|
1 | One of the following conditions is met:
| HiRDB allocates an updatable back-end server. |
2 | Shared 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. |
3 | Row-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. |
4 | Unpartitioned 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. |
5 | Row-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. |