Defining Table Distribution Schemes
DISTRIBUTE BY clause of the CREATE TABLE
statement. Regardless of how the data is distributed or on which element
specific data is located, applications can access all the data in the database while
connecting to a single element. However, there are some considerations you should take into
account when defining the distribution scheme of a table.
Note:
-
Before you start creating database objects, see Authentication in TimesTen in Oracle TimesTen In-Memory Database Security Guide.
-
If you are planning to load your tables with data, consider creating your tables without indexes. After the data is loaded, you can then create your indexes. This reduces the time it take to load the data into the tables.
The available data distribution schemes for a table in TimesTen Scaleout are:
Hash
The hash distribution scheme distributes data based on the hash of the primary key or a set of user-specified columns. The hash key determines in which replica set a row should be stored. Any given row in the table is stored in only one replica set. If the table does not have a primary key or a user-specified distribution column, TimesTen Scaleout distributes the data based on the hash of a hidden column that TimesTen Scaleout adds for this purpose. This distribution scheme is adaptive to topology changes and uses consistent hashing. In other words, a row with an specific value in the hash key columns will always be allocated on the same replica set, provided that the topology does not change. If the topology changes, the location of the row may change when the data is re-distributed.
Note:
If you create a table without specifying a DISTRIBUTE BY clause, TimesTen Scaleout defines a hash distribution scheme on the table. In addition, if a column is not specified in the DISTRIBUTE BY HASH clause, TimesTen Scaleout selects the primary key columns as the key columns of the distribution scheme. If a primary key is not defined, TimesTen Scaleout creates a hidden column as the hash key.
Create the customers table that uses a DISTRIBUTE BY HASH clause, which distributes data based on the hash of the cust_id primary key column.
CREATE TABLE customers (
cust_id NUMBER(10,0) NOT NULL PRIMARY KEY,
first_name VARCHAR2(30) NOT NULL,
last_name VARCHAR2(30) NOT NULL,
addr1 VARCHAR2(64),
addr2 VARCHAR2(64),
zipcode VARCHAR2(5),
member_since DATE NOT NULL
) DISTRIBUTE BY HASH;Figure 5-1 shows the data distribution for the customers table in the database1 database, as configured in Creating a Database. TimesTen Scaleout distributes the data to each element based on the hash of the cust_id column.
For more information on the hash distribution scheme, see CREATE TABLE in Oracle TimesTen In-Memory Database SQL Reference.
Reference
The reference distribution scheme distributes the data of a child table based on the location of the corresponding parent row of a foreign key constraint. This distribution scheme optimizes the performance of joins by distributing related data on a single element. When you join the parent and child tables, TimesTen Scaleout does not need to access different elements because all of the data is stored on the same element. The parent table can be distributed by hash or reference, which allows for a multitiered reference distribution.
Note:
Ensure you declare the child key columns of a foreign key constraint as NOT NULL when you use the DISTRIBUTE BY REFERENCE clause.
Create the customers parent table that uses a DISTRIBUTE BY HASH clause that distributes data based on the hash of the cust_id primary key column. Then, create the accounts child table that uses a DISTRIBUTE BY REFERENCE clause that distributes the data in the accounts table based on the location of the corresponding value of the referenced column, customers(cust_id), in the fk_customer foreign key.
CREATE TABLE customers (
cust_id NUMBER(10,0) NOT NULL PRIMARY KEY,
first_name VARCHAR2(30) NOT NULL,
last_name VARCHAR2(30) NOT NULL,
addr1 VARCHAR2(64),
addr2 VARCHAR2(64),
zipcode VARCHAR2(5),
member_since DATE NOT NULL
) DISTRIBUTE BY HASH;
CREATE TABLE accounts (
account_id NUMBER(10,0) NOT NULL PRIMARY KEY,
phone VARCHAR2(15) 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,0) NOT NULL,
CONSTRAINT fk_customer
FOREIGN KEY (cust_id)
REFERENCES customers(cust_id)
) DISTRIBUTE BY REFERENCE (fk_customer);Figure 5-2 shows the data distribution for the customers table in the database1 database, as configured in Creating a Database. TimesTen Scaleout distributes the data in the customers table to each replica set based on the hash of the cust_id primary key column. The figure also shows the data distribution for the accounts table, which is based on the location of the corresponding value of the referenced column, cutomers(cust_id), in the fk_customer foreign key.
For more information on the reference distribution scheme, see CREATE TABLE in Oracle TimesTen In-Memory Database SQL Reference.
Duplicate
The duplicate distribution scheme distributes identical copies of the data of a table to all the elements of a database. This distribution scheme optimizes the performance of reads and joins against the table by ensuring that all data access is local. However, inserts and updates are more resource intensive than other distribution schemes.
Create the account_type table that uses a DUPLICATE clause that distributes the data to all the elements of a database.
CREATE TABLE account_type (
type CHAR(1) NOT NULL PRIMARY KEY,
description VARCHAR2(100) NOT NULL
) DUPLICATE;Figure 5-3 shows the data distribution for the account_type table in the database1 database, as configured in Creating a Database. TimesTen Scaleout creates a copy of the data on all the elements of the database.
For more information on the duplicate distribution scheme, see CREATE TABLE in Oracle TimesTen In-Memory Database SQL Reference.