Based on SAP HANA SPS 12 (HCP trial)
"Usually I don’t do email Q&A as this simply doesn’t help with knowledge sharing.
Instead, I advise everyone to post the question in one of the HANA related forums
(SAP Community https://answers.sap.com/questions/metadata/23925/sap-hana.html ,
JAM https://jam4.sapjam.com/groups/about_page/6UHzR2Fxra4quFAbACtxFD
or even stackoverflow http://stackoverflow.com/questions/tagged/hana ) so that the question and its answers are search- and findable.
That way everyone can benefit from this and you even might get faster and/or better answers than from just writing to me."
drop table merge_test;
CREATE COLUMN TABLE "MERGE_TEST" ("ID" INTEGER ,
"AAA" NVARCHAR(20),
"BBB" INTEGER ,
PRIMARY KEY ("ID"));
do
begin
declare ts_start timestamp = current_timestamp;
declare ts_end timestamp;
declare loops integer = 100;
declare j integer;
declare i integer;
declare with_commit integer = 1;
truncate table merge_test;
for j in 0 .. :loops do
for i in 0 .. :loops*50 do
upsert merge_test
values (:i, :i || ' - ' || :j, :i*:j)
with primary key;
end for;
if with_commit = 1 then
commit;
end if;
end for;
ts_end = current_timestamp;
-- now show some infos
select seconds_between(:ts_start, :ts_end) as duration from dummy;
select count(*) as num_rows, min(id), max(id) from merge_test;
select column_name, memory_size_in_main, memory_size_in_delta, count, distinct_count
from m_cs_all_columns
where table_name ='MERGE_TEST'
and schema_name =current_user;
select merge_count, read_count, write_count, last_merge_time
from m_cs_tables
where table_name ='MERGE_TEST'
and schema_name =current_user;
select start_time, motivation, execution_time, memory_merge, merged_delta_records, success
from m_delta_merge_statistics
where table_name='MERGE_TEST'
and start_time >= :ts_start
and schema_name = current_user;
end;
with_commit = 1
a commit is done after each iteration of the outer loop./*
DURATION
386
NUM_ROWS MIN(ID) MAX(ID)
5001 0 5000
COLUMN_NAME MEMORY_SIZE_IN_MAIN MEMORY_SIZE_IN_DELTA COUNT DISTINCT_COUNT
ID 1064 3733044 505101 5001
AAA 0 17607386 505101 -1
BBB 0 5571068 505101 -1
$trex_udiv$ 232 8889016 0 0
$rowid$ 768 15676428 505101 505101
MERGE_COUNT READ_COUNT WRITE_COUNT LAST_MERGE_TIME
0 7 505101 2016-12-20 03:15:41.986
START_TIME MOTIVATION EXECUTION_TIME MEMORY_MERGE MERGED_DELTA_RECORDS SUCCESS
*/
/*
DURATION
349
NUM_ROWS MIN(ID) MAX(ID)
5001 0 5000
COLUMN_NAME MEMORY_SIZE_IN_MAIN MEMORY_SIZE_IN_DELTA COUNT DISTINCT_COUNT
ID 723716 9132 5001 5001
AAA 741076 10052 5001 5001
BBB 711828 9132 5001 5001
$trex_udiv$ 273432 1912 0 0
$rowid$ 1504 9132 5001 5001
MERGE_COUNT READ_COUNT WRITE_COUNT LAST_MERGE_TIME
5 30 505111 2016-12-20 03:40:22.85
START_TIME MOTIVATION EXECUTION_TIME MEMORY_MERGE MERGED_DELTA_RECORDS SUCCESS
2016-12-20 03:36:22.166 AUTO 54 FALSE 80016 TRUE
2016-12-20 03:37:22.24 AUTO 88 FALSE 90018 TRUE
2016-12-20 03:38:22.349 AUTO 119 FALSE 85017 TRUE
2016-12-20 03:39:22.49 AUTO 157 FALSE 85017 TRUE
2016-12-20 03:40:22.67 AUTO 186 FALSE 85017 TRUE
*/
alter table merge_test disable automerge;
/*
DURATION
325
NUM_ROWS MIN(ID) MAX(ID)
5001 0 5000
COLUMN_NAME MEMORY_SIZE_IN_MAIN MEMORY_SIZE_IN_DELTA COUNT DISTINCT_COUNT
ID 1064 3733044 505101 5001
AAA 0 17607386 505101 -1
BBB 0 5571068 505101 -1
$trex_udiv$ 232 8810536 0 0
$rowid$ 768 15676428 505101 505101
MERGE_COUNT READ_COUNT WRITE_COUNT LAST_MERGE_TIME
0 15 505101 2016-12-20 03:49:36.914
START_TIME MOTIVATION EXECUTION_TIME MEMORY_MERGE MERGED_DELTA_RECORDS SUCCESS
*/
merge delta of merge_test;
/*
START_TIME MOTIVATION EXECUTION_TIME MEMORY_MERGE MERGED_DELTA_RECORDS SUCCESS
2016-12-20 03:56:09.435 HARD 46 FALSE 5001 TRUE
*/
merge delta of merge_test;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
37 | |
19 | |
13 | |
13 | |
12 | |
10 | |
10 | |
9 | |
8 | |
8 |