Forum: Building VoltDB Applications

Post: How to use Hash index and not primary key index.

How to use Hash index and not primary key index.
sanket
Nov 15, 2016
While creating the table, I am using primary key to prevent content violation. This creates primary key index automatically which is a balance tree. I have also create Hash index for the tables and I want to use hash index and not to use primary key index. Is there any setting that I can make to use the Hash index while execution of queries.
sanket
Nov 15, 2016
How to provide 'Hint' for query planner to use a specific index during query execution?
bballard
Nov 15, 2016
VoltDB uses a convention to determine whether to use a red-black tree index or a hash index. The convention is to include the substring "hash" (case-insensitive) within the name of the index. It can be a prefix, suffix, or somewhere in the middle of the name.

In my experience, since the index is entirely in-memory, the is very little difference in performance between a tree and hash index. It may be worthwhile to try it both ways and compare the performance vs. space usage to decide which is best.

Also, there are a few syntax styles for declaring the primary key of a table. You do not have to give it a name, but you can, which would allow you to declare whether you want it to be a tree or hash index. There should be no need to create duplicate indexes simply because the primary key index was not as you wanted it. Below are two examples:

CREATE TABLE Version (
Major SMALLINT NOT NULL,
Minor SMALLINT NOT NULL,
baselevel INTEGER NOT NULL,
ReleaseDate TIMESTAMP,
CONSTRAINT Version_Hash_Idx PRIMARY KEY
(Major, Minor, Baselevel) -- hash index
);

CREATE TABLE Version (
Major SMALLINT NOT NULL,
Minor SMALLINT NOT NULL,
baselevel INTEGER NOT NULL,
ReleaseDate TIMESTAMP,
PRIMARY KEY (Major, Minor, Baselevel) -- default tree index
);

There is no method to "Hint" to the VoltDB query planner to use a specific index. It chooses the index based on a planning heuristic. It can only use one index per table in the query. Generally you can get the planner to use a different index by creating an index which is better for the query execution (different column order or includes more columns, etc).