Introduction

TimesTen XE SQL support

TimesTen XE is a free SQL relational database with persistence and support for many SQL features.

Even though TimesTen XE is an in-memory database, it does SQL and PLSQL like other databases, only a lot faster.

This blog gives some examples of using TimesTen XE SQL.  The examples do not go into much depth.  Later blogs will delve into the details.

 

 

Pre-requisites

This blog assumes that:

 

 

Creating Tables

Data model

 

Connect to your TimesTen XE database as user scott with password tiger on database sampledb.

ttisql “uid=scott;pwd=tiger;dsn=sampledb”

 

Create the dept table

create table dept(  
  deptno    number(2,0) not null primary key,  
  dname     varchar2(14),  
  loc       varchar2(13)
);

 

Describe the dept table

Dept table

  • Primary keys are shown with a *
  • TimesTen XE supports Oracle types like NUMBER and VARCHAR2
  • Narrower columns are stored INLINE [within the row]
  • Wider columns are stored NOT INLINE [or out of line] and are not stored in the row

 

 

Create the emp table

create table emp(  
  empno     number(4,0),  
  ename     varchar2(10),  
  job       varchar2(9),  
  mgr       number(4,0),  
  hiredate  date,  
  sal       number(7,2),  
  comm      number(7,2),  
  deptno    number(2,0),  
  primary key (empno),  
  constraint fk_deptno foreign key (deptno) references dept (deptno)  
);

 

Describe the emp table

emp table

  • Foreign keys are not shown in the describe table output
  • Primary keys can be multi-column

 

 

Create a table with some number types

Create table num1 (
  ID    number,
  C2   number(7,4),
  C3   binary_float,
  C4   binary_double,
  C5   float,
  C6   float(5),
  C7   tt_tinyint,
  C8   tt_smallint,
  C9   tt_integer,
  C10 tt_bigint
);

 

Describe the num1 table

num1 table

  • The Oracle NUMBER type can take up to 22 bytes to store a number.  The range is from 1.0 x 10 -130  to 1.0 x 10 126
  • TT_TINYINT requires one 1 byte of storage that ranges from 0-255
  • TT_SMALLINT requires 2 bytes of storage that ranges from -32768 to 32767
  • TT_INTEGER requires 4 bytes of storage that ranges from -2,147,483,648 to 2,147,483,647
  • TT_BIGINT requires 8 bytes of storage that ranges from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
  • BINARY_FLOAT requires 4 bytes of storage and is a 32 bit single precision floating point number
  • BINARY_DOUBLE requires 8 bytes of storage and is a 64 bit double precision floating point number
  • FLOAT uses the NUMBER type for storage

Choose the appropriate numeric type to store the precision required while minimizing the memory for storage.

 

 

Create a table with some character types

Create table chars (
  C1 char(8300),
  C2 nchar(4150),
  C3 varchar2(4194304),
  C4 nvarchar2(2097152)
);

 

Describe the chars table

Chars table

  • The Oracle varchar2 type can store strings up to 4 MB in length
  • The Oracle CHAR and NCHAR types use right padding with spaces
  • Varchar2 does not use right padding with spaces
  • TimesTen XE supports the Oracle NLS character sets. Here is a small subset of the available character sets:
    • US7ASCII
    • VN8MSWIN1258
    • AL32UTF8
  • The AL32UTF8 unicode universal character set is recommended for the database character set
  • The connection character set should be based on the character set of the client machines. eg
    • Spanish [WE8MSWIN1252]
    • Korean [KO16KSC5601]
    • Japanese [JA16EUC]
    • Mandarin [ZHT16BIG5]
    • English [WE8ISO8859P15]

 

 

Create a table with date and time types

Create table datetime1 (
  C1 date,
  C2 time,
  C3 timestamp,
  C4 tt_date,
  C5 tt_timestamp
);

 

Describe the datetime1 table

