I found the underlying cause of the issue I posted earlier and this has now turned into a design question.

Query 1 against bss_daily gets statistics for column A. It returns resultset A which are a list of objects of type BSSDailyStat.

Query 2 against bss_daily gets statistics for column B. It returns resultset B which are a list of objects of type BSSDailyStat.

I wish to insert both statistics result sets into bss_daily_stat. So BSSDailyStat serves as a domain object to hold the data returned from the queries.

In a few cases, queries 1 and 2 return the same row from bss_daily because column A and B both met statistical criteria. But since Hibernate only creates one instance of a unique object, the approach I'm taking will not work. So, I end up seeing part of resultset A mixed into resultset B because Hibernate has already retrieved that unique object id.

Should I just go back to using scalars for the resultset since this approach is violating Hibernate's design? It is convenient and more maintainable to have data from a query all bundled into an object, but I would need 2 instances of the same object in this case.

I have about 6 of such queries and need to commit them as a unit of work all together. Therefore, calling session.clear() in between queries is not an option.

This was my original post:
Using core Hibernate 3.3 with Oracle 10g. Native named SQL Queries are external. Hibernate will not completely clear out the previous resultset when the second query is done. The second List below contains 2 leftover objects from List 1, and 8 objects which are correct. The issue is consistant, if I flip the queries around behavior is consistant.

Hibernate 3.3

<sql-query name="BssDailyStat.findByTotalTxMegabytesTopN">
<return entity-name="BssDailyStatLoader" alias="bss"/>
<![CDATA[select {bss.*} from (select bss_daily_id, total_originated_calls, recorded_on_dt, 'total_tx_megabytes' as column_name, sector_name, sector_nbr, dense_rank() over (order by total_tx_megabytes desc) as rank_in_collection, COUNT(*) OVER ( ) as collection_size from (select * from bss_daily where recorded_on_dt = ? order by total_tx_megabytes desc) where rownum <= ? order by total_tx_megabytes desc ) bss]]>
</sql-query>

<sql-query name="BssDailyStat.findByRfAccessFailurePctTopN">
<return entity-name="BssDailyStatLoader" alias="bss"/>
<![CDATA[select {bss.*} from (select bss_daily_id, total_originated_calls, recorded_on_dt, 'orig_call_rf_access_fail_pct' as column_name, sector_name, sector_nbr, dense_rank() over (order by orig_call_rf_access_fail_pct desc, total_originated_calls desc) as rank_in_collection, COUNT(*) OVER ( ) as collection_size from (select * from bss_daily where recorded_on_dt = ? and total_originated_calls >= ? order by orig_call_rf_access_fail_pct desc, total_originated_calls desc) where rownum <= ? order by orig_call_rf_access_fail_pct desc, total_originated_calls desc ) bss]]>
</sql-query>

Must use an entity-name because BssDailyStat maps to two different tables

<class name="com.service.persist.domain.BssDailyStat" table="BSS_DAILY" entity-name="BssDailyStatLoader" >
<id name="id" column="BSS_DAILY_ID">
<generator class="sequence">
<param name="sequence">bss_daily_seq</param>
</generator>
</id>
<property name="sectorName">
<column name="SECTOR_NAME"/>
</property>
<property name="sectorNbr">
<column name="SECTOR_NBR"/>
</property>
<property name="columnName">
<column name="COLUMN_NAME"/>
</property>
<property name="recordedOnDt">
<column name="RECORDED_ON_DT"/>
</property>
<property name="collectionSize">
<column name="COLLECTION_SIZE"/>
</property>
<property name="rankInCollection">
<column name="RANK_IN_COLLECTION"/>
</property>
<property name="totalOriginatedCalls">
<column name="TOTAL_ORIGINATED_CALLS"/>
</property>
</class>

SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();

List<BssDailyStat> bssDailyStatsList = bssCrudService.findByRfAccessFailurePctTopN(date);
session.flush();
sessionFactory.evictQueries();


//List 2 contains 2 objects left over from List 1!
List<BssDailyStat> bssDailyStatsList2 = bssCrudService.findByTotalTxMegabytesTopN(date);

[bOracle 10g[/b]

I did end up revising my design back to use Scalars. Hibernate is a great, simple solution for some things I'm doing. Basically taking raw data from csv files and loading it into a database. But for fancy native SQL queries using Oracle Analytics, I'm thinking Ibatis would be a better fit. My conclusion is based on the issue I had previously posted. I appreciate your comments and really want to learn if there are good approaches using Hibernate that I'm not aware of.