on 04-13-2018 3:42 AM
Hi All,
From below notes, we can convert VARBINARY to BLOB, but can we revert the change back to VARBINARY after converted to BLOB?
2220627 - FAQ: SAP HANA LOBs
2375917 - How-To: Converting SAP HANA VARBINARY columns to LOB
Reason of asking is we would like to convert the CLUSTD column from VARBINARY to BLOB as it consume huge amount of HANA memory (200++GB) and afraid we need to reverse it incase of any query performance degradation and implication...
Your kind input is very much appreciated.
Thanks!
Nicholas Chang
Note 2375917 makes it clear that a copy of the old table structure with VARBINARY has to be kept until the conversion success including performance aspects has been verified. Instead of converting back and forth that should be the fallback option.
Also, this conversion effort, as any other changes, should be done on DEV and QA systems before the production system. If there's any doubt about the success of the change in either of these systems, don't push it to production.
Finally, yes it is possible to change data from LOB to VARBINARY as long as the size of the data fits into VARBINARY fields.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
That can be done with the same command as the VARBINARY to LOB conversion:
ALTER TABLE ALTER <column>
E.g.
select column_name, data_type_name
from table_columns
where table_name='RAWSTUFF';
/*
COLUMN_NAME DATA_TYPE_NAME
DATA BLOB
ID BIGINT
*/
select * from rawstuff;
/*
ID DATA
1 Àÿî
*/
alter table rawstuff alter ( data VARBINARY (4000));
/*
COLUMN_NAME DATA_TYPE_NAME
DATA VARBINARY
ID BIGINT
*/
select * from rawstuff;
/*
ID DATA
1 C0FFEE
*/
And with that, I'm getting my second coffee this morning 🙂
Cheers,
User | Count |
---|---|
76 | |
8 | |
8 | |
8 | |
7 | |
6 | |
5 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.