SAP HANA HDB Zoning Management (i)

SAP HANA HDB partition management:

Using the partitioning feature of the SAP HANA database, you can divide the large data tables stored in the columns horizontally into separate partitions.

The partitions of SAP Hana can only be used for column storage tables, and syntax errors can occur if the tables stored on rows are used.

error message: SAP Dbtech JDBC: [257]: SQL syntax error:not supported for row table

The SAP Hana partition table can contain multiple partitions, and each partition is independent.

A bit of partitioning:

load Balancing: using table partitioning, individual data for individual partitions can be distributed to different distribution servers. When a table is queried, it is not handled by a single server but is decomposed into the servers of all the associated primary partitions for parallel processing. Data is also dispersed across partitions to reduce data corruption.

parallelism: operation parallelism, operation in parallel using several execution threads on each table. queries, additions, modifications to large tables can be broken down into different partitions of the table to execute in parallel, making it faster to run

Partition Pruning : query analysis to see if a table matches a given partition specification. If a match is found, it is possible to determine the actual partition (save the data issue). Using this method can reduce the load of the system and the response time is also better.

Clear Zoning Processing: The SAP Hana database proactively controls partitions, such as adding partitions to store data for the upcoming one months.

Limit : A non-partitioned table cannot store more than 200 million rows of data, and by using partitions, this restriction can overcome the fact that tables are stored by distributing them to several partitions. Note, however, that each partition cannot exceed 200 million rows of data.

Performance: The performance of the SAP Hana database Incremental Consolidation depends on the size of the database primary index, and if the data is modified only in a subset of the partitions, a small portion of the data needs to be incrementally merged, which is better for the database.

Modify-delete partition:

ALTER TABLE Mytab ADD PARTITION Others
ALTER TABLE Mytab DROP PARTITION Others

to view table partitions:

SELECT * FROM sys. m_cs_partitions WHERE table_name = tablename

types of partitions provided by SAP HANA:

single-level partitioning (Single-level partitioning): HASH, Range,round-robin.

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

range partition (range partitioning):

A range partition can be used to create a dedicated partition that can be a fixed value or make a range. You can choose to place quarterly data within a single partition, or create a partition for each month of the year.

You can also choose to use a range partition for active management of partitioned tables, which can be created or deleted, such as: The database can create a partition for the upcoming one months, and new data will be inserted into the new partition.

If inserting a data does not match the condition of any one partition, SAP Hana will feed back an error message, in order to avoid this situation, you need to create a rest partition (rest partition) When you create the partition.

Note: The range partition is not suitable for load distribution.

Range partitioning restrictions on data types: only strings, integers, dates are available.

Syntax: A field that is partitioned must be part of a primary key .

To create a partition of an integer type: INT

CREATE COLUMN TABLE mytab (a int, b int, c int, PRIMARY KEY (a,b)) PARTITION by RANGE (a)
(PARTITION 1 <= VALUES < 5,
PARTITION 5 <= VALUES < 20,
PARTITION VALUE = 44,
PARTITION others)

Create a range of partitions: PARTITION <= VALUES <

Create a fixed value partition with: PARTITION value =

Create a remaining partition with: PARTITION others

To delete a range of partitions: ALTER TABLE Mytab drop partition 1 <= VALUES < 5

To delete a fixed value partition: ALTER TABLE MYTAB2 DROP partition VALUE = 44

To delete a remaining partition: ALTER TABLE MYTAB2 DROP partition others

Create a date type partition: Date

CREATE COLUMN TABLE test_range_partition_date (ID int,name VARCHAR (), Birthday date,primary KEY (id,birthday))
PARTITION by RANGE (birthday) (

PARTITION ' 2011-12-31 ' <= VALUES < ' 2012-04-01 ',

PARTITION others)

Create a String type partition: String

CREATE COLUMN TABLE test_range_patition_string (ID int,city VARCHAR (), DESCRIPTION VARCHAR (m), PRIMARY KEY (id,city))
PARTITION by RANGE (city) (
PARTITION VALUES = ' Guangzhou ',
PARTITION VALUES = ' Shanghai ',
PARTITION VALUES = ' Xiamen ',
PARTITION others
)

A partition's field is not part of a primary key:

CREATE COLUMN TABLE test_range_patition_string_nokey (ID int,city VARCHAR (), DESCRIPTION VARCHAR (m),PRIMARY KEY ( ID))
PARTITION by RANGE (city) (
PARTITION VALUES = ' Guangzhou ',
PARTITION VALUES = ' Shanghai ',
PARTITION VALUES = ' Xiamen ',
PARTITION others
)

error message: SAP Dbtech JDBC: [435] (at 140): invalid expression: partition column should is included in primary key columns: City:line 2 col (at POS 140)

no remaining partitions created: (rest partition)

CREATE COLUMN TABLE mytab_test (a int, b int, c int, PRIMARY KEY (a,b)) PARTITION by RANGE (a)
(PARTITION 1 <= VALUES < 5,
PARTITION 5 <= VALUES < 20,
PARTITION VALUE = 44
)

INSERT into Mytab_test VALUES (55,5,55); A value of 55 does not fall in any one partition.

error message: Could not execute ' insert into mytab_test values (55,5,55) '
SAP Dbtech JDBC: [2048] (AT): Column store error: [2592] Error allocating rows to parts; Could not allocate value ' in ' for column ' a '. The table has no rest part. Create a new partition for the value.

Add partitions: Add rest to the Mytab_test table partition

ALTER TABLE mytab_test ADD PARTITION others;

INSERT into Mytab_test VALUES (55,5,55);

Execution information:

Statement ' ALTER TABLE mytab_test ADD PARTITION Others ' successfully executed in MS 439µs-rows affected:0
Statement ' INSERT into Mytab_test VALUES (55,5,55) ' successfully executed in MS 119µs-rows Affected:1

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

SAP HANA HDB Zoning Management (ii) http://blog.csdn.net/qptufly/article/details/7965305