0

I have table test1 (TXID primary key, STATE, NEXTRUN, TARGET) and want to get a row with minimum NEXTRUN for state (passed as input).

Table Data:
-----------
TXID    STATE   NEXTRUN TARGET
2   KA  2   ANY
1   TN  1   ANY
3   KA  2   ANY
4   TN  3   A
5   KA  1   ANY

Query 1 with MIN():

SELECT *
  FROM test1
 WHERE NEXTRUN = (SELECT MIN(NEXTRUN)
                    FROM test1
                   WHERE TARGET = 'ANY'
                     AND STATE = 'KA')
   AND TARGET = 'ANY'
   AND STATE = 'KA'
   FOR UPDATE

The explain plan shows ( 2 TABLE ACCESS FULL)

Query 2 with ORDER BY and ROWNUM:

SELECT *
  FROM TEST1
 WHERE TXID = (SELECT TXID
                 FROM (SELECT *
                         FROM TEST1
                        WHERE STATE = 'KA'
                          AND TARGET = 'ANY'
                        ORDER BY NEXTRUN ASC)
                WHERE ROWNUM = 1)
   FOR UPDATE

The explain plan:

(1 TABLE ACCESS FULL , 1 TABLE ACCESS BY INDEX ROWID

Which one is better query in terms of deadlock prevention and performance? The query is called from multiple threads (connections). Sometimes I see deadlock happening in the first case (where 2 TABLE ACCESS FULL) is happening. Hence I'm trying with the query 2.

Yaroslav Shabalin
  • 1,645
  • 3
  • 17
  • 29
  • It's seems that your question fails into [XY Problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) category. May be it is reasonable to rethink it in terms of real problem you have? Because IMHO root of your problem is not in the queries itself, but rather in table design, indexes, etc. – Yaroslav Shabalin Apr 18 '14 at 13:19
  • This is still pretty close to your [previous question](http://stackoverflow.com/questions/23127222/select-for-update-with-subquery-results-in-deadlock). Check out my new update, it may resolve your issue. It is better to update an existing question than to ask a new one, or else this site will be filled with even more unanswered questions. – Jon Heller Apr 19 '14 at 04:33

0 Answers0