7 Using SQL in TimesTen Scaleout

This chapter describes how to use SQL to work with databases in TimesTen Scaleout. Topics include:

• Overview of SQL

• Overview of PL/SQL

• Working with tables

• Altering tables

• Understanding indexes

• Using sequences

• Performing DML operations

• Using pseudocolumns

• Using the TT_CommitDMLOnSuccess hint

• Using optimizer hints

• Understanding ROWID in data distribution

• Understanding system views

Overview of SQL

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 run a query that is prepared from one element and ran on other elements in the query with the result returned to the originating element.

Note:

• 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.

Overview of PL/SQL

Applications can use PL/SQL to access and manipulate data. PL/SQL is processed on the element to which the application is connected. See 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.

Consider the following when using PL/SQL in TimesTen Scaleout:

• SQL statements that are invoked from PL/SQL are run across the grid as with any other SQL statement.

• PL/SQL functions or procedures are run as local operations.

• As with other SQL objects, TimesTen Scaleout automatically creates all PL/SQL objects when new elements are added to the distribution map of the database.

• As with other DDL statements, TimesTen Scaleout logs PL/SQL DDL statements. PL/SQL objects created or dropped while an element is down are created during the recovery phase of the element. However, PL/SQL DDL statements are blocked on the database during the log-based catch up recovery phase of the element, just like any other DDL operation.

TimesTen Scaleout supports most, but not all, PL/SQL features supported by TimesTen Classic. For unsupported PL/SQL features, see Table 1-9.

Working with tables

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.

Altering tables

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 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:

• Use ALTER TABLE to add a primary key constraint

• Use ALTER TABLE to change the distribution key

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 *)

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 *)

Understanding indexes

TimesTen Scaleout supports both local and global indexes.

• Local index: TimesTen Scaleout creates the index on all elements of the database. The index in each element maps to rows in that element. Queries against index columns that do not also include all the distribution key columns of the table require communication with an element in every replica set.

• Global index: A global index maps all rows in the database in a hash distribution scheme. When you create a global index, TimesTen Scaleout creates a materialized view with a local index and a hash distribution scheme to the index key columns. The materialized view optimizes query execution by making predictable in which replica set any given value of the index key columns is located. The local index on the materialized view further optimizes query performance.

  Note:

  There is an execution cost overhead incurred for DML operations against the columns that are defined in a global index. Also, a global index has a storage cost overhead when compared to a local index. These are key tuning trade-offs in TimesTen Scaleout.

To increase query performance, consider using a global index instead a local index for:

• Unique indexes. With a global unique index, TimesTen Scaleout can perform unique constraint checks more efficiently. See Example 7-1.

  Note:

  Create a local unique index instead if the distribution key is a subset of the index key. TimesTen Scaleout uses the distribution key columns for uniqueness verification instead of accessing all replica sets as it would do for any other local unique index case.

• Columns that are frequently joined with primary key columns in queries. If one or more of the joined sets of columns are neither the primary key or distribution key, then creating global indexes on such sets of columns optimizes query performance by reducing the number of replica sets that need to be accessed. See Example 7-2.

• Indexes that include non-index columns that are frequently accessed in queries. Global indexes enable you to add non-index columns in index structure with the INCLUDE clause. These non-index columns in the index can be used to satisfy some queries without needing to access the base table. See Example 7-2.

• Indexes where the index key is a prefix of the distribution key of the table.

Note:

To reduce space usage and improve DML performance, it is recommended that you combine all global indexes with the same prefix in the index key into a single global index using the columns in the prefix as index key.

Likewise, consider using a local index instead of a global index for:

• Indexes where the index key:

  • Consists of only non-unique columns

  • Is the same as the distribution key of the table

• Indexes where the distribution key of the table is a prefix of the index key.

Global indexes are not supported on these cases:

• Tables using a duplicate distribution scheme

• Tables using a reference distribution scheme where the distribution key references a table using a reference distribution scheme.

Example 7-1 Creating a unique index

The following example illustrates how to create a unique index (local and global) on an existing table and shows the query optimizer plan for inserting values into the table.

