Data in column store tables is separated into individual columns to support high performance on queries (query pruning) and also for better memory management (load only required column to memory). But it is also possible to subdivide the rows of column tables into separate blocks of data. We call this table partitioning. If column separation is vertical subdivision, think of table partitioning as horizontal subdivision.
Reasons for Partitioning a Column Store
Table partitioning is typically used in multiple-host systems, but it may also be beneficial in single-host systems especially on very large tables where only small sets of data are requested from large tables. Table partitioning is typically evaluated in the wider context of overall SAP HANA data management. Often modelers will not be responsible for creating and maintaining partitions but their input is incredibly important to the decisions being made about partitioning. That is why modelers must be aware of partitioning.
Partitions can be defined in a Create column table statement. You can also define partitions once a table is created using the alter statement. This can be done even if the table contains data.
Specifying table partitions with SQL
Distribution of Records over Partitions
Partitioning is transparent for SQL query definitions. This means the SQL code does not refer to partitions. However, the key purpose of partitioning tables from a modeler perspective is to improve performance of the queries. Knowing how a table is partitioned is essential for the modeler. With this knowledge they can then carefully design calculation views, functions, or procedures to exploit the way the partitions are formed. For example, a modeler that is aware of partitions should ensure that filters are applied as early as possible on the partitioned column. This means that only data for the partition is loaded to memory.
Partitions are usually defined when a column-store table is created, but it is also possible to partition a column-store table that was created but never partitioned. You do not have to drop the table to create partitions. You can partition an existing table that is empty or already contains data.
As well as creating partitions, it is also possible to:
Re-partition an already partitioned table, for example, to change the partitioning specification (hash to round-robin and so on) or to change the partitioning columns or to increase or decrease the number of partitions.
Merge partitions back to one table.
Add/delete individual partitions.
Move partitions to other hosts.
You can display partition information for a table by opening Runtime Information tab of the table definition in the Database Explorer.
With multi-level partitions, you can create additional partitions on each partition. It might help to think of this as a hierarchy of partitions. This is typically used when you first want to distribute large tables over multiple hosts (use hash partitioning to load balance), and then for each of those partitions you apply the next level of range partitions (break up by year). So now you have host/year partitioning. Also, multi-level partitioning can be used to overcome the limitation of single-level hash partitioning and range partitioning, that is, the limitation of only being able to use key columns as partitioning columns when you are dealing with keyed tables. Multi-level partitioning makes it possible to partition by a column that is not part of the primary key.