Hibernate Multi-table Bulk Operations always try to create the temporary table

Question

I have some entities using join-inheritance and I'm doing bulk operations on them. As explained in Multi-table Bulk Operations Hibernate uses a temporary table to execute the bulk operations.

As I understand temporary tables the data in them is temporary (deleted at end of transaction or session) but the table themselves are permanent. What I see is that Hibernate tries to create the temporary table every time such a query is executed. Which in my case is up more than 35.000 times per hour. The create table statement obviously fails every time, because a table with that name already exists. This is really unnecessary and probably hurts the performance, also the DBAs are not happy...

Is there a way that Hibernate remembers that it already created the temporary table?

If not, are there any workarounds? My only idea is to use single-table-inheritance instead to avoid using temporary tables completely.

Hibernate version is 4.2.8, DB is Oracle 11g.

Answer 1

I think this is a bug in TemporaryTableBulkIdStrategy, because when using the Oracle8iDialect says that temporary tables shouldn't be deleted:

@Override
public boolean dropTemporaryTableAfterUse() {
    return false;
}

But this check is made only when deleting the table:

protected void releaseTempTable(Queryable persister, SessionImplementor session) {
    if ( session.getFactory().getDialect().dropTemporaryTableAfterUse() ) {
        TemporaryTableDropWork work = new TemporaryTableDropWork( persister, session );
        if ( shouldIsolateTemporaryTableDDL( session ) ) {
            session.getTransactionCoordinator()
                    .getTransaction()
                    .createIsolationDelegate()
                    .delegateWork( work, shouldTransactIsolatedTemporaryTableDDL( session ) );
        }
        else {
            final Connection connection = session.getTransactionCoordinator()
                    .getJdbcCoordinator()
                    .getLogicalConnection()
                    .getConnection();
            work.execute( connection );
            session.getTransactionCoordinator()
                    .getJdbcCoordinator()
                    .afterStatementExecution();
        }
    }
    else {
        // at the very least cleanup the data :)
        PreparedStatement ps = null;
        try {
            final String sql = "delete from " + persister.getTemporaryIdTableName();
            ps = session.getTransactionCoordinator().getJdbcCoordinator().getStatementPreparer().prepareStatement( sql, false );
            session.getTransactionCoordinator().getJdbcCoordinator().getResultSetReturn().executeUpdate( ps );
        }
        catch( Throwable t ) {
            log.unableToCleanupTemporaryIdTable(t);
        }
        finally {
            if ( ps != null ) {
                try {
                    session.getTransactionCoordinator().getJdbcCoordinator().release( ps );
                }
                catch( Throwable ignore ) {
                    // ignore
                }
            }
        }
    }
}

but now when creating the table:

protected void createTempTable(Queryable persister, SessionImplementor session) {
    // Don't really know all the codes required to adequately decipher returned jdbc exceptions here.
    // simply allow the failure to be eaten and the subsequent insert-selects/deletes should fail
    TemporaryTableCreationWork work = new TemporaryTableCreationWork( persister );
    if ( shouldIsolateTemporaryTableDDL( session ) ) {
        session.getTransactionCoordinator()
                .getTransaction()
                .createIsolationDelegate()
                .delegateWork( work, shouldTransactIsolatedTemporaryTableDDL( session ) );
    }
    else {
        final Connection connection = session.getTransactionCoordinator()
                .getJdbcCoordinator()
                .getLogicalConnection()
                .getConnection();
        work.execute( connection );
        session.getTransactionCoordinator()
                .getJdbcCoordinator()
                .afterStatementExecution();
    }
}

As a workaround, you could extend the Oracle dialect and override the dropTemporaryTableAfterUse method to return false.

I filled the HHH-9744 issue for this.