Shrink your Tables with SAP HANA SP08

[abanip]

Shrink your Tables with SAP HANA SP08

Abani Pattanayak, SAP HANA COE (Delivery)

Jako Blagoev, SAP HANA COE (AGS)

Introduction:

Yes, with HANA SP08 you can significantly reduce size of your FACT tables significantly. Depending on the the size of the primary key and cardinality of the dataset, you can get significant (up to 40%) savings in static in static memory usage.

This savings in memory is compared to HANA SP07 or earlier revisions.

So what's the catch?

There is no catch.

The saving is based on how the primary key of the table is stored in HANA. Please check the biggest FACT table in your SP07 or SP06 HANA database, the size of the primary key will be around 30 - 40 % of the total size of the table.

With HANA SP08, we can eliminate this 30 - 40% memory taken by the primary key. So there is no negative performance impact on query performance.

Show me the Money (What's the trick)?

You need to recreate the primary key of the table with INVERTED HASH option.

CREATE COLUMN TABLE "SAPSR3"."MY_FACT_TABLE"(

        "STORID" NVARCHAR(10),

        "ORDERID" NVARCHAR(15),

        "SEQ" NVARCHAR(10),

        "CALMONTH" NVARCHAR(6),

        "CALDAY" NVARCHAR(8),

        "COUNTRY" NVARCHAR(3),

        "REGION" NVARCHAR(3),

..

..

        PRIMARY KEY INVERTED HASH ("STORID",

        "ORDERID",

        "SEQ",

        "CALMONTH",

        "CALDAY",

        "COUNTRY",

        "REGION"))

WITH PARAMETERS ('PARTITION_SPEC' = 'HASH 8 STORID')

;

You can use ALTER TABLE command to drop and recreate primary key of the table.

However, if you have a scale-out system or a really BIG fact table with billions of record, We'd highly recommend to create a NEW table with INVERTED HASH Primary Key and then copy the data over to the new table. Then rename the tables.

Result

The following is the result of updating the primary key in a customer project. As you see below, the saving in static memory is around 531GB over 1980GB.

So overall, there is a saving of at least 2-nodes (0.5 TB each) in a 9-node scale out system.

The best part of this exercise, there is no negative performance impact on query performance.

Note: I'd suggest you review your existing system and evaluate if you can take advantage of this feature.