HANA on-premise - Memory error with SELECT COUNT(*) on Calculation View

[lferrario]

On Hana2 sp7 we have some large calculation wiews on which it is impossible to execute the count of record like: select count(*) from ‘view name’ because the select ends in memory error with the message (over 300gb of memory consumed which is the current limit of available memory in hana):

Cannot allocate enough memory: search table error: cannot allocate enough memory: [9] Memory allocation failed;exception 1000002: Allocation failed ; $failure_type$=STATEMENT_MEMORY_LIMIT_FROM_GLOBAL_CONFIG

The same error obviously also occurs with the data preview.

The number of records in one of these views is 270 million so not excessive and the model is built on a base table (not partitioned) with these characteristics:

The strange think is that if the select is more complex, for example inserting a group by year like: select count(*),year from ‘view name’ group by year, it works correctly and consumes few memory (30 gb). The same if we add a where to the select(*) on the id field (which is the 'key' field of the view and is never 'null') like: select count(*) from ‘view_name’ where id is not null.

The view's data model has already been optimized and there is nothing more that can be done about it, we also tried to apply different HINTs to the calc. view but without success.

Do you have any ideas on possible causes and any suggestions?

Thanks

Regards

Luca

[jan_zwickel]

Difficult to answer generically, why adding a where filter changes the resource consumption so much. It might be caused by different optimization strategies. Could you share the explain plan for both queries: with and without the where clause?

Does your model involve remote objects?

Would it help to use the count only on the key "id", i.e., count("id")?

[lferrario]

Hello,

no remote objects are involved (only Hana tables and views), the count(ID) on the entire view lasts less than a second and consumes less than 1GB of memory, while the count(*) as mentioned ends in memory error. As soon as possible I'll try to share the explain plan for the two statements even if I fear that the one on select(*) will be incomplete due to the memory problem.

Thanks

Luca