I'm not sure if this is relevant, but in Java Hibernate you can do this with a subselect in your list mapping. The explain plan on Oracle doesn't look horrible for this type of query, but you'd definitely want to do some performance testing to make sure on whatever database you're using.
Mapping:
Code:
<list name="prices" >
<subselect>
SELECT ID,
Price1 as price,
0 AS idx
FROM PRICELISTS
UNION
SELECT ID,
Price2 as price,
1 AS idx
FROM PRICELISTS
*** more ***
UNION
SELECT ID,
Price60 as price,
59 AS idx
FROM PRICELISTS
</subselect>
<key>
<column name="ID"></column>
</key>
<list-index column="idx"></list-index>
<element column="price" type="float"></element>
</list>
Query will look something like:
Code:
select
prices0_.ID as ID22_0_,
prices0_.price as price22_0_,
prices0_.idx as idx3_0_
from
( SELECT ID,
Price1 as price,
0 AS idx
FROM PRICELISTS
UNION
SELECT ID,
Price2 as price,
1 AS idx
FROM PRICELISTS
*** more ***
UNION
SELECT ID,
Price60 as price,
59 AS idx
FROM PRICELISTS
) prices0_
where
prices0_.ID=?
I think because it's using the primary key, the filter gets into the subselect which is why the explain plan isn't horrible. Anyway, hope this is helpful.
Login
FAQ
Search