3236922 - Optimizing HANA expensive memory statement with CS_JOIN as the main operator

Customer is getting Composite OOM for example:  indexserver_<host>.30003.rtedump.20220904-211925.compositelimit_oom.trc

The following example shows an excerpt from  indexserver.composite.oom relevant for this problem:

[MEMORY_LIMIT_VIOLATION]  Information about current memory composite-limit violation: (000-00-00 00:00:00 000 Local)
Composite limit violation (OUT OF MEMORY) occurred.
Composite limit= xx gb 

Executable: hdbindexserver
PID: 
Failed to allocate X GB 

In Query Plan or Explain Plan you will find CS_JOIN, which is used heavily ( more than 2-3 times).

For example:-

QUERY PLAN:
|OPERATOR_NAME|OPERATOR_DETAILS|OPERATOR_PROPERTIES|EXECUTION_ENGINE|DATABASE_NAME|SCHEMA_NAME|TABLE_NAME|TABLE_TYPE|TABLE_SIZE|OUTPUT_SIZE|SUBTREE_SIZE|OPERATOR_ID|PARENT_OPERATOR_ID|LEVEL|POSITION|HOST|PORT|BUFFER_ID|
|COLUMN SEARCH|B.ELN|RECOMPILED, LATE MATERIALIZATION, OLTP SEARCH, RANGE RESTRICTION ('CURRENT'), ENUM_BY: CS_JOIN, PREFERENCE: 1|COLUMN|<SID>||||-1|1.61542|0.00136964|1|0|1|1|HOST|<port>|4|
|    DISTINCT|GROUPING: B.<Table>|ENUM_BY: PREAGGR_BEFORE_JOIN|COLUMN|||||-1|1.61542|0.0013582|2|1|3|1||-1|3|
|      JOIN|JOIN CONDITION: (INNER many-to-one) B.<Table> = A.<Table>|ENUM_BY: PREAGGR_BEFORE_JOIN|COLUMN|||||-1|1.61542|0.00135744|3|2|4|1||-1|3|
|        COLUMN TABLE|FILTER CONDITION: B.MANDT =  :2  AND B.WERKS =  :4 ||COLUMN|||LIPS|COLUMN TABLE|1.21082e+09|6.95703e+07|-1|4|3|5|1|PARTITIONED|-1|3|
|        COLUMN SEARCH|A.<Table>|ENUM_BY: CS_JOIN, PREFERENCE: 1|COLUMN|<SID>||#_SYS_QO_COL_L:153_7ba8bc76e850:402000000004fc68||-1|1.70044|1.09194e-05|5|4|5|1|<host_name>|<Port>|2|
|            DISTINCT|GROUPING: A.<Table>|ENUM_BY: PREAGGR_BEFORE_JOIN|COLUMN|||||-1|1.70044|8.03524e-07|6|5|7|1||-1|1|
|              FILTER|A.MANDT = ?2 AND A.VBTYP_N = ?3 AND (ITAB_IN (C_0, C_1)) A.<table> = t_00.C_0 AND A.POSNV = t_00.C_1||COLUMN|||||-1|1.70044|-1|7|6|8|1||-1|1|
|                COLUMN TABLE|||COLUMN|||<TABLE_NAME>|COLUMN TABLE|8.60509e+09|9.98518e+08|-1|8|7|9|1|PARTITIONED|-1|1|

SAP HANA DATABASE 2.0

OOM, COMPOSITE OOM, CS_JOIN, HANA, Out of Memory, Limit, Composite, Optimization, HINTs , KBA , HAN-DB-PERF , SAP HANA Database Performance , Problem