Pseudocolumns in TimesTen Scaleout
Pseudocolumns are not actual columns in a table but behave like columns. A pseudocolumn is an assigned value used in the same context as a column, but is not stored.
You can perform select operations, but you cannot perform insert or update operations on a pseudocolumn.
Pseudocolumns in TimesTen Scaleout:
-
elementId#
: An element stores a portion of the database. Use theelementId#
pseudocolumn to determine the element from which you accessed the row. This pseudocolumn returns aNOT
NULL
TT_INTEGER
data type. -
replicaSetId#
: Use this pseudocolumn to determine the replica set in which the row is stored. This pseudocolumn returns aNOT
NULL
TT_INTEGER
data type. -
dataspaceId#
: Use this pseudocolumn to determine the data space in which the copy of the row resides. This pseudocolumn returns aNOT
NULL
TT_INTEGER
data type.
Note:
-
For DML operations, use
replicaSetId#
instead ofelementId#
. This is also true forSELECT...FOR UPDATE
. -
For
SELECT
operations, usereplicaSetId#
unless you want to select rows from a specific element. In this case, use theTT_GridQueryExec (GLOBAL)
optimizer hint with yourSELECT
statement. See "TT_GridQueryExec Optimizer Hint" for more information.
These sections illustrate how to use pseudocolumns:
Using Pseudocolumns to Locate Data
This example illustrates how to use pseudocolumns to locate data. It determines the element to which the application is connected. It then issues a query on the customers
table and returns the elementId#
, replicaSetId#
, and dataspaceId#
where the data is located.
Command> SELECT elementid# FROM dual; < 1 > 1 row found. Command> SELECT elementId#,replicasetid#,dataspaceId#,cust_id,last_name,first_name FROM customers WHERE cust_id BETWEEN 910 AND 920 ORDER BY cust_id, last_name, first_name; < 3, 2, 1, 910, Riley, Tessa > < 1, 1, 1, 911, Riley, Rashad > < 1, 1, 1, 912, Riley, Emma > < 1, 1, 1, 913, Rivera, Erin > < 1, 1, 1, 914, Roberts, Ava > < 1, 1, 1, 915, Roberts, Lee > < 3, 2, 1, 916, Roberts, Clint > < 5, 3, 1, 917, Robertson, Faith > < 3, 2, 1, 918, Robinson, Miguel > < 3, 2, 1, 919, Robinson, Mozell > < 5, 3, 1, 920, Rodgers, Darryl > 11 rows found.
Working With Pseudocolumns and Duplicate Tables
This example illustrates how to use pseudocolumns with duplicate tables. It 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 from a connection that has been connected to element 1. The example returns the elementId#
, replicasetId#
, and dataspaceId#
so the location of the data can be determined. The example repeats the same query from a connection that has been connected to element 2. The example illustrates the data is located on the element to which the application is connected and thus is present in every element of 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 *) Command> SELECT elementId# FROM dual; < 1 > 1 row found. Command> SELECT elementId#,replicaSetId#,dataspaceId#, * FROM account_status; < 1, 1, 1, 10, Active - Account is in good standing > < 1, 1, 1, 20, Pending - Payment is being processed > < 1, 1, 1, 30, Grace - Automatic payment did not process successfully > < 1, 1, 1, 40, Suspend - Account is in process of being disconnected > < 1, 1, 1, 50, Disconnected - You can no longer make calls or receive calls > 5 rows found.
Issue the same query from a connection to element 2.
Command> SELECT elementid# from dual; < 2 > 1 row found. Command> SELECT elementId#,replicaSetId#,dataspaceId#, * FROM account_status; < 2, 1, 2, 10, Active - Account is in good standing > < 2, 1, 2, 20, Pending - Payment is being processed > < 2, 1, 2, 30, Grace - Automatic payment did not process successfully > < 2, 1, 2, 40, Suspend - Account is in process of being disconnected > < 2, 1, 2, 50, Disconnected - You can no longer make calls or receive calls > 5 rows found.
Using Pseudocolumns to Locate a Local Element
This example illustrates how to use pseudocolumns to return information for the element to which the application is connected. It assumes you have created a grid with six data instances and K-safety set to 2. The purpose of this example is to show you how to identify the element id, replica set, and data space group for the element to which the application is connected.
In this example, your connection is connected to element 1. Selecting from the dual
table returns the element id, replica set id, and data space id of the current local connection. In this example, element 1 is in replica set 1 and data space 1.
Command> SELECT elementId#,replicaSetId#,dataspaceId# FROM dual; < 1, 1, 1 > 1 row found.
In this example, your connection is connected to element 3. Element 3 is in replica set 2 and in data space 1.
Command> SELECT elementId#,replicaSetId#,dataspaceId# FROM dual; < 3, 2, 1 > 1 row found.
Displaying the Element Id Associated With an Instance
This example illustrates how to use the ttGridAdmin
dbStatus
-element
command to display the element id associated with each instance. This command also gives the status of each element. (You must issue this command from the active management instance and you must issue it as the instance administrator.)
See Monitor the Status of a Database (dbStatus) in the Oracle TimesTen In-Memory Database Reference for more information.
$ ttGridAdmin dbStatus -element Database database1 element level status as of Thu Apr 5 12:57:44 PDT 2018 Host Instance Elem Status Date/Time of Event Message --------------- -------- ---- ------ ------------------- ------- host1 instance1 1 opened 2018-04-05 11:15:33 host2 instance2 2 opened 2018-04-05 11:15:33 host3 instance3 3 opened 2018-04-05 11:15:33 host4 instance4 4 opened 2018-04-05 11:15:33 host5 instance5 5 opened 2018-04-05 11:15:33 host6 instance6 6 opened 2018-04-05 11:15:33