This chapter describes how to use SQL to work with databases in TimesTen Scaleout. Topics include:
A database consists of elements. Each element stores a portion of your data. You manipulate and query the data in the database through SQL operations from any element. For example, you can use the CREATE USER statement to create a user in your database from any element. After TimesTen Scaleout creates the user, this user is available in all elements of the database. You can issue DDL and DML statements from any element which TimesTen Scaleout then applies to all elements in your database. You can issue a SELECT statement to execute a query that is prepared from one element and executed on other elements in the query with the result returned to the originating element.
Notes:
The syntax and semantics for SQL statements, functions, and the like are detailed in the Oracle TimesTen In-Memory Database SQL Reference.
See "Summary of SQL statements supported in TimesTen" in the Oracle TimesTen In-Memory Database SQL Reference for information on the SQL statements supported in TimesTen Scaleout.
Applications can use PL/SQL to access and manipulate data. Anonymous blocks are fully supported. PL/SQL is executed on the element to which the application is connected. SQL statements that are invoked from PL/SQL are executed across the grid as with any other SQL.
See the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for detailed information on PL/SQL and the "Examples Using TimesTen SQL in PL/SQL" chapter in the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for examples. For unsupported PL/SQL features, see Table 1-9, "TimesTen Classic features that are unsupported in TimesTen Scaleout" for information.
Tables are the objects used to define how to distribute data in your database. Each user-defined table has a defined distribution scheme. TimesTen Scaleout manages the distribution of data according to this defined distribution scheme. The distribution scheme defines how the rows of data in the table are distributed across the grid. The CREATE TABLE statement allows you to specify a distribution clause to define the distribution scheme for the table. When you create the table, it exists on every element of the database. Rows of data in the table exist on different elements of the database.
For detailed information on the syntax and semantics for creating, altering, and dropping tables, see "CREATE TABLE" in the Oracle TimesTen In-Memory Database SQL Reference. See "Data distribution" or "Defining table distribution schemes" for more information on defining distribution schemes.
You can alter tables in TimesTen Scaleout to change defaults or add and drop columns and constraints. However, you cannot change the distribution scheme unless the table is empty. In addition, you cannot drop a constraint that is named in the DISTRIBUTE BY REFERENCE clause. See "ALTER TABLE" in the Oracle TimesTen In-Memory Database SQL Reference for more information.
Table 7-1, "ALTER TABLE rules for distribution schemes" shows the rules associated with altering tables. Supporting examples follow.
Table 7-1 ALTER TABLE rules for distribution schemes
| ALTER statement | Comment |
|---|---|
CREATE TABLE t1 (c1 NUMBER, c2 VARCHAR2 (10)); ALTER TABLE t1 DISTRIBUTE BY HASH (c1); |
The operation succeeds if the table is empty. If the table is not empty, the operation fails because the distribution key cannot be changed on tables that are not empty. |
CREATE TABLE t1...CONSTRAINT fk1... DISTRIBUTE BY REFERENCE(fk1); ALTER TABLE t1 DROP CONSTRAINT(fk1); |
The operation fails. The foreign key is used to distribute the table. |
Examples include:
Example 7-1, "Use ALTER TABLE to add a primary key constraint"
Example 7-2, "Use ALTER TABLE to change the distribution key"
Example 7-1 Use ALTER TABLE to add a primary key constraint
This example creates the mytable table without a primary key or distribution clause. The table is distributed by hash on a hidden column. Then the ALTER TABLE statement is used to add a primary key constraint. The operation succeeds but the distribution key is not changed.
Command> CREATE TABLE mytable (col1 NUMBER NOT NULL, col2 VARCHAR2 (32));
Command> describe mytable;
Table SAMPLEUSER.MYTABLE:
Columns:
COL1 NUMBER NOT NULL
COL2 VARCHAR2 (32) INLINE
DISTRIBUTE BY HASH
1 table found.
(primary key columns are indicated with *)
Now alter the table to add the primary key. The operation succeeds. The distribution scheme and distribution key do not change.
Command> ALTER TABLE mytable ADD CONSTRAINT c1 PRIMARY KEY (col1);
Command> describe mytable;
Table SAMPLEUSER.MYTABLE:
Columns:
*COL1 NUMBER NOT NULL
COL2 VARCHAR2 (32) INLINE
DISTRIBUTE BY HASH
1 table found.
(primary key columns are indicated with *)
Example 7-2 Use ALTER TABLE to change the distribution key
This example shows that you can use the ALTER TABLE statement to change the distribution key, but only if the table is empty.
Command> CREATE TABLE mytable2 (col1 NUMBER NOT NULL, col2 VARCHAR2 (32)) DISTRIBUTE BY HASH (col1,col2);
Command> describe mytable2;
Table SAMPLEUSER.MYTABLE2:
Columns:
COL1 NUMBER NOT NULL
COL2 VARCHAR2 (32) INLINE
DISTRIBUTE BY HASH (COL1, COL2)
1 table found.
(primary key columns are indicated with *)
Use the ALTER TABLE statement to change the distribution key to col1. The operation succeeds because the table is empty.
Command> ALTER TABLE mytable2 DISTRIBUTE BY HASH (col1);
Command> describe mytable2;
Table SAMPLEUSER.MYTABLE2:
Columns:
COL1 NUMBER NOT NULL
COL2 VARCHAR2 (32) INLINE
DISTRIBUTE BY HASH (COL1)
1 table found.
(primary key columns are indicated with *)
Materialized views provide a second mechanism for distributing rows of data. (The first mechanism is tables.) See "Materialized views as a secondary form of distribution" for more information.
Materialized views are useful as global indexes to reduce or eliminate multi-element access (broadcasts). See "Understanding indexes" for more information.
Additional considerations:
Specify the DISTRIBUTE BY HASH distribution scheme. DISTRIBUTE BY REFERENCE and DUPLICATE clauses are not supported.
You must specify the DISTRIBUTE BY HASH clause and specify a set of columns in the DISTRIBUTE BY clause. Even if you distribute the materialized view by primary key, you must specify the primary key in the distribution clause.
Create a unique index for a materialized view if there is a unique column on the detail table, but only create it if the unique column is used as the distribution key for the materialized view. This will increase performance if the columns are frequently used in DML operations and queries.
The unique index columns and primary key columns must be a super set of the distribution key columns.
Consider creating a materialized view on a table along with any indexes after the table is populated to decrease the time it takes to populate the table.
See "CREATE MATERIALIZED VIEW" in the Oracle TimesTen In-Memory Database SQL Reference for more information on materialized views.
When you create an index, TimesTen Scaleout creates the index on all elements that are in the distribution map. TimesTen Scaleout populates each element's index with the rows that are stored on that element. When you drop an index, TimesTen Scaleout drops the index on all elements.
Indexed access that does not include all columns of the distribution key requires multi-element access (a broadcast across all elements). To avoid broadcasts, thereby optimizing index access, consider creating a materialized view and an index on the materialized view. Example 7-3, "Use materialized view as a global index" illustrates how to create a materialized view to optimize index access.
Note:
There are storage and overhead costs incurred for DML operations against the columns that are defined in the materialized view. This is a key tuning trade-off in TimesTen Scaleout.Example 7-3 Use materialized view as a global index
The accounts table is distributed by reference based on cust_id. To increase the performance of an UPDATE statement in which the distribution key is not used, consider creating a materialized view and distribute the materialized view on the column(s) not included in the distribution key (account_id, in this example). Then, create an index on the account_id column. Use the ttIsql set timing command to illustrate the difference in execution times.
Use the ttIsql describe command to describe the accounts table.
Command> SELECT status FROM accounts WHERE account_id = 500;
< 10 >
1 row found.
Command> autocommit off;
Command> set timing on;
Command> UPDATE accounts SET status=20 WHERE account_id=500;
1 row updated.
Execution time (SQLExecute) = 1.018369 seconds.
Command> set timing off;
Command> SELECT status FROM accounts WHERE account_id = 500;
< 20 >
1 row found.
Command> rollback;
Command> SELECT status FROM accounts WHERE account_id = 500;
< 10 >
1 row found.
Command> CREATE MATERIALIZED VIEW account_id_mv
DISTRIBUTE BY HASH (account_id) AS SELECT * FROM accounts;
1010000 rows materialized.
Command> CREATE UNIQUE HASH INDEX account_index_mv ON account_id_mv (account_id);
Command> autocommit off;
Command> set timing on;
Command> UPDATE accounts SET status=20 WHERE account_id=500;
1 row updated.
Execution time (SQLExecute) = 0.002601 seconds.
Command> set timing off;
Command> rollback;
Command> SELECT status FROM accounts WHERE account_id = 500;
< 10 >
1 row found.
The CREATE SEQUENCE statement creates a new sequence number generator that can subsequently be used by multiple users to generate unique BIGINT data types. As with materialized views and tables, once you create the sequence object, sequence values can be retrieved from any element of the database.
The values are retrieved from the sequence in blocks and cached in order to reduce the overhead of performing a globally coordinated update on the sequence object every time a value is retrieved. While the values returned from a sequence in TimesTen Scaleout are guaranteed to be unique, they are not guaranteed to be sequential.
The BATCH clause is specific to TimesTen Scaleout. The batch value configures the range of unique sequence values stored in the element. Each element has its own batch. An element will get a new batch when its local batch is consumed. There is one element that owns the sequence and is responsible for allocating batch sequence blocks to other elements.
Sequence values are unique, but across elements the values might not be returned in monotonic order. Within a single element, sequence values are in monotonic order. But over time, across elements, sequence values are not returned monotonically. However, the monotonic property is guaranteed within an element.
If your application records events and tags each event with a sequence value, the application cannot assume that event 100, for example, happened after event 80. If your application needs to make this assumption, then set BATCH to 1. However, there is substantial communication overhead if you set BATCH to 1.
In summary, unless the BATCH value is set to 1, the order of sequence values is not guaranteed to be maintained across all elements. However, no matter what the batch value is, the uniqueness of the sequence value is guaranteed to be maintained across all elements. In addition, the order of sequence values is guaranteed to be maintained within an element.
You can change the default batch value of an existing sequence by issuing the ALTER SEQUENCE statement. The batch value is the only alterable clause. See "CREATE SEQUENCE" and "ALTER SEQUENCE" in the Oracle TimesTen In-Memory Database SQL Reference for more information. Use the DROP SEQUENCE statement to drop a sequence. See "DROP SEQUENCE" in the Oracle TimesTen In-Memory Database SQL Reference for information on dropping a sequence.
Deciding what to set for the batch value depends on these considerations:
If you set the value to 1, sequence values are issued in monotonic order, no matter how many elements exist. However, there is substantial communication overhead with a value of 1, which results in a detrimental impact on performance. Unless absolutely necessary, do not set the value to 1 as it will directly impact the performance of your system.
If you set the value greater than 1, unique sequence values are not issued in strict order across all elements. If your connection retrieves multiple values from a sequence, there is no guarantee that the values will be consecutive or contiguous. If multiple connections retrieve values from a sequence, there may be gaps in the range of values retrieved.
You should consider setting batch to a high value to avoid excessive communication among elements (unless it is necessary to set the batch value to 1 for the proper functioning of your application).
The unique sequence value within the batch boundary cannot be greater than MAXVALUE. For example, if a sequence increments by 1, has a batch value of 3, and a maximum value of 5, the first batch includes 1, 2, and 3. The second batch includes 4 and 5 only.
The batch value must be greater or equal to the cache value.
If you do not specify a batch value, the default is 10 million. Each element starts with its own set of 10 million values. If the 10 million values are used up, the element gets 10 million more. The minimum and maximum values and the number of unique values are determined by the MINVALUE, MAXVALUE, and INCREMENT BY values.
Each element in a replica set has different batches.
Examples of batch assignment:
Example 7-4, "Illustrate batch assignment for three elements"
Example 7-5, "Illustrate a second batch assignment for three elements"
Example 7-4 Illustrate batch assignment for three elements
This example creates the myseq sequence with a batch value of 100. Then, from the connection that is connected to element 1, the example issues a SELECT...NEXTVAL query. The example then issues a second and third SELECT...NEXTVAL query from the connection that is connected to element 2 and the connection that is connected to element 3 respectively. The example illustrates the allocation of batch assignment for each element. In this example:
Element 1 receives a batch of 1-100.
Element 2 receives a batch of 101-200.
Element 3 receives a batch of 201-300.
From the connection that is connected to element 1 (demonstrated by SELECT elementId# FROM dual), create the myseq sequence specifying a batch value of 100. Then, issue a SELECT...NEXTVAL query. Observe the value 1 is returned.
Command> SELECT elementId# FROM dual; < 1 > 1 row found. Command> CREATE SEQUENCE myseq BATCH 100; Command> SELECT myseq.NEXTVAL FROM dual; < 1 > 1 row found.
From the connection that is connected to element 2, first verify the connection to element 2, then issue a SELECT...NEXTVAL query. Observe the value 101 is returned.
Command> SELECT elementId# FROM dual; < 2 > 1 row found. Command> SELECT myseq.NEXTVAL FROM dual; < 101 > 1 row found.
From the connection that is connected to element 3, first verify the connection to element 3, then issue a SELECT...NEXTVAL query. Observe the value 201 is returned.
Command> SELECT elementId# FROM dual; < 3 > 1 row found. Command> SELECT myseq.NEXTVAL FROM dual; < 201 > 1 row found.
Example 7-5 Illustrate a second batch assignment for three elements
This example creates the myseq2 sequence with a batch value of 100. Then, from the connection that is connected to element 1, the example issues a SELECT...NEXTVAL query. The example then issues a second and third SELECT...NEXTVAL query from the connection that is connected to element 3 and the connection that is connected to element 2 respectively. The example illustrates the allocation of batch assignment for each element. In this example:
Element 1 receives a batch of 1-100.
Element 3 receives a batch of 101-200.
Element 2 receives a batch of 201-300.
From the connection that is connected to element 1 (demonstrated by SELECT elementId# FROM dual), create the myseq2 sequence specifying a batch value of 100. Then, issue a SELECT...NEXTVAL query. Observe the value 1 is returned.
Command> SELECT elementId# FROM dual; < 1 > 1 row found. Command> CREATE SEQUENCE myseq2 BATCH 100; Command> SELECT myseq2.NEXTVAL FROM dual; < 1 > 1 row found.
From the connection that is connected to element 3, first verify the connection to element 3, then issue a SELECT...NEXTVAL query. Observe the value 101 is returned.
Command> SELECT elementId# FROM dual; < 3 > 1 row found. Command> SELECT myseq2.NEXTVAL FROM dual; < 101 > 1 row found.
From the connection that is connected to element 2, first verify the connection to element 2, then issue a SELECT...NEXTVAL query. Observe the value 201 is returned.
Command> SELECT elementId# FROM dual; < 2 > 1 row found. Command> SELECT myseq2.NEXTVAL FROM dual; < 201 > 1 row found.
TimesTen Scaleout supports the INSERT, DELETE, and UPDATE, and SELECT DML operations. The MERGE operation is not supported.
All data in all elements is accessible from everywhere. You can query or modify data in any or all elements. Transactions obey ACID rules. TimesTen Scaleout provides read committed semantics for isolation level. Readers do not block writers and writers do not block readers.
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 the Oracle TimesTen In-Memory Database SQL Reference for information on the additional pseudocolumns supported in TimesTen Scaleout.
Examples include:
Example 7-6 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.
Example 7-7 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.
The TT_CommitDMLOnSuccess hint is used to enable or disable a commit operation as part of DML execution. You can specify the hint at the connection level or at the statement level.
While using this hint (TT_CommitDMLOnSuccess set to 1):
At statement level, if a statement encounters an error while executing, the transaction remains active and the database consistent.
For transactions that impact a single replica set, the commit operation uses a one-phase commit instead of a two-phase commit protocol.
There is no difference in performance if you set autocommit to 1 or if you set the TT_CommitDMLOnSuccess hint to 1.
See "TT_CommitDMLOnSuccess optimizer hint" in the Oracle TimesTen In-Memory Database SQL Reference for detailed information.
The TimesTen query optimizer is a cost-based optimizer that determines the most efficient way to execute a given query by considering possible query plans. A query plan in TimesTen Scaleout is affected by the distribution scheme and the distribution keys of a hash distribution scheme as well as the column and table statistics, the presence or absence of indexes, the volume of data, the number of unique values, and the selectivity of predicates. You can manually examine a query plan by running the ttIsql explain command. See "The TimesTen Query Optimizer" in the Oracle TimesTen In-Memory Database Operations Guide for more information.
You can use optimizer hints to influence the execution plan generated by the optimizer. There are two optimizer hints that are specific to TimesTen Scaleout. These hints are valid at the statement and the connection levels. At the statement level, the hints are valid for SELECT statements only:
See "Optimizer hints supported in TimesTen Scaleout only" in the Oracle TimesTen In-Memory Database SQL Reference for information on the optimizer hints specific to TimesTen Scaleout. See "Use optimizer hints to modify the execution plan" in the Oracle TimesTen In-Memory Database Operations Guide for more information on all optimizer hints.
The TT_GridQueryExec optimizer hint enables you to specify whether the query should return data from the local element or from all elements, including the elements in a replica set when K-safety is set to 2.
If you do not specify this hint, the query is executed in one logical data space. It is neither local nor global. Exactly one full copy of the data is used to compute the query.
Valid options for this hint are LOCAL and GLOBAL:
LOCAL: TimesTen Scaleout executes the queries in the local element only. Data is retrieved locally from the element to which you are connected. If the local element does not have a full copy of the data, TimesTen Scaleout returns partial results.
GLOBAL: TimesTen Scaleout retrieves data from all elements, including copies of the rows from all tables from all replica sets to generate the results. This results in duplicate data returned if K-safety is set to 2 or if tables have a duplicate distribution scheme.
As with all queries, the element that you are directly connected to and issue the SQL query from prepares the query and sends it to all other elements in the grid. The request is executed on elements that are up and the results are reported locally on the connected element.
See "TT_GridQueryExec optimizer hint" in the Oracle TimesTen In-Memory Database SQL Reference for information on the syntax and semantics for this hint.
The distribution scheme is a determining factor in the number of rows returned. For example, Table 7-2 shows the number of rows used in query for the three distribution schemes. k represents the number of copies (k=2 in our example), e represents one element from each replica set (e=3 in our example), and r represents the number of rows in the table.
Table 7-2 TT_GridQueryExec optimizer hint
| Option | Table Type | Number of rows used in query |
|---|---|---|
|
|
Duplicate distribution scheme table Distributed by hash table Distributed by reference table |
r r/e (Assumes uniform distribution) r/e (Assumes uniform distribution) |
|
|
Duplicate distribution scheme table Distributed by hash table Distributed by reference table |
e*k*r k*r k*r |
Examples include:
Example 7-8, "Use TT_GridQueryExec on a hash distribution scheme table"
Example 7-9, "Use TT_GridQueryExec on a duplicate distribution scheme table"
Example 7-10, "Use TT_GridQueryExec on a reference distribution scheme table"
Note:
Reads do not get a distributed lock and return committed data. For the examples that use theTT_GridQueryExec(GLOBAL) optimizer hint, if a write to a replica set happens between the reads to its replicas, it is possible that the count will not match for all replicas. This is expected behavior because each replica is afforded read committed isolation.Example 7-8 Use TT_GridQueryExec on a hash distribution scheme table
This example uses the ttIsql describe command on the customers table to illustrate the table is distributed by hash. The example executes a SELECT COUNT (*) query on the customers table to return the number of rows in the table (1000). From the connection that is connected to element 4, the example uses the TT_GridQueryExec (Local) and (Global) optimizer hints to return the number of rows. The rows returned differ based on whether Local or Global was specified in the TT_GridQueryExec hint.
Command> describe customers;
Table SAMPLEUSER.CUSTOMERS:
Columns:
*CUST_ID NUMBER (10) NOT NULL
FIRST_NAME VARCHAR2 (30) INLINE NOT NULL
LAST_NAME VARCHAR2 (30) INLINE NOT NULL
ADDR1 VARCHAR2 (64) INLINE
ADDR2 VARCHAR2 (64) INLINE
ZIPCODE VARCHAR2 (5) INLINE
MEMBER_SINCE DATE NOT NULL
DISTRIBUTE BY HASH (CUST_ID)
1 table found.
(primary key columns are indicated with *)
Command> SELECT COUNT (*) FROM customers;
< 1000 >
1 row found.
Issue a SELECT elementId# FROM dual query to determine the local element connection (4).
Command> SELECT elementId# FROM dual; < 4 > 1 row found.
From this connection, issue a SELECT query supplying the TT_GridQueryExec(LOCAL) optimizer hint. Expect approximately 333 rows to be returned (1000/3).
Command> SELECT /*+TT_GridQueryExec(LOCAL)*/ COUNT (*), elementId#
FROM customers GROUP BY elementId#;
< 326, 4 >
1 row found.
Now issue a SELECT query supplying the TT_GridQueryExec(GLOBAL) optimizer hint. Expect 2000 rows returned (k=2 * r=1000 = 2000). Validate the results by using the SUM function to calculate the total rows returned for all 6 elements.
Command> SELECT /*+TT_GridQueryExec(GLOBAL)*/ COUNT (*), elementId#
FROM customers GROUP BY elementId#
ORDER BY elementId#;
< 338, 1 >
< 338, 2 >
< 326, 3 >
< 326, 4 >
< 336, 5 >
< 336, 6 >
6 rows found.
Command> SELECT SUM (338+338+326+326+336+336) FROM dual;
< 2000 >
1 row found.
Validate the total count using the TT_GridQueryExec(GLOBAL) hint.
Command> SELECT/*+TT_GridQueryExec(GLOBAL)*/ COUNT(*) FROM customers; < 2000 > 1 row found.
Example 7-9 Use TT_GridQueryExec on a duplicate distribution scheme table
This example uses the ttIsql describe command on the account_status table to illustrate the table is a duplicate distribution scheme. The example executes a SELECT COUNT (*) query on the account_status table to return the number of rows in the table (5). From the connection that is connected to element 2, the example uses the TT_GridQueryExec (Local) and (Global) optimizer hints to return the number of rows. The rows return differ based on whether Local or Global was specified in the TT_GridQueryExec hint.
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 count (*) FROM account_status;
< 5 >
1 row found.
Command> SELECT elementId# FROM dual;
< 2 >
1 row found.
Issue a SELECT query supplying the TT_GridQueryExec(LOCAL) optimizer hint. Expect approximately 5 rows to be returned (r = 5).
Command> SELECT /*+TT_GridQueryExec(LOCAL)*/ COUNT (*),elementId#
FROM account_status GROUP BY elementId#;
< 5, 2 >
1 row found.
Now issue a SELECT query supplying the TT_GridQueryExec(GLOBAL) optimizer hint. Expect 30 rows returned (e=3 *k=2 * r=5= 30).
Command> SELECT /*+TT_GridQueryExec(GLOBAL)*/ COUNT (*),elementId#
FROM account_status GROUP BY elementId#
ORDER BY elementId#;
< 5, 1 >
< 5, 2 >
< 5, 3 >
< 5, 4 >
< 5, 5 >
< 5, 6 >
6 rows found.
Validate the total count using the TT_GridQueryExec(GLOBAL) hint.
Command> SELECT /*+TT_GridQueryExec(GLOBAL)*/ COUNT (*) FROM account_status; < 30 > 1 row found.
Example 7-10 Use TT_GridQueryExec on a reference distribution scheme table
This example uses the ttIsql describe command on the accounts table to illustrate the table is distributed by reference. The example executes a SELECT COUNT (*) query on the accounts table to return the number of rows in the table (1010). From the connection that is connected to element 1, the example uses the TT_GridQueryExec (Local) and (Global) optimizer hint to return the number of rows. The rows returned differ based on whether Local or Global was specified in the TT_GridQueryExec hint.
Command> describe accounts;
Table SAMPLEUSER.ACCOUNTS:
Columns:
*ACCOUNT_ID NUMBER (10) NOT NULL
PHONE VARCHAR2 (15) INLINE NOT NULL
ACCOUNT_TYPE CHAR (1) NOT NULL
STATUS NUMBER (2) NOT NULL
CURRENT_BALANCE NUMBER (10,2) NOT NULL
PREV_BALANCE NUMBER (10,2) NOT NULL
DATE_CREATED DATE NOT NULL
CUST_ID NUMBER (10) NOT NULL
DISTRIBUTE BY REFERENCE (FK_CUSTOMER)
1 table found.
(primary key columns are indicated with *)
Command> SELECT COUNT (*) FROM accounts;
< 1010 >
1 row found.
Command> SELECT elementId# FROM dual;
< 1 >
1 row found.
Issue a SELECT query supplying the TT_GridQueryExec(LOCAL) optimizer hint. Expect approximately 336 rows to be returned (1010/3).
Command> SELECT /*+TT_GridQueryExec(LOCAL)*/ COUNT (*), elementId#
FROM accounts GROUP BY elementId#;
< 339, 1>
1 row found.
Now issue a SELECT query supplying the TT_GridQueryExec(GLOBAL) optimizer hint. Expect 2020 rows returned (k=2 * r=1010 = 2020). Validate the results by using the SUM function to calculate the total rows returned for all 6 elements.
Command> SELECT /*+TT_GridQueryExec(GLOBAL)*/ COUNT (*), elementId#
FROM accounts GROUP BY elementId#
ORDER BY elementId#;
< 339, 1 >
< 339, 2 >
< 332, 3 >
< 332, 4 >
< 339, 5 >
< 339, 6 >
6 rows found.
Command> SELECT SUM (339+339+332+332+339+339) FROM dual;
< 2020 >
1 row found.
Validate the total count using the TT_GridQueryExec(GLOBAL) hint.
Command> SELECT/*+TT_GridQueryExec(GLOBAL)*/ COUNT(*) FROM accounts; < 2020 > 1 row found.
The TT_PartialResult optimizer hint enables you to specify whether the query should return partial results or error if data is not available.
Use TT_PartialResult(1) to direct the query to return partial results if all elements in a replica set are not available.
Use TT_PartialResult(0) to direct the query to return an error if the required data is not available in the case where all elements in a replica set are not available. If at least one element from each replica set is available or the data required by the query is available, the optimizer returns the query result correctly without error.
The default is TT_PartialResult(0).
See "TT_PartialResult optimizer hint" in the Oracle TimesTen In-Memory Database SQL Reference for information on the syntax and semantics for this hint.
Example 7-11 Examine results using TT_PartialResult
In this example, select the elementId#, replicaSetId#, and dataspaceId# pseudocolumns to locate the row of data involved in the query. Force elements 3 and 4 to be unavailable. Set TT_PartialResult to 0 to return an error if the replica set is unavailable. Then, set TT_PartialResult to 1 to return partial results from the elements that are available.
Command> SELECT elementId#,replicasetId#,dataspaceId#, last_name,first_name
FROM customers WHERE last_name LIKE ('%Wh%') ORDER BY last_name;
< 6, 3, 2, Whitaker, Armand >
< 4, 2, 2, Whitaker, Ariel >
< 6, 3, 2, White, Carlene >
< 6, 3, 2, White, Marcelo >
< 4, 2, 2, White, Dona >
< 4, 2, 2, White, Ellyn >
< 4, 2, 2, White, Nora >
< 4, 2, 2, White, Phylis >
8 rows found.
Command> SELECT /*+TT_PartialResult(0)*/ elementId#,replicasetId#,dataspaceId#,
last_name,first_name FROM customers
WHERE last_name like ('%Wh%') ORDER BY last_name;
< 6, 3, 2, Whitaker, Armand >
< 4, 2, 2, Whitaker, Ariel >
< 6, 3, 2, White, Carlene >
< 6, 3, 2, White, Marcelo >
< 4, 2, 2, White, Dona >
< 4, 2, 2, White, Ellyn >
< 4, 2, 2, White, Nora >
< 4, 2, 2, White, Phylis >
8 rows found.
Command> SELECT /*+TT_PartialResult(1)*/ elementId#,replicasetId#,dataspaceId#,
last_name,first_name FROM customers
WHERE last_name LIKE ('%Wh%') ORDER BY last_name;
< 6, 3, 2, Whitaker, Armand >
< 4, 2, 2, Whitaker, Ariel >
< 6, 3, 2, White, Carlene >
< 6, 3, 2, White, Marcelo >
< 4, 2, 2, White, Dona >
< 4, 2, 2, White, Ellyn >
< 4, 2, 2, White, Nora >
< 4, 2, 2, White, Phylis >
8 rows found.
Element 4 is no longer available. Expect same results. Element 3 is available.
Command> SELECT /*+TT_PartialResult(1)*/ elementId#,replicasetId#,dataspaceId#,
last_name,first_name FROM customers
WHERE last_name LIKE ('%Wh%') ORDER BY last_name;
< 6, 3, 2, Whitaker, Armand >
< 3, 2, 1, Whitaker, Ariel >
< 6, 3, 2, White, Carlene >
< 6, 3, 2, White, Marcelo >
< 3, 2, 1, White, Dona >
< 3, 2, 1, White, Ellyn >
< 3, 2, 1, White, Nora >
< 3, 2, 1, White, Phylis >
8 rows found.
Command> SELECT /*+TT_PartialResult(0)*/ elementId#,replicasetId#,dataspaceId#,
last_name,first_name FROM customers
WHERE last_name LIKE ('%Wh%') ORDER BY last_name;
< 6, 3, 2, Whitaker, Armand >
< 3, 2, 1, Whitaker, Ariel >
< 6, 3, 2, White, Carlene >
< 6, 3, 2, White, Marcelo >
< 3, 2, 1, White, Dona >
< 3, 2, 1, White, Ellyn >
< 3, 2, 1, White, Nora >
< 3, 2, 1, White, Phylis >
8 rows found.
Now element 3 becomes unavailable. Replica set 2 is unavailable. Expect TT_PartialResult set to 1 to return partial results. Expect TT_PartialResult set to 0 to return an error.
Command> SELECT /*+TT_PartialResult(1)*/ elementId#,replicasetId#,dataspaceId#,
last_name,first_name FROM customers
WHERE last_name LIKE ('%Wh%') ORDER BY last_name;
< 6, 3, 2, Whitaker, Armand >
< 6, 3, 2, White, Carlene >
< 6, 3, 2, White, Marcelo >
3 rows found.
Command> SELECT /*+TT_PartialResult(0)*/ elementId#,replicasetId#,dataspaceId#,
last_name,first_name FROM customers
WHERE last_name LIKE ('%Wh%') ORDER BY last_name;
3723: Replica set 2 down
The command failed.
TimesTen Scaleout requires a unique id for row distribution. It uses ROWID to ensure uniqueness across all elements.
For tables with 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, the physical location of each copy of a row is different, so each copy of the row has different ROWID values. In this case, when using ROWID based access, TimesTen Scaleout returns the value of the ROWID in the first data space. If the row in the first data space is not available, TimesTen Scaleout returns the ROWID in the next (second) data space.
Since ROWID is the identifier of a specific copy of a row, if that copy is not available, you cannot access the row by ROWID. In this case, you should access the row by primary key.
See "ROWID pseudocolumn" in the Oracle TimesTen In-Memory Database SQL Reference for more information.
Note:
Applications should not storeROWID values in the database and try to use these values later. Applications can fetch the ROWID in a transaction and then use the ROWID later in the same transaction.There are several local (V$) global (GV$) system views you can query to retrieve metadata information about your database.
The V$ views contain data for the element to which your application is connected.
The GV$ views contain the contents of the V$ view for every element of the database.
In addition, there are several views that you can query that are based on TimesTen built-in procedures. See "System Tables and Views" in the Oracle TimesTen In-Memory Database System Tables and Views Reference for more information.