ODBC: ERROR 3815: Join inner did not fit in memory

1. Run the statistics if not run, the inner table should be a dimension table. if no statistics are run, vertica may not determine which table should be inner.

2. Try for a mergejoin ( see the projection design and the join column)


3. If it is hash join,

A hash join to an inner relation could require additional memory than was initially allocated to the query. If memory is available, the hash join executes the query without needing to spill to disk.  If memory is not available, the query automatically re-executes with join spill enabled (ENABLE_JOIN_SPILL), but query performance will be slower.

Two ways you can do this:

a. At the session level, Select add_vertica_options('EE','ENABLE_JOIN_SPILL');

b. At the statement level, select /*+ set_vertica_options(EE,ENABLE_JOIN_SPILL) */....


There are several ways of enabling hash join spill in Vertica:

c.      Statement level:   Select /*+ set_vertica_options('EE','ENABLE_JOIN_SPILL') */….

d.      Session level:  Select set_vertica_options('EE','ENABLE_JOIN_SPILL');

e.      If using 7.1, then at the database level:  select set_config_parameter('EEVerticaOptions','ENABLE_JOIN_SPILL');

You could not answer my question. As I said with the same user it is completed in vsql client without a problem. When I use odbc, or python code I am getting that error. How vsql completes the query?

I set the above parameters but it did not change anything. I am getting the same error again.

Hi Ozi!

Define trace file and DEBUG mode in vertica.ini file, send query with ODBC driver and post a DEBUG info from log.

We have to see an ODBC query to understand a difference between vsql and ODBC. And also post your odbc.ini config (hide sensitive info like UID/PWD).


Example

vertica.ini

[Driver]
DriverManagerEncoding=UTF-16
ODBCInstLib=/usr/lib/x86_64-linux-gnu/libodbcinst.so
ErrorMessagesPath=/opt/vertica/lib64
LogLevel=4
LogPath=/tmp

Execute with pyODBC

In [3]: sql = sql.strip()

In [4]: print sql
SELECT 
    t1.a,
    t2.b, 
    t2.c
FROM
    {IJ tbl AS t1 INNER JOIN tbl AS t2 ON t1.a = t2.a };

In [5]: cxn.execute(sql)
Out[7]: <pyodbc.Cursor at 0x7f9be8016630>

Trace file:

Jan 13 15:03:37 INFO  3954849600 StatementState::InternalPrepare: Preparing query: SELECT 
    t1.a,
    t2.b, 
    t2.c
FROM
    {IJ tbl AS t1 INNER JOIN tbl AS t2 ON t1.a = t2.a };
Jan 13 15:03:37 INFO  3954849600 VDataEngine::Prepare: Original Query: SELECT 
    t1.a,
    t2.b, 
    t2.c
FROM
    {IJ tbl AS t1 INNER JOIN tbl AS t2 ON t1.a = t2.a };
Jan 13 15:03:37 INFO  3954849600 VDataEngine::Prepare: Query is issued as ExecDirect
Jan 13 15:03:37 INFO  3954849600 VDataEngine::Prepare: Query: SELECT t1.a, t2.b, t2.c FROM   tbl AS t1 INNER JOIN tbl AS t2 ON t1.a = t2.a   ;
Jan 13 15:03:37 INFO  3954849600 VDataEngine::Prepare: Initializing prepared statement: _PLAN0x228fd90_0
Jan 13 15:03:37 INFO  3954849600 VPQResultSet::SetupColumns: column type is SQL_WVARCHAR
Jan 13 15:03:37 INFO  3954849600 VPQResultSet::SetupColumns: column type is SQL_BIGINT
Jan 13 15:03:37 INFO  3954849600 VPQResultSet::SetupColumns: column type is SQL_TYPE_TIMESTAMP

Cheers.