Column Store and Row Store FAQ's
Q) Can a table be assigned in both column and row store?
Ans) No, only one store can be assigned to a table at any time which can be either column store or row store.
Q) Do we need to adjust the application while moving a table between column store and row store?
Ans) No, you don't have to do any adjustment in the application coding as the location of a table in row store or columns store is transparent to the application.
Q) Can we transport the assignment of a table to row or column store in SAP ABAP Environments?
Ans) Assignment of a table in SAP Netweaver 7.40 to row or column store is a technical setting in the ABAP dictionary, therefore, it can be transported between systems. Technically this information is stored in column ROWORCOLST of table DD09L. Report RUT_ROWORCOLST_FIX_UNDEF is provided by SAP document 2122576 which is used to populate this technical flag based on the actually used store.
Q) What should we consider for indexes on column store and row store tables?
Ans) The optimal index design on column store and row store tables are different:
- Column store prefers single-column indexes over multi-column indexes.
- Due to efficient column scans column store tables require overall fewer indexes as compared to row store tables.
- Different index types in row store (e.g. CPBTREE) and column store (e.g. INVERTED VALUE).
Therefore we should always consider the possibility of changes to the index design while moving a table between the stores.
Q) What should we consider for LOBs on column and row store tables?
Ans) LOB columns and features such as hybrid LOBs/disk LOBs are available for both column store and row store.
Note: See SAP Document 2220627 for more info.
Q) Do the column store and row store tables are kept in memory permanently?
Ans) The row store is loaded in the memory at the time of startup and kept permanently, therefore, it is not possible to unload parts of the row store.
The column store table columns are loaded while they are accessed or based on a reload algorithm, therefore, it can be unloaded when memory is required for other purposes, when they are explicitly unloaded or after a defined retention period.
Q) How can the memory of column store and row store are technically implemented?
Ans) Shared memory is used by the tables and the catalog of the row store whereas heap memory is used by the column store and other row store components like indexes.
Q) Where in the SAP HANA scale-out environments do the column store and row store are located?
Ans) The columns store is distributed across all nodes where as row store located on master node. This is normal and recommended configuration
Please note it is possible to technically move row store tables to slave nodes. Due to this a minimalistic row store (with only some dictionary tables) exists on every slave node. But it is not recommended to utilize the row store and slave nodes because running table redistribution tasks with inappropriate table placement settings can result in automatic row store distribution across all nodes.
Q) How can we re-organize the column store and row store?
Ans) The column store tables are implicitly reorganized during delta merge operations t4herefore we don't need to explicitly reorganize it. Yes in some cases, like for cleaning up empty slots after a mass deletion, it can be useful to force a complete reorganization of a table based on a optimize compression run.
The row store is significantly fragmented for example because of garbage collection issue when the table data is archived / deleted or when tables are moved from the row store to the column store.
Note: SAP Document provides full description about how to defragment the row store.
Q) What should we consider the row store reorganization in system replication scenarios?
Ans) In the case for system replication scenarios a reorganization of the row store on the primary site will be replicated to the secondary site, therefore there is no need to perform a row store reorganization on the secondary site. Executing the row store re-organization on the secondary site would be of no help as it has to be unregistered before and after re-registering it the row store from primary site will be replicated again.
Q) What are the limits of column store and row store?
- The are different limits for column store and row store. For example, the total size in row store is limited whereas there are no limits for the size of column store.
- The number of records per column store table partition is limited by 2 billion whereas there is no such limit for row store.
Q) Does the column store and row store tables also stored on disk?
Ans) Yes all the table data also needs to be stored on disk otherwise you will lose information after a crash or shutdown. The content of the table stores in memory is regularly synchronized with the information on disk using savepoints.
The table size can differ between in memory and on disk in both the row and column store.
Q) How much time does it take to reorganize row store?
Ans) The time taken by reorganization of the row store depends on many factors such as row store size, amount of moved pages and available resources and so it is hard to provide a general rule of thumb. In order to get a good figure, you can perform a test in your individual environment.
Note: The SAP Document 2380176 provide details about the row store reorganization steps and performance.