Skip to main content

Celonis Product Documentation

O2C_VBAP: inner partition did not fit in memory
Problem

In O2C_VBAP creation script, there are several joins between the main case table, which contains millions of records, and a dimensional/master table containing a small number of records. This situation together with the way Vertica handles it may lead the script to fail due to an out-of-memory issue.

[Vertica][VJDBC](3811) ERROR: Join [("50eb93a5-bfc3-4931-a350-2dede5495f5b_6562390f-28e9-4f23-ad58-1638b876eca2".TCURF_CC x Join Order Restriction 19) using TCURF_CC_super and previous join (PATH ID: 3)] inner partition did not fit in memory; value [220,USD,EUR,M]
Root cause

When performing the left join between a huge table and a small one (e.g. VBAP left join T001L), the Vertica query optimizer may sometimes try to build a HASH table using VBAP columns, which could require a lot of memory, and often leads to an out-of-memory issue. The issue appears more often in a situation when the vast majority (80%+) records of the main table (e.g. VBAP) do not have related record in a smaller table (e.g.T001L).

In O2C_VBAP there are several joins with this kind of tables:

FROM "VBAP" AS "VBAP" 
        [...]
                LEFT JOIN "MAKT" ON 1=1
        [...]
                LEFT JOIN "T001W" ON 1=1
        [...]
                LEFT JOIN "T001L" ON 1=1
        [...]
                LEFT JOIN "TVSTT" ON 1=1
        [...]
                LEFT JOIN "TVKMT" ON 1=1
        [...]
                LEFT JOIN "TVAPT" ON 1=1
        [...]
                LEFT JOIN "TVAGT" ON 1=1
        [...]
                LEFT JOIN "T023T" AS "T023T" on 1=1
        [...]
        LEFT JOIN "T179T" AS "T179T" on 1=1
        [...]
            LEFT JOIN "TVRO" AS "TVRO" ON 1=1
        [...]
Solution

  • Re-Consider the necessity of joining a dimensional table in case it is relevant for only 1-5% of the main case table records. In standard scripts, especially for Data model tables, templates are containing joins to certain dimensional tables that might not be relevant for some customers (e.g T001L –Storage Location).

  • If the table is relevant and required, consider the creation of a temporary or derived table within the main query, which will be pre-sorted and joined on the VBAP primary key and therefore promoted to MERGE join instead of a HASH join.

    • Example: for TVKMT, instead of performing a left join from VBAP to TVKMT on KTGRM (column that is in some cases NULL for more than 99% of VBAP records), either temp table or a derived table can be created which will contain only those records that do exist in VBAP by using an inner join. With Inner join between TVKMT and VBAP, a smaller table (TVKMT) will be chosen to build an in-memory hash table, and more importantly, by assigning the VBAP key columns to that table, means it will be sorted in such a way that joining them with the main query will be much faster and require significantly fewer resources (MERGE JOIN).

Old Query:

FROM "VBAP" AS "VBAP" 
        INNER JOIN "VBAK" ON 1=1
            AND "VBAK"."MANDT" = "VBAP"."MANDT"
                    AND "VBAK"."VBELN" = "VBAP"."VBELN"
                AND "VBAK"."VBTYP" = '<%=orderDocSalesOrders%>'
        LEFT JOIN "VBAP_CURR_TMP" AS "VBAP_CURR_TMP" ON 1=1
            AND "VBAP"."MANDT" = "VBAP_CURR_TMP"."MANDT"
                AND "VBAP"."VBELN" = "VBAP_CURR_TMP"."VBELN"
                AND "VBAP"."POSNR" = "VBAP_CURR_TMP"."POSNR"  
                LEFT JOIN "MAKT" ON 1=1
                AND "VBAP"."MANDT" = "MAKT"."MANDT"
                        AND "VBAP"."MATNR" = "MAKT"."MATNR"
            AND "MAKT"."SPRAS" = '<%=primaryLanguageKey%>'
                LEFT JOIN "T001W" ON 1=1
                        AND "VBAP"."MANDT" = "T001W"."MANDT"
                        AND "VBAP"."WERKS" = "T001W"."WERKS"
                LEFT JOIN "T001L" ON 1=1
                        AND "VBAP"."MANDT" = "T001L"."MANDT"
                        AND "VBAP"."WERKS" = "T001L"."WERKS"
                        AND "VBAP"."LGORT" = "T001L"."LGORT"
                LEFT JOIN "TVSTT" ON 1=1
                        AND "VBAP"."MANDT" = "TVSTT"."MANDT"
                        AND "VBAP"."VSTEL" = "TVSTT"."VSTEL"
            AND "TVSTT"."SPRAS" = '<%=primaryLanguageKey%>'
                LEFT JOIN "TVKMT" ON 1=1
                        AND "TVKMT"."MANDT"="VBAP"."MANDT"
            AND "TVKMT"."SPRAS" = '<%=primaryLanguageKey%>'
                        AND "TVKMT"."KTGRM"="VBAP"."KTGRM"
...

New Query:

-- this will create temp table with only those VBAP records that have TVKMT records
DROP TABLE IF EXISTS "_TMP_VBAP_TVKMT";
CREATE TABLE _TMP VBAP TVKMT
AS
SELECT
  --Add VBAP key columns to pre-sort it and join it with VBAP later in the main query (and promote MERGE join)
    "VBAP"."MANDT"
    ,"VBAP"."VBELN"
    ,"VBAP"."POSNR"
    ,IFNULL("TVKMT"."VTEXT", '') AS "KTGRM_TEXT" --the column we need from TVKMT in the main select
FROM
    "TVKMT"
INNER JOIN "VBAP" ON 1=1
                AND "TVKMT"."MANDT" = "VBAP"."MANDT"
                AND "TVKMT"."SPRAS" = '<%=primaryLanguageKey%>'
                            AND "TVKMT"."KTGRM" = "VBAP"."KTGRM"
;

-- in the main query now just join this way:
"VBAP" ...
       LEFT JOIN _TMP_VBAP_TVKMT AS "TVKMT" ON 1=1
                                AND "VBAP"."MANDT" = "_TMP_VBAP_TVKMT"."MANDT"
                                AND "VBAP"."VBELN" = "_TMP_VBAP_TVKMT"."VBELN"
                                AND "VBAP"."POSNR" = "_TMP_VBAP_TVKMT"."POSNR"

  • Make sure to check the query execution plan (EXPLAIN statement), to detect a potential negative impact on the overall query performance and potential use of the “/*+SYNTACTIC_JOIN*/ “ hint. (See AP_BSEG)