datetime1 table

  • TT_DATE requires 4 bytes of storage and has the range 1753-01-01 (January 1, 1753 AD) to 9999-12-31 (December 31, 9999 AD)
  • TT_TIMESTAMP requires 8 bytes of storage and has the range 1753-01-01 00:00:00 (January 1, 1753, midnight) to 9999-12-31 23:59:59 (December 31, 9999, 11:59:59 PM)
  • DATE requires 7 bytes of storage and has the range -4712-01-01 (January 1, 4712 BC) to 9999-12-31
  • TIME requires 8 bytes of storage and has the range 00:00:00 (midnight) to 23:59:59 (11:59:59 pm)
  • TIMESTAMP requires 12 bytes of storage and ranges from -4712-01-01 (January 1, 4712 BC) to 9999-12-31 (December 31, 9999 AD)
  • Choose the date or timestamp type based on your precision and storage requirements

 

Create a table with large object types

Create table largeobjs1 (
  C1 clob,
  C2 blob,
  C3 nclob,
  C4 binary(8300),
  C5 varbinary(4194304)
);

 

Describe the largeobjs1 table

table largeobjs1

  • CLOBs and NCLOBs can be up to 4 MB in size
  • BLOBs can be up to 16 MB in size
  • BINARY is a fixed size type and is right padded with zeros. It can store up to 8300 bytes
  • VARBINARY is variable sized and can be up to 4 MB in size

 

 

Create a table with ANSI SQL types

Create table ansi2 (
  C1  character varying( 10 ),
  C2  character varying(  5 byte ),
  C3  character varying(  5 char ),
  C4  char varying(      10 ),
  C5  char varying(       5 byte ),
  C6  char varying(       5 char ),
  C7  double,
  C8  float,
  C9  float(10),
  C10 int,
  C11 integer,
  C12 national character(            4150),
  C13 national char     (            4150),
  C14 national character varying (2097152 ),
  C15 national char varying      (2097152 ),
  C16 nchar varying              (2097152 ),
  C17 numeric,
  C18 numeric( 7 ),
  C19 numeric( 7,7),
  C20 dec,
  C21 dec(     12),
  C22 dec(     12,3),
  C23 decimal,
  C24 decimal( 12),
  C25 decimal( 12,3),
  C26 real,
  C27 smallint
);

 

Describe the ansi2 table

table ansi2

  • The ANSI types are stored as Oracle types
  • Use the Oracle and TimesTen types rather than ANSI types as they give you more control over the space needed for each column

 

 

 

Loading data

Data loading

There are several ways to load data into tables:

 

 

SQL insert statements

The followng SQL statements will populate the DEPT table that you created.

insert into dept 
values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept 
values(20, 'RESEARCH', 'DALLAS');
insert into dept 
values(30, 'SALES', 'CHICAGO');
insert into dept 
values(40, 'OPERATIONS', 'BOSTON');

 

The followng SQL statements will populate the EMP table that you created.

insert into emp 
values( 
7839, 'KING', 'PRESIDENT', null, 
to_date('17-11-1981','dd-mm-yyyy'), 
5000, null, 10 
);
insert into emp 
values( 
7698, 'BLAKE', 'MANAGER', 7839, 
to_date('1-5-1981','dd-mm-yyyy'), 
2850, null, 30 
);
insert into emp 
values( 
7782, 'CLARK', 'MANAGER', 7839, 
to_date('9-6-1981','dd-mm-yyyy'), 
2450, null, 10 
);
insert into emp 
values( 
7566, 'JONES', 'MANAGER', 7839, 
to_date('2-4-1981','dd-mm-yyyy'), 
2975, null, 20 
);
insert into emp 
values( 
7788, 'SCOTT', 'ANALYST', 7566, 
to_date('13-JUL-87','dd-mm-rr') - 85, 
3000, null, 20 
);
insert into emp 
values( 
7902, 'FORD', 'ANALYST', 7566, 
to_date('3-12-1981','dd-mm-yyyy'), 
3000, null, 20 
);
insert into emp 
values( 
7369, 'SMITH', 'CLERK', 7902, 
to_date('17-12-1980','dd-mm-yyyy'), 
800, null, 20 
);
insert into emp 
values( 
7499, 'ALLEN', 'SALESMAN', 7698, 
to_date('20-2-1981','dd-mm-yyyy'), 
1600, 300, 30 
);
insert into emp 
values( 
7521, 'WARD', 'SALESMAN', 7698, 
to_date('22-2-1981','dd-mm-yyyy'), 
1250, 500, 30 
);
insert into emp 
values( 
7654, 'MARTIN', 'SALESMAN', 7698, 
to_date('28-9-1981','dd-mm-yyyy'), 
1250, 1400, 30 
);
insert into emp 
values( 
7844, 'TURNER', 'SALESMAN', 7698, 
to_date('8-9-1981','dd-mm-yyyy'), 
1500, 0, 30 
);
insert into emp 
values( 
7876, 'ADAMS', 'CLERK', 7788, 
to_date('13-JUL-87', 'dd-mm-rr') - 51, 
1100, null, 20 
);
insert into emp 
values( 
7900, 'JAMES', 'CLERK', 7698, 
to_date('3-12-1981','dd-mm-yyyy'), 
950, null, 30 
);
insert into emp 
values( 
7934, 'MILLER', 'CLERK', 7782, 
to_date('23-1-1982','dd-mm-yyyy'), 
1300, null, 10 
);

 


 

