X

TimesTen In-Memory Database
for Extreme Performance

How to Create Database Schema in the TimesTen Scaleout Database using TTISQL

Jenny Bloom
Product Manager
There are 3 database distribution types in TimesTen Scaleout.  A sample SQL script  (file name , cr_tables.sql) is included  utilizes all 3 distribution types: hash, duplicate and reference.
  • Distributed by Hash is the default type if no distribution type is specified. The hash key can be the primary key (default) or user_defined columns. It uses the consistent hash algorithms. CUSTOMERS table is an example table of Distribution by Hash.
  • Distributed by Duplicate tables is commonly used for reference tables because identical copies of the data are on all elements. Distributed by Duplicate tables are optimized for read and joins. The ACCOUNT_TYPE and ACCOUNT_STATUS are examples of such tables.
  • Distributed by Reference tables allow related data to be co-locate together for join optimization. It is based on foreign key relationship. It supports multi-level hierachy. ACCOUNTS table is an example of a reference table.

Entity Relation(ER) diagram of the sample script is followed:

For more detail information on the distribution types, consult the TimesTen In-Memory Database Scaleout User's Guide.

Create Schema using ttIsql

Follow the steps below to create the sample schema using cr_tables.sql script file.

1:

On a data instance, source the TimesTen environment. For this example, the data instance used here is /home/oracle/TimesTen/instances/instance1.

$ source /home/oracle/TimesTen/instances/instance1/bin/ttenv.sh

2:

Go to the directory where the sample scheme file is saved to. For this example, the sample schema file is saved to /home/timesten directory with the file name, cr_tables.sql.

$ cd /home/timesten

3.:

Run the SQL cr_tables.sql script against the database using the ttIsql -f option.


TTISQL

$ ttisql -f cr_tables.sql "dsn=sampledb;uid=appuser"

At this point, the tables in the cr_tables.sql are created in your TimesTen Scaleout Database.  For more information about TimesTen Scaleout, please visit the TimesTen 18.1 documentation library.

Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.