Oracle: HT_ tables are created for inheritance type table-per-class


#1

Hi everyone,

we use a table-per-class inheritence mapping. After upgrading from Hibernate 4.3.8 to 5.3.3 for all the entities which use inheritence a HT_ table is created. We use Postgres and Oracle and this only occurs with Oracle databases. With Postgres no tables are created which is the expected behaviour as they are meaningless, if you don’t use joined-sublasses.

Can anyone help? Is this a regression in Hibernate 5.3.3?

Kind regards
Oliver


#2

Send us a replicating test case so we can investigate it.


#3

Ok, I will try to. First of all I wanted to ensure that my assumption is correct that the “HT_” tables shouldn’t be created with table-per-class inheritence mapping.


#4

It might be that they are needed for bulk update if the DB does not support temporary tables.


#5

Here you can find the test case: https://github.com/OLibutzki/hibernate-test-case-templates/tree/oracle-ht-tables

Notice that I did not know how to check if a certain table exists, but you can find these entries in the log if you execute org.hibernate.bugs.JPAUnitTestCase.hhh123Test():

Hibernate: 
    create global temporary table HT_MyProduct (productId number(19,0) not null) 
        on commit delete rows
Hibernate: 
    create global temporary table HT_Pen (productId number(19,0) not null) 
        on commit delete rows
Hibernate: 
    create global temporary table HT_Book (productId number(19,0) not null) 
        on commit delete rows

With H2 these tables are not created.

With Oracle and inheritence type “single-class” the ta_ tables are not created, too.

Edit: By setting hibernate.hql.bulk_id_strategy to org.hibernate.hql.spi.id.inline.InlineIdsSubSelectValueListBulkIdStrategy the creation of the HT_ tables is omitted. Anyway, I guess they should not be created without this property.


#6

I think they are needed for bulk update statements. Try to run a delete from BaseEntity and you’ll see them in action.

These are temporary tables, not regular ones and are needed. The difference between Oracle and H2 is given by the getDefaultMultiTableBulkIdStrategy method. If you take a look on the associated Dialect classes, you will see the difference.


#7

Yeah, you are right. I switched to the entities in your blog post and just changed the inheritence type to table-per-class. This is the query I execute:

entityManager.createQuery("delete from Person where employed = :employed" )
  .setParameter( "employed", false )
  .executeUpdate();

The SQL statements:

Hibernate: 
    insert 
    into
        HT_Person
        select
            person0_.id as id,
            person0_.companyName as companyName 
        from
            ( select
                id,
                companyName,
                employed,
                name,
                null as fellow,
                0 as clazz_ 
            from
                Person 
            union
            all select
                id,
                companyName,
                employed,
                name,
                null as fellow,
                1 as clazz_ 
            from
                Doctor 
            union
            all select
                id,
                companyName,
                employed,
                name,
                fellow,
                2 as clazz_ 
            from
                Engineer 
        ) person0_ 
    where
        person0_.employed=?
Hibernate: 
    delete 
    from
        Person 
    where
        (
            id, companyName
        ) IN (
            select
                id,
                companyName 
            from
                HT_Person
        )
Hibernate: 
    delete 
    from
        Person 
    where
        (
            id, companyName
        ) IN (
            select
                id,
                companyName 
            from
                HT_Person
        )
Hibernate: 
    delete 
    from
        Doctor 
    where
        (
            id, companyName
        ) IN (
            select
                id,
                companyName 
            from
                HT_Person
        )
Hibernate: 
    delete 
    from
        Engineer 
    where
        (
            id, companyName
        ) IN (
            select
                id,
                companyName 
            from
                HT_Person
        )
Hibernate: 
    delete 
    from
        HT_Person

Ok, HT_Person is used, but is this meaningful?

First of all, the delete statement for the Person table seems to be executed twice.
Second, which benefit does the HT_ table provide, why not just executing this:

    delete 
    from
        Person person0_ 
    where
        person0_.employed=?

    delete 
    from
        Doctor doctor0_ 
    where
        doctor0_ .employed=?

    delete 
    from
        Engineer engineer0_
    where
        engineer0_.employed=?

Btw. with H2 an error occurs:

Hibernate: 
    create cached local temporary table if not exists HT_Person (id integer not null, companyName varchar(255) not null) 
        on commit drop transactional
Hibernate: 
    insert 
    into
        HT_Person
        select
            person0_.id as id,
            person0_.companyName as companyName 
        from
            ( select
                id,
                companyName,
                employed,
                name,
                null as fellow,
                0 as clazz_ 
            from
                Person 
            union
            all select
                id,
                companyName,
                employed,
                name,
                null as fellow,
                1 as clazz_ 
            from
                Doctor 
            union
            all select
                id,
                companyName,
                employed,
                name,
                fellow,
                2 as clazz_ 
            from
                Engineer 
        ) person0_ 
    where
        person0_.employed=?
Hibernate: 
    delete 
    from
        Person 
    where
        (
            id, companyName
        ) IN (
            select
                id,
                companyName 
            from
                HT_Person
        )
2018-09-12 13:53:58 WARN  SqlExceptionHelper:137 - SQL Error: 90052, SQLState: 90052
2018-09-12 13:53:58 ERROR SqlExceptionHelper:142 - Unterabfrage gibt mehr als eine Feld zurück
Subquery is not a single column query; SQL statement:
delete from Person where (id, companyName) IN (select id, companyName from HT_Person) [90052-176]

I just tested with Hibernate 4.3.6:
The log is all the same with the different that these entries are missing:

Hibernate: 
    create global temporary table HT_Doctor (id number(10,0) not null, companyName varchar2(255 char) not null) 
        on commit delete rows
Hibernate: 
    create global temporary table HT_Person (id number(10,0) not null, companyName varchar2(255 char) not null) 
        on commit delete rows
Hibernate: 
    create global temporary table HT_Engineer (id number(10,0) not null, companyName varchar2(255 char) not null) 
        on commit delete rows

#8

First of all, the delete statement for the Person table seems to be executed twice.

That sounds like a bug. You can recode your Pull Request to prove that and open a Jira issue.

Second, which benefit does the HT_ table provide, why not just executing this:

Because this is also used for JOINED inheritance where if you delete by a baseclass column, you won;t be ale to run that condition against the subclass tables. Hence, the same strategy is used for TALE_PER_CLASS, which, in terms of performance, is the worst inheritance strategy to use anyway.

Btw. with H2 an error occurs:

Please open a Jira issue for that too with a Pull Request that proves it.

Thanks.


#9

I see, but there is room for improvement, isn’t it? Using the same strategy for two different inheritence types is not a good choice from the performance’s point of view.

So it seems to be meaningful to have three Jirs issues: The two bugs you mentioned and the enchancement request for table-per-class bulk operations.


#10

Sure, but that not a priority for the moment since TABLE_PER_CLASS is already a poor choice when it comes to performance. Optimizing just this use case will not make it a viable option. If you want to implement it, feel free to supply a Pull Request with a fix.