Can I use hibernate Criteria objects to build a query with sub-selects as table alias, see example below, aliases b & c?
The current Criteria implementation uses a massive IN clause which is slow. The DBA's have suggested this optimisation, but as far as I can see it cannot be done with hibernate Criteria objects..
select * FROM VW_ACCOUNTS acc, (select distinct account_id, legal_entity_id from VW_AUTH where User_ID='user01' and party_id ='12345' ) b, (select distinct VALUE from VW_AUTH2 where party_id='12345' and SRVC_ID='345' ) c WHERE acc.status='LIVE' AND acc.client_id =12345 AND (acc.account_id = b.account_id OR acc.secondary_id = b.secondary_id) AND acc.account_id = c.VALUE ORDER BY lower(acc.account_id) ASC;
|