Hi,
I have the following query:
select SIP.SAFEWORD_ID, SIP.USER_ID, SIP.DT_TM from SAFEWORD_ID_POOL SIP WHERE ( SIP.USER_ID is null ) ORDER BY SIP.SAFEWORD_ID ASC FOR UPDATE OF USER_ID, DT_TM, SAFEWORD_ID NOWAIT

I want to retrieve only one row but in a sorted order.

So, I did the following:
select SAFEWORD_ID, USER_ID, DT_TM from (select /*+ index_ffs(sip) */ SIP.SAFEWORD_ID, SIP.USER_ID, SIP.DT_TM
from SAFEWORD_ID_POOL SIP
WHERE SIP.USER_ID is null
ORDER BY SIP.SAFEWORD_ID ASC)
where rownum < 2
FOR UPDATE OF USER_ID, DT_TM, SAFEWORD_ID NOWAIT

I created an index on safeword_id, user_id and dt_tm columns. There was already a primary key index on safeword_id column.

I put a index_ffs hint on the sub-query but when I ran the explain plan, it gave me an error message:
'cannot select FOR UPDATE from view with DISTINCT< GROUP BY, etc.'

What is this? How do I resolve this? What is the solution?

Please explain as this is extremely URGENT.

Thanks.