rownum with order by and for update of

[lee]

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.

[stecal]

That would be error ORA-02014 - which says you can't do what it said you were trying to do. Your SIP table is a view? Perhaps a view based on two or more tables? You have to perform the update directly against the table(s) in question.

Modifying a Join View

A modifiable join view is a view that contains more than one table in the top level FROM clause of the SELECT statement, and that does not contain any of the following:

- DISTINCT operator
- aggregate functions: AVG, COUNT, GLB, MAX, MIN, STDDEV, SUM, or VARIANCE
- set operations: UNION, UNION ALL, INTERSECT, MINUS
- GROUP BY or HAVING clauses
- START WITH or CONNECT BY clauses
- ROWNUM pseudocolumn

With some restrictions, you can modify views that involve joins. If a view is a join on other nested views, then the other nested views must be mergeable into the top level view.

[Orca777]

Hi;

* Skip the order by, because rownum is number of the row acessed before SORTING

* Try to use the index with where-clause so the data should be selected automatically 'sorted' ( indexed access is sorted access );


select
SAFEWORD_ID,
USER_ID
from SAFEWORD_ID_POOL SIP
WHERE SIP.USER_ID is null
and SIP.SAFEWORD_ID > 0
and rownum < 2
FOR UPDATE OF USER_ID, DT_TM, SAFEWORD_ID NOWAIT

Orca

[stecal]

Also, if you are doing where rownum < 2, use rownum =1 instead (this is the one exception where you can use "=" with rownum).

[lee]

Thanks a lot for the replies. So, is the condition 'safeword_id > 0' necessary? And if I do not put that, then what will the sql be? Please explain.

I am so confused!!!


Thanks.

[lee]

safeword_id is a varchar2 column. So, I cannot give ' where safeword_id > 0.

Please help. It's urgent!!

Thanks.

[Orca777]

Hi;

select min(col_name ) from table;

and you have the smallest val;
-- i hope this min-val does not change ;-)
Orca

[lee]

So, how do I modify the query? What is the solution?

Thanks.