Expressions are used for the following purposes:
The select list of the INSERT...SELECT
statement
A condition of the WHERE
clause and the HAVING
clause
The GROUP BY
and ORDER BY
clauses
The following sections describe expressions in TimesTen:
An expression specifies a value to be used in a SQL operation.
An expression can consist of a primary or several primaries connected by arithmetic operators, comparison operators, string or binary operators, bit operators or any of the functions described in Chapter 4, "Functions". A primary is a signed or unsigned value derived from one of the items listed in the SQL syntax.
{ColumnName | ROWID | {? | :DynamicParameter} | Function | Constant | (Expression)}
Or:
[[+ |-] {ColumnName | SYSDATE | TT_SYSDATE|GETDATE() | {? | :DynamicParameter} | Function | Constant | {~ | + | -} Expression}] [...]
Or:
Expression1 [& | | | ^ | + | / | * | - ] Expression2
Or:
Expression1 | | Expression2
Or:
Expression
Component | Description |
---|---|
+, – |
Unary plus and unary minus
Unary minus changes the sign of the primary. The default is to leave the sign unchanged. |
ColumnName |
Name of a column from which a value is to be taken
See Chapter 2, "Names, Namespace and Parameters" for more information. |
ROWID |
Unique ID for each row stored in a table
The rowid value can be retrieved through the |
?
|
A placeholder for a dynamic parameter
The value of the dynamic parameter is supplied at runtime. |
Function |
A computed value
See Chapter 4, "Functions" for more information. |
Constant |
A specific value
See "Constants". |
( Expression ) |
Any expression enclosed in parentheses |
Expression1
|
The specified expressions
|
* |
Multiplies two primaries |
/ |
Divides two primaries |
+ |
Adds two primaries |
– |
Subtracts two primaries |
& |
Bitwise AND of the two operands
Sets a bit to 1 if and only if both of the corresponding bits in |
| |
Bitwise OR of the two operands
Sets a bit to 1 if one or both of the corresponding bits in |
~ |
Bitwise NOT of the operand
Takes only one |
^ |
Exclusive OR of the two operands
Sets the bit to 1 where the corresponding bits in its |
|| |
Concatenate operator
Concatenates |
Arithmetic operators can be used between numeric values. See "Numeric data types".
Arithmetic operators can also be used between datetime values and interval types. The result of a datetime expression is either a datetime data type or an interval data type.
Arithmetic operators cannot be applied to string values.
Elements in an expression are evaluated in the following order:
Functions and expressions in parentheses
Unary pluses and minuses
The *
and /
operations
The +
and –
operations
Elements of equal precedence are evaluated in left-to-right order
You can enclose expressions in parentheses to control the order of their evaluation. An example follows.
10 * 2 – 1 = 19 but 10 * (2 – 1) = 10
Type conversion, truncation, underflow, or overflow can occur when some expressions are evaluated. See Chapter 1, "Data Types" for more information.
If either operand in a numeric expression is NULL
, the result is NULL
.
Since NVL
takes two parameters, both designated as an "expression", TimesTen does not permit NULL
in either position. If there is a NULL
value in an expression, comparison operators and other predicates evaluate to NULL
. See Chapter 5, "Search Conditions" for more information on evaluation of comparison operators and predicates containing NULL
values. TimesTen permits inserting NULL
, but in general INSERT
takes only specific values, and not general expressions.
The query optimizer and execution engine permit multiple rowid lookups when a predicate specifies a disjunct of rowid equalities or uses IN
. For example, multiple fast rowid lookups are executed for:
WHERE ROWID = :v1 OR ROWID = :v2
Or equivalently:
WHERE ROWID IN (:v1, :v2)
The ?
or :
DynamicParameter
can be used as a dynamic parameter in an expression.
This example shows a dynamic parameter in the WHERE
clause of any SELECT
statement:
SELECT * FROM purchasing.orders WHERE partnumber = ? AND ordernumber > ? ORDER BY ordernumber;
This example shows a dynamic parameter in the WHERE
and SET
clauses of an UPDATE
statement:
UPDATE purchasing.parts SET salesprice = :dynamicparameter1 WHERE partnumber = :dynamicparameter2;
This example shows a dynamic parameter in the WHERE
clause of a DELETE
statement:
DELETE FROM purchasing.orderitems WHERE itemnumber BETWEEN ? AND ?;
This example shows a dynamic parameter in the VALUES
clause of an INSERT
statement. In this example, both ?
and :
dynamicparameter
are used where :dynamicparameter1
corresponds to both the second and fourth columns of the purchasing.orderitems
table. Therefore, only four distinct dynamic parameters need to be passed to this expression with the second parameter used for both the second and fourth columns.
INSERT INTO purchasing.orderitems VALUES (?,:dynamicparameter1, :dynamicparameter2, :dynamicparameter1,?);
This example demonstrates that both ?
and :dynamicparameter
can be used in the same SQL statement and shows the semantic difference between repeating both types of dynamic parameters.
Following are examples of bitwise operators.
Command> SELECT 0x183D & 0x00FF FROM dual; < 003D > 1 row found. Command> SELECT ~255 FROM dual; < -256 > 1 row found. Command> SELECT 0x08 | 0x0F FROM dual; < 0F > 1 row found.
TimesTen supports subqueries in INSERT...SELECT
, CREATE VIEW
or UPDATE
statements and in the SET
clause of an UPDATE
statement, in a search condition and as a derived table. TimesTen supports table subqueries and scalar subqueries. TimesTen does not support row subqueries. A subquery can specify an aggregate with a HAVING
clause or joined table. It can also be correlated.
[NOT] EXISTS | [NOT] IN (Subquery) Expression {= | <> | > | >= | < | <= } [ANY | ALL] (Subquery) Expression [NOT] IN (ValueList | Subquery)
Where ValueList
is a list of constant expressions. Each constant expression specifies a constant value or an expression that evaluates to a constant value (such as a number, character string, or date). This includes support for bound values (?
or :DynamicParameter
), NULL
, and calls to functions that return constant values.
TimesTen supports queries with the characteristics listed in each section.
A subquery can appear in the WHERE
clause or HAVING
clause of any statement except one that creates a materialized view. Only one table subquery can be specified in a predicate. These predicates can be specified in a WHERE
or HAVING
clause, an OR
expression within a WHERE
or HAVING
clause, or an ON
clause of a joined table. They cannot be specified in a CASE
expression, a materialized view, or a HAVING
clause that uses the +
operator for outer joins.
A subquery can be specified in an EXISTS
or NOT EXISTS
predicate, a quantified predicate with ANY
or ALL
, or a comparison predicate. The allowed operators for both comparison and quantified predicates are: =
, <
, >
, <=
, >=
, <>
. The subquery cannot be connected to the outer query through a UNIQUE
or NOT UNIQUE
operator.
Only one subquery can be specified in a quantified or comparison predicate. Specify the subquery as either the right operand or the left operand of the predicate, but not both.
FIRST
NumRows
is not supported in subquery statements.
In a query specified in a quantified or comparison predicate, the underlying SELECT
must have a single expression in the select list. In a query specified in a comparison predicate, if the underlying select returns a single row, the return value is the select result. If the underlying select returns no row, the return value is NULL
. It is an error if the subquery returns multiple rows.
A scalar subquery returns a single value.
A nonverifiable scalar subquery has a predicate such that the optimizer cannot detect at compile time that the subquery returns at most one row for each row of the outer query. The subquery cannot be specified in an OR
expression.
Examples of supported subqueries for a list of customers having at least one unshipped order:
SELECT customers.name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE customers.id = orders.custid AND orders.status = 'unshipped'); SELECT customers.name FROM customers WHERE customers.id = ANY (SELECT orders.custid FROM orders WHERE orders.status = 'unshipped'); SELECT customers.name FROM customers WHERE customers.id IN (SELECT orders.custid FROM orders WHERE orders.status = 'unshipped');
In this example, list items are shipped on the same date as when they are ordered:
SELECT line_items.id FROM line_items WHERE line_items.ship_date = (SELECT orders.order_date FROM orders WHERE orders.id = line_items.order_id);
A constant is a literal value.
{IntegerValue | FloatValue |FloatingPointLiteral| FixedPointValue | 'CharacterString'| 'NationalCharacterString' | HexadecimalLiteral | 'DateString' | DateLiteral |'TimeString' | TimeLiteral | 'TimestampString' | TimestampLiteral | IntervalLiteral | BINARY_FLOAT_INFINITY | BINARY_DOUBLE_INFINITY | -BINARY_FLOAT_INFINITY | -BINARY_DOUBLE_INFINITY | BINARY_FLOAT_NAN | BINARY_DOUBLE_NAN }
A format model is a character literal that describes the format of datetime and numeric data stored in a character string. When you convert a character string into a date or number, a format model determines how TimesTen interprets the string.
This section covers the following format models:
Use number format models in the following functions:
In the TO_CHAR
function to translate a value of NUMBER
, BINARY_FLOAT
, or BINARY_DOUBLE
data type to VARCHAR2
data type.
In the TO_NUMBER
function to translate a value of CHAR
or VARCHAR2
data type to NUMBER
data type.
The default american_america
NLS language and territory setting is used.
A number format model is composed of one or more number format elements. The table lists the elements of a number format model. Negative return values automatically contain a leading negative sign and positive values automatically contain a leading space unless the format model contains the MI
, S
, or PR
format element.
Table 3-1 Number format elements
Element | Example | Description |
---|---|---|
|
|
Returns a comma in the specified position. You can specify multiple commas in a number format model. Restrictions:
|
|
|
Returns a decimal point, which is a period (.) in the specified position. Restriction: You can specify only one period in a format model. |
$ |
|
Returns value with leading dollar sign. |
|
|
Returns leading zeros. Returns trailing zeros. |
|
|
Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number. |
|
|
Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of zeros in the format model). |
|
|
Returns in the specified position the ISO currency symbol. |
|
|
Returns the decimal character in the specified position. The default is a period ( Restriction: You can specify only one decimal character in a number format model. |
|
|
Returns a value in scientific notation. |
|
|
Returns the group separator in the specified position. You can specify multiple group separators in a number format model. Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model. |
|
|
Returns the local currency symbol in the specified position. |
|
|
Returns negative value with a trailing minus sign ( Returns positive value with a trailing blank. Restriction: The |
|
|
Returns negative value in angle brackets ( Returns positive value with a leading and trailing blank. Restriction: The |
|
|
Returns a value as Roman numerals in uppercase. |
|
|
Returns a value as Roman numerals in lowercase. Value can be an integer between 1 and 3999. |
|
|
Returns negative value with a leading minus sign ( Returns positive value with a leading plus sign ( |
|
|
Returns negative value with a trailing minus sign ( Returns positive value with a trailing plus sign ( Restriction: The |
|
|
The text minimum number format model returns (in decimal output) the smallest number of characters possible. This element is case insensitive. The default is Restrictions:
|
|
|
Returns the euro or other dual currency symbol in the specified position. |
|
|
Returns a value multiplied by 10n (and if necessary, rounds it up), where |
|
|
Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then TimesTen rounds it to an integer. Restrictions:
|
Use datetime format models in the following functions:
In the TO_CHAR
, TO_DATE
, and TO_TIMESTAMP
functions to translate a character value that is in a format other than the default format for a datetime value.
In the TO_CHAR
function to translate a datetime value that is in a format other than the default format into a string.
The total length of a datetime format model cannot exceed 22 characters.
The default american_america
NLS language and territory setting are used.
A datetime format model is composed of one or more datetime format elements. Table 3-2, "Datetime format elements" shows the datatime format elements. In addition:
For input format models, the format elements cannot appear more than once, and format elements that represent similar information cannot be combined. For example, you cannot use the SYYYY
and the BC
format elements in the same format string.
For DATE
format elements, capitalization in a spelled-out word, abbreviation or Roman numeral, follows the capitalization in the corresponding format element. For example, the date format model DAY
produces capitalized words, like MONDAY
, whereas Day
produces Monday
, and day
produces monday
.
Table 3-2 Datetime format elements
Element | Description |
---|---|
|
Punctuation and quoted text are reproduced in the result. |
|
AD indicator with or without periods. |
|
Meridian indicator with or without periods. |
|
BC indicator with or without periods. |
|
Valid in Century:
For example, 2002 returns 21 and 2000 returns 20. |
|
Day of week (1-7). |
|
Name of day, padded with blanks to display width of widest name of day. |
|
Day of month (1-31). |
|
Day of year. |
|
Long date format. In the default Restriction: Specify this format only with the |
|
Short date format In the default Restriction: Specify this format only with the |
|
Abbreviated name of day. |
|
Valid in Fractional seconds. No radix character is printed. Use the |
|
Returns a value with no leading or no trailing blanks. |
|
Returns exact matching between the character data and the format model. |
|
Hour of day (1-12). |
|
Hour of day (0-23). |
|
Valid in Calendar week of year (1-52 or 1-53), as defined by the ISO 8601 standard.
|
|
Valid in 4-digit year of the year containing the calendar week, as defined by the ISO 8601 standard. |
|
Valid in Last 3, 2, or 1 digit(s) of the year containing the calendar week, as defined by the ISO 8601 standard. |
|
Julian day. This is the number of days since January 1, 4712 BC. Numbers specified with |
|
Minute (0-59). |
|
Month (01-12, where January = 01). |
|
Abbreviated name of month. |
|
Name of month padded with blanks to display width of the widest name of month. |
|
Meridian indicator with or without periods. |
|
Valid in Quarter of the year (1, 2, 3, 4). January = 1. |
|
Roman numeral month (I-XII. January = I). |
|
For 20th century dates in the 21st century using only two digits. |
|
Rounded year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as |
|
Second (0-59). |
|
Seconds past midnight (0-86399). |
|
Returns a value in the short time format. Restriction: Specify this format only with the |
|
Valid in Week of year (1-53) where week one starts on the first day of the year and continues to the seventh day of the year. |
|
Valid in Week of month (1-5) where week one starts on the first day of the month and ends on the seventh. |
|
Local radix character For example: 'HH:MI:SSXFF' |
|
Year with comma in this position |
|
Valid in Year, spelled out. |
|
4-digit year.
|
|
Last 3, 2, or 1 digit(s) of year. |
The table lists the format models you can use with the ROUND
and TRUNC
date functions and the units to which they round and truncate dates. The default model DD
returns the date rounded or truncated to the day with a time of midnight.
Format model | Rounding or truncating unit |
---|---|
CC
|
Century
If the last two digits of a four-digit year are between 01 and 99 (inclusive), then the century is one greater than the first two digits of that year. If the last two digits of a four-digit year are 00, then the century is the same as the first two digits of that year. For example, 2002 returns 21; 2000 returns 20. |
SYYYY
|
Year (rounds up on July 1) |
IYYY
|
ISO year |
Q |
Quarter (rounds up on the sixteenth day of the second month of the quarter) |
MONTH
|
Name of month (rounds up on the sixteenth day) |
WW |
Same day of the week as the first day of the year |
IW |
Same day of the week as the first day of the ISO week, which is Monday |
W |
Same day of the week as the first day of the month |
DDD
|
Day of year |
DAY
|
Starting day of the week |
HH
|
Hour |
MI |
Minute |
Use this format model when invoking the TO_CHAR
function to convert a datetime value of TT_TIMESTAMP
or TT_DATE
. In addition, use this format model when invoking the TO_CHAR
function to convert any numeric value other than NUMBER
or ORA_FLOAT
.
If a numeric value does not fit in the specified format, TimesTen truncates the value.
The format string cannot exceed 50 characters.
D
always results in a decimal point. Its value cannot be changed with an NLS parameter.
If a float with an absolute value less than 1e-126
or greater than 1e126
is specified as input to the TO_CHAR
function, TimesTen returns an error.
Format | Description |
---|---|
DD |
Day of month (1-31) |
MM |
Month (1-12) |
MON |
Month (three character prefix) |
MONTH |
Month (full name blank-padded to 9 characters) |
YYYY |
Year (four digits) |
Y , YYY |
Year (with comma as shown) |
YYY |
Year (last three digits) |
YY |
Year (last two digits) |
Y |
Year (last digit) |
Q |
Quarter |
HH |
Hour (1-12) |
HH12 |
Hour (1-12) |
HH24 |
Hour (0-23) |
MI |
Minute (0-59) |
SS |
Second (0-59) |
FF |
Fractions of a second to a precision of six digits |
FFn |
Fractions of a second to the precision specified by n |
AM |
Meridian indicator |
A.M. |
Meridian indicator |
PM |
Meridian indicator |
P.M. |
Meridian indicator |
- / , . ; : |
Punctuation to be output |
"text" | Text to be output |
9 |
Digit |
0 |
Leading or trailing zero |
. |
Decimal point |
, | Comma |
EEEE |
Scientific notation |
S |
Sign mode |
B |
Blank mode
If there are no digits, the string is filled with blanks. |
FM |
No-blank mode (fill mode)
If this element is used, trailing and leading spaces are suppressed. |
$ |
Leading dollar sign |
Specifies a conditional value. Both simple and searched case expressions are supported. The CASE
expression can be specified anywhere an expression can be specified and can be used as often as needed.
Instead of using a series of IF statements, the CASE
expression enables you to use a series of conditions that return the appropriate values when the conditions are met. With CASE
, you can simplify queries and write more efficient code.
The syntax for a searched CASE
expression is:
CASE {WHEN SearchCondition THEN Expression1}[…] [ELSE Expression2] END
The syntax for a simple CASE
expression is:
CASE Expression {WHEN CompExpression THEN Expression1}[…] [ELSE Expression2] END
CASE
has the parameters:
Parameter | Description |
---|---|
WHEN SearchCondition |
Specifies the search criteria. This clause cannot specify a subquery. |
WHEN CompExpression |
Specifies the operand to be compared. |
Expression | Specifies the first operand to be compared with each CompExpression . |
THEN Expression1 |
Specifies the resulting expression. |
ELSE Expression2 |
If condition is not met, specifies the resulting expression. If no ELSE clause is specified, TimesTen adds an ELSE NULL clause to the expression. |
To specify a searched CASE
statement that specifies the value of a color, use:
SELECT CASE WHEN color=1 THEN 'red' WHEN color=2 THEN 'blue' ELSE 'yellow' END FROM cars;
To specify a simple CASE
statement that specifies the value of a color, use the following.
SELECT CASE color WHEN 1 THEN 'red' WHEN 2 THEN 'blue' ELSE 'yellow' END FROM cars;
TimesTen assigns a unique id called a rowid to each row stored in a table. This rowid (called a ROWID
pseudocolumn) has data type ROWID
.
In TimesTen Scaleout, the ROWID
pseudocolumn has a different meaning than in TimesTen Classic.
In TimesTen Scaleout:
TimesTen Scaleout uses ROWID
to ensure uniqueness across all elements.
ROWID
is the identifier of a specific copy of a row. If that copy is not available because the element that has the copy is not available, then you cannot access the row by ROWID
. In this case, you should access the row by primary key.
Each copy of a row has different ROWID
values. This is true for a duplicate distribution scheme where K-safety is set to 1 and for all tables (no matter what the distribution scheme is) where K-safety is set to 2. In these cases, when using ROWID
based access, TimesTen Scaleout returns the value of the ROWID
in the first data space.
Applications should not store ROWID
values and try to use these values later.
ROWID
values may change if the location of the data changes (through data redistribution).
For information on the ttGridAdmin dbDistribute
command, see "Set or modify the distribution scheme of a database (dbDistribute)" in the Oracle TimesTen In-Memory Database Reference.
TimesTen Scaleout does not support the ROWID
data type.
In TimesTen Classic:
You can examine a rowid by querying the ROWID
pseudocolumn.
The ROWID
is a pseudocolumn (not an actual column) and thus does not require database space. You cannot update, index, or drop ROWID
.
The ROWID
value persists throughout the life of the table row.
ROWID
values persist through recovery, backup and restore operations. However copies of rows that are created as a result of replication or as a result of running ttMigrate
to migrate rows out of the database and then back into the database or running ttBulkCp
to copy rows out of the database and then back into the database have different ROWID
values than the original rows.
For TimesTen Scaleout, see "Understanding ROWID in data distribution" in Oracle TimesTen In-Memory Database Scaleout User's Guide.
For TimesTen Classic, see "Expression specification" for more information on rowids. See "ROWID data type" for more information about the ROWID
data type.
For each row returned by a query, the ROWNUM
pseudocolumn returns a number indicating the order in which the row was selected. The first row selected has a ROWNUM
of 1, the second a ROWNUM
of 2, and so on.
Use ROWNUM
to limit the number of rows returned by a query as in this example:
SELECT * FROM employees WHERE ROWNUM < 10;
The order in which rows are selected depends on the index used and the join order. If you specify an ORDER BY
clause, ROWNUM
is assigned before sorting. However, the presence of the ORDER BY
clause may change the index used and the join order. If the order of selected rows changes, the ROWNUM
value associated with each selected row could also change.
For example, the following query may return a different set of employees than the preceding query if a different index is used:
SELECT * FROM employees WHERE ROWNUM < 10 ORDER BY last_name;
Conditions testing for ROWNUM
values greater than a positive integer are always false. For example, the following query returns no rows:
SELECT * FROM employees WHERE ROWNUM > 1;
Use ROWNUM
to assign unique values to each row of a table. For example:
UPDATE my_table SET column1 = ROWNUM;
If your query contains either FIRST
NumRows
or ROWS
m
TO
n
, do not use ROWNUM
to restrict the number of rows returned. For example, the following query results in an error message:
SELECT FIRST 2 * FROM employees WHERE ROWNUM <1 ORDER BY employee_id; 2974: Using rownum to restrict number of rows returned cannot be combined with first N or rows M to N
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 the elementId#
pseudocolumn to determine the element from which you accessed the row. This pseudocolumn returns a NOT
NULL
TT_INTEGER
data type.
replicaSetId#
: Use this pseudocolumn to determine the replica set in which the row is stored. This pseudocolumn returns a NOT
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 a NOT
NULL
TT_INTEGER
data type.
Notes:
For DML operations, use replicaSetId#
instead of elementId#
. This is also true for SELECT...FOR UPDATE
.
For SELECT
operations, use replicaSetId#
unless you want to select rows from a specific element. In this case, use the TT_GridQueryExec (GLOBAL)
optimizer hint with your SELECT
statement. See "TT_GridQueryExec optimizer hint" for more information.
Examples include:
Example 3-2, "Working with pseudocolumns and duplicate tables"
Example 3-3, "Use pseudocolumns to locate the local element"
Example 3-4, "Display the element id associated with an instance"
Example 3-1 Use pseudocolumns to locate data
This example first 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.
Example 3-2 Working with pseudocolumns and duplicate tables
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 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.
Example 3-3 Use pseudocolumns to locate the local element
This example 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.
This example assumes 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.
This example assumes 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.
Example 3-4 Display the element id associated with an instance
You can also 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 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