TimesTen In-Memory Database
for Extreme Performance

What is the best TimesTen index for my OLTP application

Doug Hood
Evangelist for Oracle TimesTen, Oracle In-Memory and Oracle NoSQL

Recently I needed to do some TimesTen OLTP performance tuning for a customer and they wanted to know whether they should use a hash index or a B+tree index for optimal query performance.  I said that it depends on your workloads, but that sometimes you can have the best of both!

Hash and B+Tree indexes

Range and hash indexes have different pros and cons:

Pros and cons of range and hash indexes

So range and hash indexes are good at different things.  Unlike some RDBMS, TimesTen can support multiple different indexes for the same column.  

A sample TimesTen table

For the above table test, there is a single unique range index for the primary key (column ID).

You can also add other hash and ranges indexes to an existing table:

Create indexes of various types

You can see that:

  • The primary key column (ID) has both range and hash indexes.
  • Col4 has both range and hash indexes.
  • Col3 + Col4 both have range and hash indexes on the same columns
  • Range and hash indexes can span multiple columns and support many different data types

Now that you have both range and hash indexes, how can they help your queries?

Explain plan for hash and range queries

As you can see from the explain plan, without any SQL hints, the TimesTen query optimizer knows to use the hash index for equality queries and the range index for in-equality queries. This not magic, you need to updated your statistics for the TimesTen query optimizer to generate good query plans.

By having both range and hash indexes on the primary key, both equality and in-equality queries can be optimized.

For the customer's workload (tables, queries and data), the equality queries could be executed in 1.3 microseconds and the in-equality queries in 2.5 microseconds.

For any database, indexes can help speed up queries, but have some overhead and will tend to slow down writes.  Some complex inserts/updates/deletes that have where clauses can benefit from indexes. Adding more indexes is most appropriate for read intensive workloads


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.