PARTITIONS IN SAP HANA

Different types of partitioning supported by SAP HANA 

There are 2 types of partitioning supported by SAP HANA

1)      Single-Level Partitioning.

i)                    Hash partitioning

Hash partitioning is used to equally distribute rows to partitions for load               balancing and overcoming the 2 billion rows limitation.

 

-- Hash

CREATE COLUMN TABLE "QBEXSQL"."EMPHASH"

(

"EMPID" INT PRIMARY KEY,

"EMPNAME" VARCHAR(20)

) PARTITION BY HASH ("EMPID") PARTITIONS 4;

 

 

INSERT INTO "QBEXSQL"."EMPHASH"

VALUES (1,'Pratap');

INSERT INTO "QBEXSQL"."EMPHASH"

VALUES (2,'Pratap');

INSERT INTO "QBEXSQL"."EMPHASH"

VALUES (3,'Pratap');

INSERT INTO "QBEXSQL"."EMPHASH"

VALUES (4,'Pratap');
 

ii)                  Range Partitioning

       Range partitioning can be used to create dedicated partitions for certain values or certain value ranges.

For example: a range partitioning scheme can be chosen to create one partition per month of the year. The range partitioning is not well suited for load distribution. The range partition specification usually takes ranges of values to determine one partition, e.g. 1 to 10. Range partitioning is similar to hash partitioning in that the partitioning column has to be part of the primary key.

 

Range partitioning also has restrictions on the data types that can be used.

 Only strings, integers and dates are allowed.

 -- Range Partitioning

---------------------

 

CREATE COLUMN TABLE "QBEXSQL"."RANGEPART"

(

"ORDERID" INT NOT NULL,

"ORDERDATE" DATE NOT NULL,

"NETSALES" DECIMAL(10,2),

 PRIMARY KEY ("ORDERID","ORDERDATE")

) PARTITION BY RANGE ("ORDERDATE")

 

(

PARTITION '2015-01-01' <= VALUES < '2015-06-01',

PARTITION VALUE = '2015-07-15', PARTITION OTHERS

);

 

 

INSERT INTO "QBEXSQL"."RANGEPART"

VALUES (1,'2015-01-15',15000.00);

 

INSERT INTO "QBEXSQL"."RANGEPART"

VALUES (2,'2015-03-15',15000.00);

INSERT INTO "QBEXSQL"."RANGEPART"

VALUES (3,'2015-07-15',15000.00);

 

INSERT INTO "QBEXSQL"."RANGEPART"

VALUES (4,'2016-07-15',15000.00);

INSERT INTO "QBEXSQL"."RANGEPART"

VALUES (5,'2016-07-15',15000.00);

INSERT INTO "QBEXSQL"."RANGEPART"

VALUES (6,'2016-07-15',15000.00);

-- Month

 

CREATE COLUMN TABLE "QBEXSQL"."RANGEPARTMONTH"

(

"ORDERID" INT NOT NULL,

"ORDERDATE" DATE NOT NULL,

"NETSALES" DECIMAL(10,2),

 PRIMARY KEY ("ORDERID","ORDERDATE")

) PARTITION BY RANGE (MONTH("ORDERDATE"))

 

(

PARTITION '2015-01' <= VALUES < '2015-06',

PARTITION VALUE = '2015-07', PARTITION OTHERS

);

 

INSERT INTO "QBEXSQL"."RANGEPARTMONTH"

VALUES (1,'2015-01-15',15000.00);

 

INSERT INTO "QBEXSQL"."RANGEPARTMONTH"

VALUES (2,'2015-03-15',15000.00);

INSERT INTO "QBEXSQL"."RANGEPARTMONTH"

VALUES (3,'2015-07-15',15000.00);

 

INSERT INTO "QBEXSQL"."RANGEPARTMONTH"

VALUES (4,'2016-07-15',15000.00);

INSERT INTO "QBEXSQL"."RANGEPARTMONTH"

VALUES (5,'2016-07-15',15000.00);

INSERT INTO "QBEXSQL"."RANGEPARTMONTH"

VALUES (6,'2016-07-15',15000.00);

 

iii)                 Round robin Partitioning

     Round robin is similar to hash partitioning as it is  used for an equal distribution of rows to parts. When using this method it is not required to specify partitioning columns.

-- Round Robin:

---------------

 

-- No Primary key

 

CREATE COLUMN TABLE "QBEXSQL"."ROUNDROBIN"

(

"ORDERID" INT ,

"ORDERDATE" DATE,

"NETSALES" DECIMAL(10,2)

) PARTITION BY ROUNDROBIN PARTITIONS 4;

 

INSERT INTO "QBEXSQL"."ROUNDROBIN"

VALUES (1,'2015-01-15',15000.00);

 

INSERT INTO "QBEXSQL"."ROUNDROBIN"

VALUES (2,'2015-03-15',15000.00);

INSERT INTO "QBEXSQL"."ROUNDROBIN"

VALUES (3,'2015-07-15',15000.00);

 

INSERT INTO "QBEXSQL"."ROUNDROBIN"

VALUES (4,'2016-07-15',15000.00);

INSERT INTO "QBEXSQL"."ROUNDROBIN"

VALUES (5,'2016-07-15',15000.00);

INSERT INTO "QBEXSQL"."ROUNDROBIN"

VALUES (6,'2016-07-15',15000.00);

2)      Multi-Level Partitioning.

       We can combine the single level partitions with one another to get multi-level       partitioning.