HANA database row tables ratio

[nicolasberthier]

The other day, one of my colleagues casually asked me what is the ratio of row tables/all tables for a typical HANA database. Good question!

In our S/4HANA 1809 sandbox system running on HANA 2.0 SPS 03 DR 33 (that contains minimal customization), the following queries give:

"SELECT COUNT(*) FROM TABLES WHERE TABLE_TYPE = 'ROW'"

>>5706



"SELECT COUNT(*) FROM TABLES WHERE TABLE_TYPE = 'COLUMN'"

>>125927



"SELECT COUNT(*) FROM TABLES"

>>131633

We see that for this system, row tables account for 4.33% of all tables. I genuinely thought the ratio would be higher!

Generally, column tables are used whenever column operations can be performed against huge volumes of data. Column tables tend to contain business data.

Row tables tend to contain data that cannot be compressed efficiently (unstructured data with non-similar or distinct values, and a small number of records). SAP tends to use row tables for technical and system tables - explaining the small ratio.

It is worth noting that from HANA 2.0 SP3 upwards, all SQL CREATE TABLE statements create column tables by default unless explicitly stated otherwise (see OSS note 2551355).

That's all folks, I certainly found calculating this ratio interesting!