AP_BSEG: inner partition did not fit in memory
Problem
The following error might occur when executing the create AP_BSEG transformation, or alternatively on loading the data model (even if they have worked before):
inner partition did not fit in memory; value [510,USD]
The values in the square brackets are only an example.
Root cause
The problem is that Vertica loads the bigger table (in this case BSEG) as the inner side of the join into memory (and in this case the table is bigger than the available memory), and puts the smaller tables (e.g. TCURR_CC) on the outer side.
Solution
To solve this, simply add /*+SYNTACTIC_JOIN*/ after the SELECT to your query:
DROP VIEW IF EXISTS "AP_BSEG";
CREATE VIEW "AP_BSEG" AS(
SELECT /*+SYNTACTIC_JOIN*/
BSEG.*
...or
DROP VIEW IF EXISTS "AP_BSEG";
CREATE VIEW "AP_BSEG" AS(
SELECT /*+SYNTACTIC_JOIN*/ DISTINCT
BSEG.*
...Note
This solution is specific to the standard SAP ECC Accounts Payable AP_BSEG transformation. The error "inner partition did not fit in memory" can occur in any other script, and adding /*+SYNTACTIC_JOIN*/ might not solve the problem.