Temporary segment Problem

[amits]

When i run the below query i am getting the error ORA-01652 ,but when i execute the same query with Count(*) ,i am not getting any error .
Can anyone explain the reason.Either i should get error in both the cases or i should get the results properly.

Case1:
Select * from INT_ACC_MVMNT
where
(INT_ACC_MVMNT.orddealid_d_d_postyp_1,INT_ACC_MVMNT.orddealid_d_d_posnum_2)
in
3 (Select FX_FOREX.posid_d_d_postyp_1,FX_FOREX.posid_d_d_posnum_2
4 from INT_ACC_MVMNT, FX_FOREX
5 where INT_ACC_MVMNT.orddealid_d_d_postyp_1 =
FX_FOREX.posid_d_d_postyp_1
6 and INT_ACC_MVMNT.orddealid_d_d_posnum_2 = FX_FOREX.posid_d_d_posnum_2
7 and FX_FOREX.dealstat = 7
8* and FX_FOREX.dm_lstUpddt <= to_date(19991231,'yyyymmdd'))
SQL..OPS$TEST32 >/
ERROR:
ORA-01652: unable to extend temp segment by 11075 in tablespace TEMP


Case2:

Select count(*) from INT_ACC_MVMNT
2 where (INT_ACC_MVMNT.orddealid_d_d_postyp_1,INT_ACC_MVMNT.orddealid_d_d_posnum_2) in
3 (Select FX_FOREX.posid_d_d_postyp_1,FX_FOREX.posid_d_d_posnum_2
4 from INT_ACC_MVMNT, FX_FOREX
5 where INT_ACC_MVMNT.orddealid_d_d_postyp_1 = FX_FOREX.posid_d_d_postyp_1
6 and INT_ACC_MVMNT.orddealid_d_d_posnum_2 = FX_FOREX.posid_d_d_posnum_2
7 and FX_FOREX.dealstat = 7
8* and FX_FOREX.dm_lstUpddt <= to_date(19991231,'yyyymmdd'))

[ggnanaraj]

When the results are sorted in the temporary segments for count(*) the space is sufficient, so no ORA ERROR.

However, for SELECT * the sort needs more space, so you get the ORA error.

To rectify, increase the size of temporary tablespace or add another datafile to the tablespace.

[Calvin_Qiu]

Compare the execution path of two queries.