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.