ELEMENTIDCOMPUTE

The ELEMENTIDCOMPUTE function is only supported in TimesTen Scaleout. This function returns the id of the element to which the distribution key belongs.

SQL syntax

ELEMENTIDCOMPUTE (Expression [,...])

Parameters

ELEMENTIDCOMPUTE has the parameters:

Parameter Description

Expression

One or more expressions.

Description

  • The ELEMENTIDCOMPUTE SQL function accepts one or more expressions. This list of expressions represents a hash distribution key. The element id returned by this function is stable within the transaction. If the TimesTen Scaleout topology changes, the element id of the particular row may change since the row may be mapped to a different element.

  • You can use this function to predict into which element a particular distribution key is inserted.

  • ELEMENTIDCOMPUTE returns a TT_INTEGER data type.

Note:

If you have set K-safety to 2, ELEMENTIDCOMPUTE returns the id of one of the elements of the replica set. The value returned may not be the element id to which you are connected even though the data row maps to the local element id.

Examples

These examples illustrate the use of the ELEMENTIDCOMPUTE function:

This example, (the first example), invokes the ELEMENTIDCOMPUTE function to return the element id of one element in the replica set to which the cust_id distribution key belongs. The query also returns the cust_id, last_name, and first_name columns from the customers table where the cust_id is between 910 and 920. 

Command> SELECT ELEMENTIDCOMPUTE (cust_id), cust_id,last_name,first_name
         FROM customers WHERE cust_id BETWEEN 910 AND 920
         ORDER BY cust_id, last_name, first_name;
< 3, 910, Riley, Tessa >
< 1, 911, Riley, Rashad >
< 1, 912, Riley, Emma >
< 1, 913, Rivera, Erin >
< 1, 914, Roberts, Ava >
< 1, 915, Roberts, Lee >
< 3, 916, Roberts, Clint >
< 5, 917, Robertson, Faith >
< 3, 918, Robinson, Miguel >
< 3, 919, Robinson, Mozell >
< 5, 920, Rodgers, Darryl >
11 rows found.

In this example, (the second example), elements 1 and 2 are in the same replica set. This example shows a connection to element 1 and a second connection to the replica (element 2). When connected to element 2, the value returned from ELEMENTIDCOMPUTE is not element 2, even though the data row maps to element 2. This example illustrates that the value returned may not be the element id to which the application is connected. 

Command> SELECT elementId# FROM dual;
< 1 >
1 row found.

Command> SELECT FIRST 5 ELEMENTIDCOMPUTE (cust_id), elementid# FROM customers;
< 1, 1 >
< 1, 1 >
< 1, 1 >
< 1, 1 >
< 1, 1 >
5 rows found.

Command> SELECT elementId# FROM dual;
< 2 >
1 row found.

Command> SELECT FIRST 5 ELEMENTIDCOMPUTE (cust_id), elementid# FROM customers;
< 1, 2 >
< 1, 2 >
< 1, 2 >
< 1, 2 >
< 1, 2 >
5 rows found.

This example, (the third example), illustrates how to use the ELEMENTIDCOMPUTE function to predict into which element a particular row of data gets inserted. In the customers table, there are 1 to 1000 cust_id values. Predict into which element cust_id 1003, 2000 or 2400 is inserted. For example, a cust_id value of 1003, if inserted into the customers table, is predicted to reside in the replica set containing element 5. 

Command> SELECT FIRST 1 ELEMENTIDCOMPUTE (CAST (? AS NUMBER)) FROM customers;
 
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.
 
Enter Parameter 1 '_QMARK_1' (NUMBER) > 1003
< 5 >
1 row found.

A cust_id value of 2000 if inserted into the customers table is predicted to reside in the replica set containing element 3. 

Command> SELECT FIRST 1 ELEMENTIDCOMPUTE (CAST (? AS NUMBER)) FROM customers;
 
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.
 
Enter Parameter 1 '_QMARK_1' (NUMBER) > 2000
< 3 >
1 row found.

A cust_id value of 2400 if inserted into the customers table is predicted to reside in the replica set containing element 1. 

Command> SELECT FIRST 1 ELEMENTIDCOMPUTE (CAST (? AS NUMBER)) FROM customers;
 
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.
 
Enter Parameter 1 '_QMARK_1' (NUMBER) > 2400
< 1 >
1 row found.