Some simple SQL

The following are some simple queries using the DEPT and EMP tables.

select ename, dname, job, empno, hiredate, loc 
from emp, dept 
where emp.deptno = dept.deptno 
order by ename;

 

Query DEPT and EMP

 

The ANSI SQL JOIN sytax can also be used

SELECT d.dname,
       e.ename,
       e.sal,
       e.comm
FROM   DEPT d
       JOIN EMP e ON d.deptno = e.deptno
WHERE  d.deptno >= 30
ORDER BY d.dname;

 

ANSI SQL JOIN

 

Select * from emp;

 

select * from

 

timing 1;
select * from emp where empno = 7844;

 

PK lookup on emp

  • TimesTen XE uses a cost based optimizer, so using the correct indexes and having up to date optimizer statistics is very important for SQL performance
  • Primary key lookups can be fast
    • This example was non optional as it took about 24 microseconds
      • It used dynamic SQL with a range index and did not have huge pages configured
      • If the SQL statement was prepared and bound, used a hash index, had up to date statistics and huge pages, then it could execute in about 2 microseconds
    • The CPU speed and L3 cache size can also benefit TimesTen XE SQL performance

 

 

Create table de as SELECT d.dname,
       e.ename,
       e.sal,
       e.comm
FROM   DEPT d
       JOIN EMP e ON d.deptno = e.deptno
WHERE  d.deptno >= 30
ORDER BY d.dname;

 

create as select

 

select * from de;

 

Select * from de

 

delete from de where comm is null;
update de set sal = sal * 3.14;
select * from de;

 

update delete

 

Truncate table de;
insert into de SELECT d.dname,
       e.ename,
       e.sal,
       e.comm
FROM   DEPT d
       JOIN EMP e ON d.deptno = e.deptno
WHERE  d.deptno >= 30
ORDER BY d.dname;

 

truncate insert select

 

 

 

Views and Materialized Views

Views and MVs

TimesTen XE supports views and materialized views.

create view v1 as select d.dname,
       e.ename,
       e.sal,
       e.comm
FROM   DEPT d
       JOIN EMP e ON d.deptno = e.deptno
WHERE  d.deptno >= 30
ORDER BY d.dname;

 

create materialized view mv1 as
select d.dname, e.ename,
       e.sal,
       e.comm
FROM   DEPT d, EMP e
WHERE  d.deptno < 30 and d.deptno = e.deptno;

 

views and mvs

 

 

Commit and rollback

  • By default, the TimesTen XE ttIsql utility uses autocommit
  • TimesTen was originally an ODBC database and autocommit is the default for ODBC
  • Autocommit means that each statement is a separate transaction
    • This means that you cannot rollback work that spans multiple statements when using autocommit
  • You can turn autocommit off via autocommit 0, or autocommit off
