Using Pseudocolumns
A pseudocolumn is an assigned value used in the same context as a column, but is not stored. Pseudocolumns are not actual columns in a table but behave like columns. You can perform select operations, but you cannot perform insert, update, or delete operations on a pseudocolumn.
Use the replicaSetId#
pseudocolumn to determine the replica set in which the row is stored. This pseudocolumn returns a NOT
NULL
TT_INTEGER
data type.
See Pseudocolumns in TimesTen Scaleout in Oracle TimesTen In-Memory Database SQL Reference for information on the additional pseudocolumns supported in TimesTen Scaleout.
Examples include:
Use replicaSetId# to Locate Data
This example issues a query on the customers
table, returning the replica set in which the data is stored (as determined by replicaSetId#)
.
Command> SELECT replicasetid#, cust_id,last_name,first_name
FROM customers WHERE cust_id BETWEEN 910 AND 920
ORDER BY cust_id, last_name, first_name;
< 2, 910, Riley, Tessa >
< 1, 911, Riley, Rashad >
< 1, 912, Riley, Emma >
< 1, 913, Rivera, Erin >
< 1, 914, Roberts, Ava >
< 1, 915, Roberts, Lee >
< 2, 916, Roberts, Clint >
< 3, 917, Robertson, Faith >
< 2, 918, Robinson, Miguel >
< 2, 919, Robinson, Mozell >
< 3, 920, Rodgers, Darryl >
11 rows found.
Use replicaSetId# with a Table That Has a Duplicate Distribution Scheme
This example first uses the ttIsql
describe
command on the account_status
table to validate the table has a duplicate distribution scheme. The example then issues a query to return the replicasetId#
. The example then repeats the same query from a different connection. The example shows that the data returned is located on the replica set to which the application is connected and thus is present in every element in the database (duplicate distribution scheme).
Command> describe account_status;
Table SAMPLEUSER.ACCOUNT_STATUS:
Columns:
*STATUS NUMBER (2) NOT NULL
DESCRIPTION VARCHAR2 (100) INLINE NOT NULL
DUPLICATE
1 table found.
(primary key columns are indicated with *)
Query the dual
table to return the replica set to which the application is connected. In this example, the replica set is 1.
Command> SELECT replicaSetId# FROM dual;
< 1 >
1 row found.
Command> SELECT replicaSetId#,* FROM account_status;
< 1, 10, Active - Account is in good standing >
< 1, 20, Pending - Payment is being processed >
< 1, 30, Grace - Automatic payment did not process successfully >
< 1, 40, Suspend - Account is in process of being disconnected >
< 1, 50, Disconnected - You can no longer make calls or receive calls >
5 rows found.
Issue a second query from a different ttIsql
session running on a different data instance:
Command> SELECT elementid# from dual;
< 6>
1 row found.
Command> SELECT replicaSetId#, * FROM account_status;
< 3, 10, Active - Account is in good standing >
< 3, 20, Pending - Payment is being processed >
< 3, 30, Grace - Automatic payment did not process successfully >
< 3, 40, Suspend - Account is in process of being disconnected >
< 3, 50, Disconnected - You can no longer make calls or receive calls >
5 rows found.