Utilizing Indexes

Objective

After completing this lesson, you will be able to utilize indexes.

Theory of Indexes

A database index has the following properties:

It can be considered an access path that helps the DBMS locate rows faster.
It can speed up searching and sorting as follows:
- It reduces the need for full table scans or full column scans.
- It reduces the need for non-unique indexes in SAP HANA to exceptional cases only.
It has no influence on the result of queries, only on how fast the result is returned.
It can slow down INSERT, UPDATE, and DELETE operations (especially non-unique indexes).
Coming up with a good balance of indexes is an iterative process.

SAP HANA: Why Indexes on Column Store?

SAP HANA column store tables store keep column values together, not row values.
Columns are stored as:
- Sorted dictionary of values
- (Bit-)Vector of value IDs

Are indexes necessary to speed up data access?

The following figure gives an example of when you would use indexes on a column store.

Example for SAP HANA: Indexes on Column Store.

SAP HANA: Table Scan with Value ID

Value ID lookup is fast.
Binary search on sorted dictionary
Row ID lookup can still involve full column scans.
- No issue for most value ID vectors, even less if sorted
- But may be slow for very large value ID vectors

The following figure gives an example of this type of scan.

Example for SAP HANA: Table Scan with Value ID.

SAP HANA: Inverted Index on Column Store

An inverted index can be reasonable in exceptional cases if column scan performance is not sufficient.

The following figure gives an example of this type of index.

Example for SAP HANA: Inverted Index on Column Store.

Practice of Indexes

Indexes are managed using the CREATE INDEX and DROP INDEX commands. The basic syntaxes are:

CREATE INDEX: CREATE [UNIQUE] INDEX <index name> ON <table name> <column list>
DROP INDEX: DROP INDEX <index name>

When dropping an index, the underlying data are not dropped or altered in any way.

Example: Creating a Non-Unique Index

To speed up the read access, you can create an index on a table column. The respective table can be empty or contain data.

An index on column PlateNumber of table Car will be created:

Code Snippet

12
CREATE INDEX PlateNumberIndex
ON Car (PlateNumber);

Example: Creating a Unique Index

The below example shows creating a unique index is equivalent to adding a UNIQUE constraint to the definition of the database table. Each such constraint automatically leads to a unique index. In addition, a unique index is created automatically for the primary key.

You can create a UNIQUE INDEX to ensure that the corresponding column cannot contain duplicate values. Unlike PRIMARY KEY, UNIQUE INDEX does not prohibit NULL values. A UNIQUE INDEX can only be created when the column contains no duplicate values.

In table Car, no duplicated plate numbers are allowed:

Code Snippet

12
CREATE UNIQUE INDEX PlateNumberIndex
ON Car (PlateNumber);

Index Creation Options

You can also create indexes as follows:

You can create multiple indexes on the same table.
You can create multi-column indexes, provided you observe the following guidelines:
- Multi-column non-unique indexes are not recommended.
- The memory cost of multi-column indexes is substantial.
- Creating a non-unique index on the most important column of a multi-column set is sufficient to achieve good performance.

Example: Dropping an Index

You have determined the index PlateNumberIndex to no longer be useful. Drop it with the DROP INDEX command:

Code Snippet

1
DROP INDEX PlateNumberIndex;

Note that DROP INDEX does not syntactically distinguish between unique and non-unique indexes.

Next lesson