autocommit off

 

The autocommit command on its own shows the current state of autocommit.

autocommit

 

The following example shows the normal commit / rollback behavior when autocommit is turned off

autocommit rollback and commit

  • Deletes can be rolled back when autocommit is off
  • When a delete is committed it cannot be rolled back

 

 

Sequences and synonyms

TimesTen XE also supports sequences and synonyms.

create sequence s1;
create sequence s2 increment by 2 minvalue 42 cycle cache 1000 start with 5000;

 

Sequences

 

create or replace public synonym s1 for scott.s1;
grant select on s1 to public;
select s1.nextval from dual;

 

synonyms

 

 

Some trivial PLSQL

TimesTen PLSQL

TimesTen XE supports Oracle 19c PLSQL.

You can do the fun stuff like:

Set serveroutput on;
Alter session set plsql_timeout=0;
Begin
  dbms_output.put_line(‘Hello World’);
End;
/

 

PLSQL hi

  • Oracle 19c pipelined functions are not supported in TimesTen XE
  • Stored routine timeouts
    • By default long running TimesTen PLSQL routines timeout after 30 seconds
    • Alter session set plsql_timeout=0 enables stored routines to run forever
    • The Oracle database does not have this PLSQL routine default timeout behavior

 

 

Alter and drop tables

The following gives examples of dropping and altering tables.

drop table de;
Create table de as SELECT d.dname,
       e.ename,
       e.sal,
       e.comm
FROM   DEPT d
       JOIN EMP e ON d.deptno = e.deptno
WHERE  d.deptno >= 30
ORDER BY d.dname;
alter table de add column "when" date default sysdate;
select * from de;

 

alter and drop table

  • The new column when needed to be in quotes as it is a reserved word in TimesTen SQL

 

 

Creating indexes

Indexes

The following examples create some indexes on a table without any indexes.

desc de;
indexes de;
create index de_idx on de(dname);
create index d2_idx on de(ename asc , sal desc );
indexes de;

 

Indexes

  • The indexes command shows the indexes for a table
  • Indexes can be created on one or more columns
  • Indexes can be unique or non unique
  • You can define the sort order for each column in an index
  • Create index will create a range [B+-tree] index
  • Hash indexes will be covered in a separate blog

 

 

Updating statistics

statistics

TimesTen XE uses a cost based optimizer so it needs recent optimizer statistics to generate good query plans.

There are several ways to update statistics that balance simplicity vs time:

  • The ttIsql statsupdate command
    • A command is a helper function which may execute many SQL statements
    • statsupdate will update statistics for all tables in the database
  • statsupdate tablename
    • This only updates the statistics for a single table
  • call ttOptUpdateStats
    • This is an example of a TimesTen XE builtin
    • Bultins are TimesTen kernel C routines which can be called from a SQL program
    • The statsupdate command calls ttOptUpdateStats under the covers
  • call ttOptEstimateStats
    • This builtin updates the statistics on a subset of the table rows rather than all rows
    • When a table has billions of rows, calculating the statics can take some time so estimating is a lot faster

 

statsupdate;
statsupdate de;
call ttOptUpdateStats('emp');
call ttOptEstimateStats('emp', 1, '5 percent');

 

 

Query plans

Query plans

Adding the prefix explain to a SQL statement will display the explain plan in TimesTen XE

explain plan

  • The primary key lookup for EMP used the unique range index [EMP] via a row lock range scan [RowLkRangeScan]
  • The new table de2 did not have any indexes so a full table scan [TblLkSerialScan] was used
  • The de table used a range index [DE_IDX] via a TblLkRangeScan

 

 

Learn more about TimesTen XE:

 

 

More TimesTen XE Blogs

 

 

 

Summary

 

  • TimesTen XE is a really fast SQL relational database with persistence and an in-memory database
  • TimesTen XE has tables with PK/FK, indexes, sequences, synonyms, views, materialized views and PLSQL
  • TimesTen XE supports complex joins and a cost based optimizer
  • TimesTen XE supports ACID transactions, database recovery and high availability

 

 

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