Live Performance Issue For One SQL Query

From Couple of Days Back we are facing live performance issue in our Hibernate SQL Query.

TABLE1 RECORDS IN DATABASE

ID	TRX_ID	QUE_ID	ITEM_TYPE
108109	103327	100843	T
108384	103327	100843	SI

Problem With Original Correlated Query:

select * from TABLE1 where this_.QUE_ID=100843 and this_.ITEM_TYPE='T' and trx_id
not in (select trx_id from TABLE1 where item_type='SI' AND que_id=100843) order by this_.ID asc;

Well ideally this query should not return no records as per data shown on top as we are trying to eliminate item_type of 'T' where we do already have a row with item_type of 'SI' for the same value of trx_id and que_id.

And yes,it is working but unfortunately failing when having > 1000 records.I am guessing it is probably because of the corelated subquery where it is ideally checking for each record returned in the outer query.

Now Query we have enhanced infact it is our collegues Gajananad Deshpande and Matt who helped us in this query.

Enhanced Query

select id from TABLE1 tab1, (select this_.trx_id,this_.que_id
from TABLE1 where this_.QUE_ID=100843 and this_.ITEM_TYPE='T'
minus
select trx_id, que_id from TABLE1 where item_type='SI' AND que_id=100843) temp_tab
where tab1.trx_id = temp_tab.trx_id
and tab1.que_id = temp_tab.que_id
and tab1.item_type = 'T';

Ohh!! Little clever way where we are trying to eliminate item_type of 'T' where we do already have a row with item_type of 'SI' for the same value of trx_id and que_id.

I think instead of using Correlated sub queries suggest using SQL SET operators if possible.

Hope this helps for any.

Any comments welcome!!!!!