The types of partitioning include: range partitioning, hash-range and range-range partitioning, and time-selection partitioning.
In this module we will create a multi-store table that uses single level Range partitioning. With single level range partitioning, at least one range partition must be in the DEFAULT STORAGE and one range partition in extended storage. Only range partitions are supported when it comes to single-level partitioning. The diagram below shows the ranges that will be defined.

We will store ranges 1 to 4 in EXTENDED STORAGE and range 5 and “OTHERS
” in the in-memory DEFAULT STORAGE. The “OTHERS
” range is optional but if it is created, it is always in DEFAULT STORAGE.
Since multi-store tables have been implemented on top of existing HANA and dynamic tiering capabilities, existing limitations of HANA and dynamic tiering are also inherited by multi-store tables. The following are unsupported data types:
- TIMESTAMP
- SHORTTEXT
- TEXT
- ARRAY
The following are limitations set on table and index definitions:
- No full text or geocode indexes
- Several clauses specific to in-memory tables are not supported, other clauses will only apply to either the in-memory partition or the EXTENDED STORAGE partitions
- No triggers on partitions in EXTENDED STORAGE
To create the single level range partitioned multi-store table, copy the following into the SQL console and execute it.
CREATE COLUMN TABLE "TPCH"."LINEITEM_MS" (
L_ORDERKEY integer not null,
L_PARTKEY integer not null,
L_SUPPKEY integer not null,
L_LINENUMBER integer not null,
L_QUANTITY decimal(15,2) not null,
L_EXTENDEDPRICE decimal(15,2) not null,
L_DISCOUNT decimal(15,2) not null,
L_TAX decimal(15,2) not null,
L_RETURNFLAG char not null,
L_LINESTATUS char not null,
L_SHIPDATE date not null,
L_COMMITDATE date not null,
L_RECEIPTDATE date not null,
L_SHIPINSTRUCT char(25) not null,
L_SHIPMODE char(10) not null,
L_COMMENT varchar(44) not null,
primary key (L_ORDERKEY, L_LINENUMBER)
)
PARTITION BY RANGE ("L_ORDERKEY")
(
USING DEFAULT STORAGE
( PARTITION OTHERS,
PARTITION 2000000 <= VALUES < 5000000
)
USING EXTENDED STORAGE
( PARTITION 0 <= VALUES < 50000,
PARTITION 50000 <= VALUES < 200000,
PARTITION 200000 <= VALUES < 500000,
PARTITION 500000 <= VALUES < 2000000
)
);
The above script will create a table called “LINEITEM_MS
”. The partitions defined for DEFAULT STORAGE will hold any records with L_ORDERKEY
values between 2,000,000 and 4,999,999, or L_ORDERKEY
values that do not fall under any other specified range. The partitions defined for EXTENDED STORAGE will hold any records with L_ORDERKEY
values between 0 and 49,999; 50,000 and 199,999; 200,000 and 499,999; or 500,000 and 1,999,999.
After executing the “CREATE TABLE
” statement, navigate to Catalog > TPCH > Right-click Tables > Show Tables.
Make sure you see newly created “LINEITEM_MS
” table as seen below.
Multi-store tables are not classified as type EXTENDED
.