Hibernate Multi-Table Bulk Operations always tries to create a temporary table

I have several objects using join-inheritance and I am doing bulk operations on them. As explained in Multiple Bulk Operations, Hibernate uses a temporary table to perform bulk operations.

As I understand temporary tables, the data in them is temporary (deleted at the end of a transaction or session), but the table itself is permanent. I see that Hibernate tries to create a temporary table every time a query like this is executed. Which in my case is over 35,000 times an hour. The create table statement clearly fails every time because a table with that name already exists. This is really unnecessary and probably degrades performance, also the DBAs are not happy ...

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

If not, are there any workarounds? My only idea is to use unidirectional inheritance instead to avoid using temporary tables entirely.

Hibernate version is 4.2.8, DB is Oracle 11g.

+3


source to share


2 answers


I think this is a bug in TemporaryTableBulkIdStrategy because when using Oracle8iDialect says that temporary tables should not be dropped:

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

      

But this check is only done when the table is dropped:

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 can extend the Oracle dialect and override the method dropTemporaryTableAfterUse

to return false

.

I have completed HHH-9744 for this.

+2


source


When Vlad pointed me in the right direction, I came up with the following workaround for caching the names of already created temporary tables:

public class FixedTemporaryTableBulkIdStrategy extends TemporaryTableBulkIdStrategy {

    private final Set<String> tables = new CopyOnWriteArraySet<>();

    @Override
    protected void createTempTable(Queryable persister, SessionImplementor session) {
        final String temporaryIdTableName = persister.getTemporaryIdTableName();
        if (!tables.contains(temporaryIdTableName)) {
            super.createTempTable(persister, session);
            tables.add(temporaryIdTableName);
        }
    }
}

      



This can be used by setting the property hibernate.hql.bulk_id_strategy

to the fully qualified name of this class.

Please not that this is not a general solution and only works if the database / dialect uses global temporary tables (as opposed to session / transaction specific ones).

+1


source







All Articles