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!
Range and hash indexes have different pros and cons:
So range and hash indexes are good at different things. Unlike some RDBMS, TimesTen can support multiple different indexes for the same column.
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:
You can see that:
Now that you have both range and hash indexes, how can they help your 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.