Starting with SAP HANA 2.0 we can now partition a single table between in-memory storage and SAP HANA Extended Storage (AKA.. Dynamic Tiring). This is an excellent feature because it simplifies the management and code required to manage multiple tables and subsequently bridge them together using additional code. Basically, we can establishing archiving within our tables without the need to move the data into separate tables.

Prior to SAP HANA 2.0, we would have to create two identical tables. One would be provisioned in-memory and the other in extended storage. We then had to create catalog views or SAP HANA Information Views to logically merge the tables together. With HANA 2.0 we no longer have to do this.

Lets take a look at a few details and examples:

Some Background Info for the example.

1. A table exists in the schema “BOOK_USER”
2. The name of the table in the example SQL is: “dft.tables::FACT_SALES_PARTITION”
3. I am creating a range partition on the column “ORDERYEAR”.
4. Some partitions will exists in-memory, others will exist within extended storage.
5. The “ORDERYEAR” Column is part of the primary key set.
6. Extended Storage is enabled in the cluster.

The execution plans for multistore tables, having partitions stored in both, effectively performs a UNION ALL automatically to bridge (UNION) the data together. As you can see in the following plan visualization, a node called “Materialized Union All” is created to bridge the data from both data store types together. This plan was generated from the query:

SELECT * FROM “BOOK_USER”.”dft.tables::FACT_SALES_PARTITION”

Adding filters to the query, that directly reference the partition column, seam to aid in the execution. For example, filtering on our partitioning column “ORDERYEAR” changes the execution and the UNION ALL is not executed. This means the optimizer is smart enough to remove the “Materialized Union All” from the execution because the query did not directly ask for data within the partitions hosted in extended storage.

SELECT * FROM “BOOK_USER”.”dft.tables::FACT_SALES_PARTITION” WHERE ORDERYEAR = 2016

If the query does not perform a filter on the column used for the partition, the execution plan will look in extended storage and perform the UNION ALL operation. However, in my example below, the filter finds no matching data within extended storage and moves zero (0) records from the extended storage store into the “Materialized Union All” execution node. Because no data was moved from extended storage and into the “Materialized Union All” node, the execution was slightly faster than the first example.

SELECT * FROM “BOOK_USER”.”dft.tables::FACT_SALES_PARTITION” WHERE ORDERDATEKEY >= 20160101

Given the amount of time it required to find this SQL for this new option within the documentation, I thought I would share a few example SQL statements I used with a test table. The first three SQL statements, creates a partition and then move partitions in and out of extended storage. I also included a CREATE TABLE statement to see how this would be accomplished from scratch.

For Existing Tables we need to start by creating a normal range partition.

STEP 1

— Start by Creating all range partitions in STANDARD STORAGE (In-Memory)

ALTER TABLE “BOOK_USER”.”dft.tables::FACT_SALES_PARTITION” PARTITION BY RANGE (“ORDERYEAR”)

( USING DEFAULT STORAGE (

PARTITION ‘1900’ <= VALUES < ‘2010’,

PARTITION ‘2010’ <= VALUES < ‘2011’,

PARTITION ‘2011’ <= VALUES < ‘2999’)

);

STEP 2

— Move two partition into EXTENDED STORAGE    

ALTER TABLE “BOOK_USER”.”dft.tables::FACT_SALES_PARTITION”

ALTER PARTITION (“ORDERYEAR”)

USING EXTENDED STORAGE (

‘1900’ <= VALUES < ‘2010’,

‘2010’ <= VALUES < ‘2011’);

OPTIONAL STEP 3

— Move a partition back into DEFAULT (in-memory) STORAGE    

ALTER TABLE “BOOK_USER”.”dft.tables::FACT_SALES_PARTITION”

ALTER PARTITION (“ORDERYEAR”)

USING DEFAULT STORAGE (

‘2010’ <= VALUES < ‘2011’);

For a new Table, use the following to create a table with a multi-store range partition

–Create Table with Multistore Range Partitions

CREATE COLUMN TABLE “BOOK_USER”.”dft.tables::FACT_SALES_PARTITION”

(“PRODUCTKEY” INTEGER CS_INT,

“ORDERDATEKEY” INTEGER CS_INT,

“DUEDATEKEY” INTEGER CS_INT,

“SHIPDATEKEY” INTEGER CS_INT,

“CUSTOMERKEY” INTEGER CS_INT,

“CURRENCYKEY” INTEGER CS_INT,

“SALESTERRITORYKEY” INTEGER CS_INT,

“SALESORDERNUMBER” VARCHAR(20) NOT NULL ,

“SALESORDERLINENUMBER” INTEGER CS_INT NOT NULL ,

“REVISIONNUMBER” INTEGER CS_INT,

“ORDERQUANTITY” INTEGER CS_INT,

“UNITPRICE” DECIMAL(19, 4) CS_FIXED,

“EXTENDEDAMOUNT” DECIMAL(19, 4) CS_FIXED,

“UNITPRICEDISCOUNTPCT” DOUBLE CS_DOUBLE,

“DISCOUNTAMOUNT” DOUBLE CS_DOUBLE,

“PRODUCTSTANDARDCOST” DECIMAL(19,4) CS_FIXED,

“TOTALPRODUCTCOST” DECIMAL(19,4) CS_FIXED,

“SALESAMOUNT” DECIMAL(19,4) CS_FIXED,

“ORDERYEAR” INTEGER CS_INT NOT NULL ,

PRIMARY KEY INVERTED VALUE (“ORDERYEAR”,

“SALESORDERNUMBER”,

“SALESORDERLINENUMBER”))

UNLOAD PRIORITY 5 AUTO MERGE

PARTITION BY RANGE (“ORDERYEAR”)

(USING EXTENDED STORAGE

(PARTITION ‘1900’ <= VALUES < ‘2010’,

PARTITION ‘2010’ <= VALUES < ‘2011’)

USING DEFAULT STORAGE

(PARTITION ‘2011’ <= VALUES < ‘2999’) ) WITH PARAMETERS (‘PARTNAMES’=(‘4′,’5′,’3’))

Managing multistore tables (SAP HANA Help)