- SAP HANA Admin Tutorial
- SAP HANA Admin - Home
- SAP HANA Admin - Overview
- Architecture Overview
- Multitenant Database
- Multiple Host Systems
- SAP HANA Admin - System Mngmt
- Multitenant DB Container Mgmt
- Starting a HANA System
- Stopping a HANA System
- License Keys
- Monitoring the HANA System
- SAP HANA Admin - Table Mngmt
- SAP HANA Admin - Table Partition
- SAP HANA Admin - Table Replication
- Data Compression
- Solman Integration
- SAP HANA Admin - Lifecycle Mngmt
- Securing HANA System
- User Provisioning
- Authentication Methods
- Auditing Activities
- Backing Up HANA System
- Recovery HANA System
- HANA XS Application Service
- Data Provisioning
- Smart Data Access
- New Remote System Connection
- Integration with Hadoop
- Key Commands
- Job Responsibilities
- SAP HANA Admin Useful Resources
- SAP HANA Admin - Quick Guide
- SAP HANA Admin - Useful Resources
- SAP HANA Admin - Discussion
SAP HANA Admin - Table Partition
Using Partitioning, you can split column store tables into smaller tables in a multiple host system. You can even divide tables into smaller more manageable parts using partitioning. Partitioning can be done using DML SQL statements.
When a table is partitioned it contains different set of rows for each part and Partitioning can be done based on different algorithms - Hash Partitioning, Single Level or multilevel Partitioning.
Following are the advantages of using Table Partitioning −
You can determine if a query can be analyzed in a particular partition. Using this method, you can reduce the load on the system and hence improve the response time.
Example − When you partition a table based on the year, a query is executed to analyze the data for a particular year. You can use specific partition and hence query performance is improved.
You can divide individual partitions on multiple hosts and hence a query is not processed by a single server, results in better load balancing on all the servers hosting table partitions.
In a column store table, maximum number of rows that can be entered is around 2 billion. You can overcome this by distributing the rows of a column table on multiple partitions and hence the size limit is increased to 2 billion for each partition.
Improved Delta Merge Operation
During delta merge, if data is only modified for some partitions, you need to merge less partitions in delta merge.
Parallel Processing of Queries
Using partitions, you can run multiple queries in parallel and hence improve the response time.
Let us now discuss the types of partionining.
Single Level Partitioning
There are different types of single level partitioning available in SAP HANA system −
- Hash Partitioning
- Round Robin Partitioning
- Range Partitioning
In Hash partitioning, rows are distributed equally to perform load balancing. You don’t require the detailed information about table content in this Partitioning type.
As compared to Hash Partitioning, in Round Robin Partitioning rows are equally distributed to each partition and new rows are equally assigned to each partition.
To create 4 partitions of a table using Round Robin, you can use the following SQL queries. No primary keys should be defined.
CREATE COLUMN TABLE TABLENAME (a INT, b INT, c INT) PARTITION BY ROUNDROBIN PARTITIONS 4
This command will create 4 partitions of a table.
CREATE COLUMN TABLE Table_Name (a INT, b INT, c INT, PRIMARY KEY (a,b)) PARTITION BY HASH (a, b) PARTITIONS 4
This will create 4 partitions on column a and b, and you need to specify at least one column.
In Range partitioning, you create dedicated partitions for certain values and you should have in-depth knowledge of table contents for partitioning columns.
Example − Creating one partition for each calendar month.
CREATE COLUMN TABLE TABLE_NAME (a INT, b INT, c INT, PRIMARY KEY (a,b)) PARTITION BY RANGE (a) (PARTITION 1 <= VALUES < 10, PARTITION 10 <= VALUES < 20, PARTITION VALUE = 50, PARTITION OTHERS)
This will create 4 partitions, one for partition range 1 to 10, 2nd from 10 to 20, 3rd for value 50, and 4th for all other values.
In multilevel partitioning, you can overcome the limitation of HASH and RANGE single level partitioning to use key columns as partitioning columns. Using multilevel partitioning, you can partition a column that is not a primary key. The most common type of multilevel partitioning is HASH-Range Partitioning.
In Hash-Range multilevel partitioning, you implement Hash partitioning at the first level to implement load balancing and Range partitioning at the second level to implement timebased partitioning.
CREATE COLUMN TABLE Table_name (a INT, b INT, c INT, PRIMARY KEY (a,b)) PARTITION BY HASH (a, b) PARTITIONS 4, RANGE (c) (PARTITION 1 <= VALUES < 10, PARTITION 10 <= VALUES < 20)
Instead of using Hash partitioning at the first level, you can also use Round Robin partitioning and it is known as Round Robin-Range multilevel partitioning.
CREATE COLUMN TABLE Table_name (a INT, b INT, c INT) PARTITION BY ROUNDROBIN PARTITIONS 4, RANGE (c) (PARTITION 1 <= VALUES < 10, PARTITION 10 <= VALUES < 20)
Kickstart Your Career
Get certified by completing the courseGet Started