-
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.
-
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.
-
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
-
Also, if you are doing where rownum < 2, use rownum =1 instead (this is the one exception where you can use "=" with rownum).
-
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.
-
safeword_id is a varchar2 column. So, I cannot give ' where safeword_id > 0.
Please help. It's urgent!!
Thanks.
-
Hi;
Code:
select min(col_name ) from table;
and you have the smallest val;
-- i hope this min-val does not change ;-)
Orca
-
So, how do I modify the query? What is the solution?
Thanks.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|