This is also one of the reasons why query execution tries to start with the evaluation of the most selective
predicate rst (for example, it is more likely that BELNR will be evaluated before MANDT, depending on the
selectivity estimations).
Conceptually, the runtime for these scans is 2*n, where n is the number of values in the table. However, the
actual runtime depends on the number of distinct values in the corresponding column. For attributes with very
few distinct values (for example,
MANDT), it might be sucient to use a small number of bits to encode the
dictionary values (for example, 2 bits). Since the SAP HANA database scan operators use SIMD instructions
during processing, multiple-value comparisons can be done at the same time, depending on the number of bits
required for representing an entry. Therefore, a scan of n records with 2 bits per value is notably faster than a
scan of n records with 6 bits (an almost linear speedup).
In the last step of query processing, the result set needs to be materialized. Therefore, for each cell (that is,
each attribute in each row), the actual value needs to be retrieved from the dictionary in constant time.
Single-Column Indexes
To improve the query processing time, the system can use the single-column indexes that are created for each
column of the key. Instead of doing the column scan operations for MANDT and BELNR, the indexes can be used
to retrieve all matching records for the given predicates, reducing the evaluation costs from a scan to a
constant-time lookup operation for the column store. The other costs (combining the two result sets,
dictionary lookup, and result materialization) remain the same.
Concatenated Indexes
When a concatenated index is available, it is preferrable to use it for query processing. Instead of having to do
two individual index-backed search operations on MANDT and BELNR and combine the results afterwards (AND),
the query can be answered by a single index-access operation if a concatenated index on (MANDT, BELNR) is
available. In this particular example this is not the case because the primary key also contains the POSNR
predicate and therefore cannot be used directly. However, in this special case, the concatenated index of the
primary key can still be exploited. Since the query uses predicates that form a prex of the primary key, the
search can be regarded internally as semantically equivalent to SELECT * FROM FOO WHERE MANDT='999'
and BELNR='xx2342' and POSNR like '%'. Since the SAP HANA database engine internally applies a
similar rewrite (with a wildcard as the sux of the concatenated attributes), the concatenated index can still be
used to accelerate the query.
When this example is actually executed in the system, the concatenated index is exploited as described above.
Indexes Versus Partitioning
Both indexes and partitioning can be used to accelerate query processing by avoiding expensive scans. While
partitioning and partition pruning reduce the amount of data to be scanned, the creation of indexes provides
additional, alternate access paths at the cost of higher memory consumption and maintenance.
Partitioning
If partition pruning can be applied, this can have the following benets:
● Scan operations can be limited to a subset of the data, thereby reducing the costs of the scan.
● Partitioning a table into smaller chunks might enable the system to represent large query results in a more
ecient manner. For example, a result set of hundreds of thousands of records might not be represented
SAP HANA Performance Guide for Developers
Schema Design
PUBLIC 17