FIRST_ROWS hint problem

Hi All,

I have a query as below

Update AlmEvtTbl SET State = 2 where AlmEvtId in (SELECT /*+ FIRST_ROWS */ AlmEvtSysPeView.AlmEvtId FROM AlmEvtSysPeView where (AlmEvtSysPeView.ParentCustId=0 AND
AlmEvtSysPeView.TimeStamp >= TO_DATE('2004-12-06 18:30:00', 'YYYY-MM-DD HH24:MI:SS')
and AlmEvtSysPeView.TimeStamp <= TO_DATE('2004-12-09 18:29:59',
'YYYY-MM-DD HH24:MI:SS') AND

AlmEvtSysPeView.State <> 3)
AND ROWNUM < 2000

If I remove the FIRST_ROWS hint from the SELECT Query I am able to update the 2000 rows returned by the nested SELECT query.

But if the FIRST_ROWS hint is added to the SELECT query 0 rows get updated, though when I run the internal
SELECT query seperately as below I am able to get around 10000 rows.

SELECT /*+ FIRST_ROWS */ AlmEvtSysPeView.AlmEvtId FROM AlmEvtSysPeView where (AlmEvtSysPeView.ParentCustId=0 AND AlmEvtSysPeView.TimeStamp >= TO_DATE('2004-12-06 18:30:00', 'YYYY-MM-DD HH24:MI:SS')
and AlmEvtSysPeView.TimeStamp <= TO_DATE('2004-12-09 18:29:59',
'YYYY-MM-DD HH24:MI:SS') AND

AlmEvtSysPeView.State <> 3

It will be highly grateful to know the reason for this. Thanks,
Tuhin Received on Wed Dec 15 2004 - 01:36:38 CST