[HANA] Scale-out table relocation

TABLE GROUP 지정

USER의 GROUP NAME별 위치 지정

BENCH / GRP_M - Master
          / GRP_S - Slave

 

Command

-- User별 Default Location 지정
ALTER SYSTEM ALTER TABLE PLACEMENT (SCHEMA_NAME => 'BENCH') SET(LOCATION=>'master'); 

-- User - GROUP별 Default Location 지정
ALTER SYSTEM ALTER TABLE PLACEMENT (SCHEMA_NAME => 'BENCH', GROUP_NAME => 'GRP_M') 
SET(LOCATION=>'master');
ALTER SYSTEM ALTER TABLE PLACEMENT (SCHEMA_NAME => 'BENCH', GROUP_NAME => 'GRP_S') 
SET(LOCATION=>'slave');

-- 위치 확인
select schema_name, group_name, group_type, location from table_placement;

 

시나리오> User의 Default 위치 지정

시나리오> User - GROUP의 Default 위치 지정

 

GROUP 지정 해제

ALTER SYSTEM ALTER TABLE PLACEMENT (SCHEMA_NAME => 'BENCH', GROUP_NAME => 'ABC') UNSET; 

시나리오> GROUP 지정

 

---

TABLE별 GROUP 지정

GROUP별 테이블 생성

BENCH / GRP_M - Master - A1
          / GRP_S - Slave     - A2

CREATE COLUMN TABLE "BENCH".A1 (A INT) GROUP NAME GRP_M;
CREATE COLUMN TABLE "BENCH".A2 (A INT) GROUP NAME GRP_S;

-- 확인
select * from table_groups;
select host, port, table_name, memory_size_in_total 
from m_cs_tables where schema_name = 'BENCH' and table_name like 'A%';
select * from m_table_locations where schema_name = 'BENCH' and table_name like 'A%';

시나리오> GROUP 지정하여 테이블 생성

---

단일 테이블 이동

테이블 physical하게 이동

alter table "BENCH".a3 move to location 'hana1-slave:30003' physical;
select * from m_table_locations where schema_name = 'BENCH' and table_name like 'A3';

시나리오> Table생성 후 그룹과 다르게 위치 지정

 

테이블 GROUP 지정 없이 생성하여 이후 Node 이동

시나리오> GROUP 지정없이 생성

 

시나리오> GROUP이 없는 상태에서 임의로 GROUP 지정

 

시나리오> Physical한 이동 수행

---

RULE과 다르게 설정된 TABLE Location 확인하여 조정

-- Balance landscape 확인
call reorg_generate(6, 'SCHEMA_NAME=>BENCH;NO_SPLIT');

-- Reorg plan 확인
select * from reorg_plan;

-- Reorg 수행
call reorg_execute(?);

-- 수행상태 확인
select * from reorg_overview;
select * from reorg_steps ;

- 수행결과 확인
select * from m_table_locations where schema_name = 'BENCH';

[참고] https://help.sap.com/viewer/6b94445c94ae495c83a19646e7c3fd56/2.0.04/en-US/667bfd8f3adc47f8af4c753447e40693.html?q=table%20redistribution%20algorithms

SAP Help Portal

Table Redistribution Algorithms and OptionsNumAlgorithm NameDescription

Num	Algorithm Name	Description
6	Balance landscape	This function checks if tables in the landscape are placed on invalid severs according to the table placement rules, and checks if a split or merge is necessary in order to achieve optimal positions for the partitions and tables and to evenly distribute tables across the indexserver hosts. Options: TOP | USE_GROUP_ADVISOR | SCHEMA_NAME | TABLE_NAME | GROUP_NAME | GROUP_TYPE | GROUP_SUBTYPE | RECALC | NO_PLAN | NO_SPLIT | SCOPE
1	Add server	Run this check after adding one or more index servers to the landscape. If new partitions can be created a plan will be generated to split the tables and move the new partitions to the newly added indexservers. Options: TOP | USE_GROUP_ADVISOR | SCHEMA_NAME | TABLE_NAME | GROUP_NAME | GROUP_TYPE | GROUP_SUBTYPE | RECALC | NO_PLAN
2	Clear server	Moves all partitions from a named server to other servers in the landscape. Options: USE_GROUP_ADVISOR
4	Save	Save current landscape setup. No optional parameter.
5	Restore	Restore a saved landscape setup. Enter the plan ID value as the optional parameter value.
7	Check number of partitions	This function checks if partitioned tables need to be repartitioned and creates a plan to split tables if the partitions exceed a configured row count threshold. No optional parameter.
12	Execute Group Advisor	Calls the Group Advisor and creates an executable plan from its output. See Group Advisor in the following topic.
14	Check table placement	Check current landscape against table placement rules and (if necessary) provide a plan to move tables and partitions to the correct hosts. Additional Options: LEAVE_UNCHANGED_UNTOUCHED | KEEP_VALID | NO_SPLIT
15	Rerun plan	Rerun failed items from previously executed plans. Option: RERUN_ALL
16	Housekeeping	Perform housekeeping tasks. Additional privileges may be required for specific actions. Housekeeping Options: OPTIMIZE_COMPRESSION | DEFRAG | LOAD_TABLE | MERGE_DELTA | ALL

Optional Parameter

Option	Type	Detail
TOP	String	Restrict redistribution to the top n items. Example: CALL REORG_GENERATE(6,'TOP=>4')  executes algorithm #6 and optimizes only the top four table groups.
USE_GROUP_ADVISOR	String	Calls the Group Advisor and creates an executable plan from its output.
SCHEMA_NAME	String	Restrict redistribution to the named schema(s) - comma-separated list.
TABLE_NAME	String	Restrict redistribution to the named table(s) - comma-separated list.
GROUP_NAME	String	Restrict redistribution to the named group(s) - comma-separated list.
GROUP_TYPE	String	Restrict redistribution to the named group types(s) - comma-separated list.
GROUP_SUBTYPE	String	Restrict redistribution to the named sub types(s) - comma-separated list.
RECALC	True / False	If true then recalculate the landscape data of the last REORG_GENERATE run. This option works only if REORG_GENERATE has been called before within the same connection session. This parameter can be used to speed up plan generation with different parameters.
NO_PLAN	True / False	If true then the planning stage of generating the plan is skipped. This can be used with external tools when landscape data needs to be collected and a distribution must be calculated but might be modified.
SCOPE	Keyword	Use one or more of the following values (see example which follows) to restrict the scope of the redistribution to include only the named items specified by these keywords. The default value is 'ALL' so that all tables visible to the user are included in the redistribution. LOADEDTables which are loaded or partially loadedUNLOADEDTables which are not loadedFILLEDTables with a record count greater than 10EMPTYTables with a record count less than or equal to 10USEDTables with a total execution count greater than 10UNUSEDTables with a total execution count of less than or equal to 10LOBTables with LOB columnsNOLOBTables without LOB columns

 

 

시나리오> Reorg 시나리오 생성 및 확인

 

시나리오> Reorg 수행 및 수행상태 확인

시나리오> 수행결과 확인