Consider that you need to ensure that phone numbers inserted into the accounts table are unique and the table already uses the account_id column as primary key and hash distribution key.

Command> DESCRIBE accounts;

Table TERRY.ACCOUNTS:
  Columns:
   *ACCOUNT_ID                      NUMBER (10) NOT NULL
    PHONE                           VARCHAR2 (16) 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
  PRIMARY KEY (ACCOUNT_ID) RANGE INDEX
  DISTRIBUTE BY HASH (ACCOUNT_ID)

1 table found.
(primary key columns are indicated with *)

If you create a local unique index, TimesTen Scaleout would need to connect to every replica set of the database to verify the uniqueness of the values inserted or updated in the phone column, as shown next.

Command> CREATE UNIQUE INDEX phone_ix ON accounts(phone);
Command> INDEXES;

Indexes on table TERRY.ACCOUNTS:
  ACCOUNTS: unique range index on columns: 
    ACCOUNT_ID
  PHONE_IX: unique range index on columns: 
    PHONE
  2 indexes found.

2 indexes found on 1 table.
Command> EXPLAIN INSERT INTO accounts VALUES(?,?,?,?,?,?,?,?);

Query Optimizer Plan:

  STEP:                1
  LEVEL:               5
  OPERATION:           RowLkInsert
  TBLNAME:             ACCOUNTS
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         
  MISCELLANEOUS:       


  STEP:                2
  LEVEL:               4
  OPERATION:           GridRoute(Dist: DistHash, Kind: 1ProducerNConsumer)
  TBLNAME:             
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         
  MISCELLANEOUS:       


  STEP:                3
  LEVEL:               3
  OPERATION:           DMLScan
  TBLNAME:             
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         
  MISCELLANEOUS:       opNodeCnt=1, RowLkInsert(ACCOUNTS)


  STEP:                4
  LEVEL:               2
  OPERATION:           GridRoute(Dist: Duplicate, Kind: NProducerNConsumer)
  TBLNAME:             
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         
  MISCELLANEOUS:       


  STEP:                5
  LEVEL:               1
  OPERATION:           GlobalCheckConstraint
  TBLNAME:             ACCOUNTS
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         
  MISCELLANEOUS:       UniqueKeyInsert(idx:PHONE_IX)

If you instead create a global unique index, TimesTen Scaleout would be able to verify the uniqueness of the values in the phone column more efficiently since the location of a row in the materialized view that the global index creates would be determined by the value in the phone column. The local index that the global index creates on the materialized view further ensures optimum query performance.

Command> CREATE GLOBAL UNIQUE INDEX phone_gix ON accounts(phone) DISTRIBUTE BY HASH;
Command> INDEXES;

Indexes on table TERRY.ACCOUNTS:
  ACCOUNTS: unique range index on columns: 
    ACCOUNT_ID
  PHONE_GIX: global unique range index on columns: 
    PHONE
  2 indexes found.

Indexes on materialized view TERRY.GBLMV_1:
  GBLMVIDX_1: unique range index on columns: 
    PHONE
  1 index found.

3 indexes found on 2 tables.
Command> EXPLAIN INSERT INTO accounts VALUES(?,?,?,?,?,?,?,?);

Query Optimizer Plan:

  STEP:                1
  LEVEL:               2
  OPERATION:           GridRoute(Dist: DistHash, Kind: 1ProducerNConsumer)
  TBLNAME:             
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         
  MISCELLANEOUS:       


  STEP:                2
  LEVEL:               1
  OPERATION:           DMLScan
  TBLNAME:             
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         
  MISCELLANEOUS:       opNodeCnt=2, RowLkInsert(ACCOUNTS), RowLkInsert(GBLMV_1)

Example 7-2 Using global indexes to optimize query with joins to primary key columns

The following example illustrates how to use global indexes to optimize queries to columns that are commonly joined in queries to primary key columns.

Consider that the customers table uses the cust_id column as both primary and distribution key, the accounts table uses the account_id column as both primary and distribution key, and the call_records table uses the call_id columns as both primary and distribution key.

