# Oracle: HT\_ tables are created for inheritance type table-per-class

**URL:** https://discourse.hibernate.org/t/oracle-ht-tables-are-created-for-inheritance-type-table-per-class/1388
**Category:** Hibernate ORM
**Created:** [September 12, 2018, 7:26am UTC](https://discourse.hibernate.org/t/oracle-ht-tables-are-created-for-inheritance-type-table-per-class/1388 "2018-09-12T07:26:11Z")
**Posts on this page:** 10
**Page:** 1

<div class="post-metadata">

### Author: ![OLibutzki](https://avatars.discourse-cdn.com/v4/letter/o/48db29/32.png) [@OLibutzki](https://discourse.hibernate.org/u/OLibutzki)
#### Post date: [September 12, 2018, 7:26am UTC](https://discourse.hibernate.org/t/oracle-ht-tables-are-created-for-inheritance-type-table-per-class/1388/1 "2018-09-12T07:26:11Z")

</div>

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

---

<div class="post-metadata">

### Author: ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)
#### Post date: [September 12, 2018, 8:29am UTC](https://discourse.hibernate.org/t/oracle-ht-tables-are-created-for-inheritance-type-table-per-class/1388/2 "2018-09-12T08:29:16Z")

</div>

Send us a [replicating test case](http://in.relation.to/2018/06/04/best-way-write-hibernate-orm-issue-test-case/) so we can investigate it.

---

<div class="post-metadata">

### Author: ![OLibutzki](https://avatars.discourse-cdn.com/v4/letter/o/48db29/32.png) [@OLibutzki](https://discourse.hibernate.org/u/OLibutzki)
#### Post date: [September 12, 2018, 8:35am UTC](https://discourse.hibernate.org/t/oracle-ht-tables-are-created-for-inheritance-type-table-per-class/1388/3 "2018-09-12T08:35:58Z")

</div>

> [@vlad](#):
>
> Send us a [replicating test case](http://in.relation.to/2018/06/04/best-way-write-hibernate-orm-issue-test-case/) so we can investigate it.

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.

---

<div class="post-metadata">

### Author: ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)
#### Post date: [September 12, 2018, 10:06am UTC](https://discourse.hibernate.org/t/oracle-ht-tables-are-created-for-inheritance-type-table-per-class/1388/4 "2018-09-12T10:06:10Z")

</div>

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

---

<div class="post-metadata">

### Author: ![OLibutzki](https://avatars.discourse-cdn.com/v4/letter/o/48db29/32.png) [@OLibutzki](https://discourse.hibernate.org/u/OLibutzki)
#### Post date: [September 12, 2018, 11:27am UTC](https://discourse.hibernate.org/t/oracle-ht-tables-are-created-for-inheritance-type-table-per-class/1388/5 "2018-09-12T11:27:30Z")

</div>

Here you can find the test case: [https://github.com/OLibutzki/hibernate-test-case-templates/tree/oracle-ht-tables](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():

```auto
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.

---

<div class="post-metadata">

### Author: ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)
#### Post date: [September 12, 2018, 11:42am UTC](https://discourse.hibernate.org/t/oracle-ht-tables-are-created-for-inheritance-type-table-per-class/1388/6 "2018-09-12T11:42:02Z")

</div>

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.

---

<div class="post-metadata">

### Author: ![OLibutzki](https://avatars.discourse-cdn.com/v4/letter/o/48db29/32.png) [@OLibutzki](https://discourse.hibernate.org/u/OLibutzki)
#### Post date: [September 12, 2018, 12:09pm UTC](https://discourse.hibernate.org/t/oracle-ht-tables-are-created-for-inheritance-type-table-per-class/1388/7 "2018-09-12T12:09:11Z")

</div>

> [@vlad](#):
>
> I think they are needed for bulk update statements. Try to run a `delete from BaseEntity` and you’ll see them in action.

Yeah, you are right. I switched to the entities in your [blog post](http://in.relation.to/2017/02/01/non-temporary-table-bulk-id-strategies/) and just changed the inheritence type to table-per-class. This is the query I execute:

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

```

The SQL statements:

```auto
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:

```auto
    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:

```auto
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:

```auto
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

```

---

<div class="post-metadata">

### Author: ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)
#### Post date: [September 12, 2018, 12:30pm UTC](https://discourse.hibernate.org/t/oracle-ht-tables-are-created-for-inheritance-type-table-per-class/1388/8 "2018-09-12T12:30:05Z")

</div>

> 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.

---

<div class="post-metadata">

### Author: ![OLibutzki](https://avatars.discourse-cdn.com/v4/letter/o/48db29/32.png) [@OLibutzki](https://discourse.hibernate.org/u/OLibutzki)
#### Post date: [September 12, 2018, 12:35pm UTC](https://discourse.hibernate.org/t/oracle-ht-tables-are-created-for-inheritance-type-table-per-class/1388/9 "2018-09-12T12:35:52Z")

</div>

> [@vlad](#):
>
> 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.

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.

---

<div class="post-metadata">

### Author: ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)
#### Post date: [September 12, 2018, 12:37pm UTC](https://discourse.hibernate.org/t/oracle-ht-tables-are-created-for-inheritance-type-table-per-class/1388/10 "2018-09-12T12:37:50Z")

</div>

> [@OLibutzki](#):
>
> 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.

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.