hi all,

in our project,
we are facing a problem with union subclass strategy,

the query formed by hibernate when we call order.getOrderSkuses is:-

(select charge_amount, attendee_type, price_reason, id, tax_amount, updated_by, created_at,
create_transact_id, quantity, delivery_amount, orders_id, created_by, sku_prices_id, null as cancelled_on,
null as status, line_number, update_transact_id, discount_amount, catalog_skus_id,
order_skus_id, is_deleted, duration, updated_at, null as cancelled_by, service_amount, 0 as clazz_
from order_skus
union
select charge_amount, attendee_type, price_reason, id, tax_amount, updated_by, created_at,
create_transact_id, quantity, delivery_amount, orders_id, created_by, sku_prices_id,
cancelled_on, status, line_number, update_transact_id, discount_amount, catalog_skus_id,
order_skus_id, is_deleted, duration, updated_at, cancelled_by,
service_amount, 1 as clazz_ from waitlist_order_skus )
orderskuse0_ where orderskuse0_.status is null and orderskuse0_.orders_id='abc'

The two mapping files are(not posting the whole file only the relevant maping) :-

1) Order.hbm.xml
<set name="orderSkuses" lazy="true" inverse="true"
cascade="all" where="status is null">
<key>
<column name="orders_id" />
</key>
<one-to-many class="OrderSkus" />

</set>

2)orderSkus.hbm.xml
<class
name="OrderSkus"
table="order_skus"
>

<id
name="id"
type="java.lang.String"
column="id"
>
<generator class="db.IdGenerator" />
</id>


<union-subclass name="WaitlistedOrderSkus"
table="waitlist_order_skus" >
<property
name="status"
type="java.lang.String"
column="status"
not-null="true"
length="5"
/>

</union-subclass>
</class>

Now the problem is that the above query is not using index created at order_id
as it first fetches all row from both the tables then put the where clause,
Which is giving performance issue

Is there any solution ?

Thanks