Command> DESCRIBE customers;

Table TERRY.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
    ACCOUNT_ID                      NUMBER (10)
    MEMBER_SINCE                    DATE NOT NULL
  PRIMARY KEY (CUST_ID) RANGE INDEX
  DISTRIBUTE BY HASH (CUST_ID)

1 table found.
(primary key columns are indicated with *)
Command> DESCRIBE accounts;

Table TERRY.ACCOUNTS:
  Columns:
   *ACCOUNT_ID                      NUMBER (10) NOT NULL
    PHONE                           VARCHAR2 (16) 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
  PRIMARY KEY (ACCOUNT_ID) RANGE INDEX
  DISTRIBUTE BY HASH (ACCOUNT_ID)

1 table found.
(primary key columns are indicated with *)
Command> DESCRIBE call_records;

Table TERRY.CALL_RECORDS:
  Columns:
   *CALL_ID                         NUMBER (10) NOT NULL
    CALLER                          NUMBER (10) NOT NULL
    RECEIVER                        NUMBER (10) NOT NULL
    CALL_TIME                       TIMESTAMP (6) NOT NULL
    CODE                            NUMBER (38) NOT NULL
  PRIMARY KEY (CALL_ID) RANGE INDEX
  DISTRIBUTE BY HASH (CALL_ID)

1 table found.
(primary key columns are indicated with *)

Also, consider that you need to report on the accounts and customers that made a call with a specific code, as shown in the next query.

SELECT accounts.account_id, customers.cust_id, call_records.code
    FROM accounts, customers, call_records
    WHERE customers.cust_id = call_records.caller
        AND call_records.code = ?
        AND customers.account_id = accounts.account_id;

Given that the customers.cust_id and accounts.account_id columns are the primary keys of their respective tables, queries to those columns are already optimized. However, to optimize the join between the customers and call_records tables, the example creates the customer_calls_gix global index on the call_records.caller column and includes the call_records.code column to avoid having to further access the call_records table during the execution of the query.

CREATE GLOBAL INDEX customer_calls_gix 
    ON call_records(caller)
    INCLUDE (code)
    DISTRIBUTE BY HASH;

Furthermore, the example creates the customer_account_gix global index on the customers.account_id column to optimize the join between the customers and accounts tables.

CREATE GLOBAL INDEX customer_account_gix
    ON customers(account_id)
    DISTRIBUTE BY HASH;

For more information on indexes, see "ALTER TABLE", "CREATE INDEX", and "CREATE TABLE" in the Oracle TimesTen In-Memory Database SQL Reference.

Using sequences

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.

Understanding batch allocation

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:

• Illustrate batch assignment for three elements

• Illustrate a second batch assignment for three elements

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.

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.

Performing DML operations

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.

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 the Oracle TimesTen In-Memory Database SQL Reference for information on the additional pseudocolumns supported in TimesTen Scaleout.

Examples include:

• Use replicaSetId# to locate data

• Use replicaSetId# with a table that has a duplicate distribution scheme

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.

Using the TT_CommitDMLOnSuccess hint

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.

Using optimizer hints

The TimesTen query optimizer is a cost-based optimizer that determines the most efficient way to run 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:

• TT_GridQueryExec

• TT_PartialResult

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.

TT_GridQueryExec

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 run 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 runs 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 run 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
LOCAL	Duplicate distribution scheme table Distributed by hash table Distributed by reference table	r r/e (Assumes uniform distribution) r/e (Assumes uniform distribution)
GLOBAL	Duplicate distribution scheme table Distributed by hash table Distributed by reference table	e*k*r k*r k*r

Examples include:

• Use TT_GridQueryExec on a hash distribution scheme table

• Use TT_GridQueryExec on a duplicate distribution scheme table

• 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 the TT_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.

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 runs 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.

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 runs 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.

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 runs 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.

TT_PartialResult

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.

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.

Understanding ROWID in data distribution

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 store ROWID 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.

Understanding system views

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.