Creating a Table

To create a table, use the SQL statement CREATE TABLE.

The syntax for all SQL statements is provided in SQL Statements in the Oracle TimesTen In-Memory Database SQL Reference. TimesTen converts table names to upper case characters.

The following SQL statement example creates a table, called customer, with two columns: cust_id and cust_name of two different data types.

Command> CREATE TABLE customer (cust_id TT_INTEGER, cust_name VARCHAR2(50));

This example creates a table, called customer, with a hash index. The customer table has columns: cust_id, cust_name, addr, zip, and region. The cust_id column is designated as the primary key, so that the CustId value in a row uniquely identifies that row in the table, as described in Primary Keys, Foreign Keys, and Unique Indexes.

The UNIQUE HASH ON custId PAGES value indicates that there are 30 pages in the hash index. This means that the expected number of rows in the table is 30 * 256 = 7680. If the table ends up with significantly more rows than this, performance can be degraded, and the hash index should be resized.

See SET PAGES in the ALTER TABLE section in the Oracle TimesTen In-Memory Database SQL Reference. See Size Hash Indexes Appropriately.

Command> CREATE TABLE customer
(cust_id NUMBER NOT NULL PRIMARY KEY, 
cust_name CHAR(100) NOT NULL, 
addr CHAR(100), 
zip NUMBER, 
region CHAR(10)) 
UNIQUE HASH ON (cust_id) PAGES = 30;