1. The FIRST_ROWS hint probably won't help you here since you are only returning 1 row. The first row is also the last row is also ALL_ROW. In order to select the MIN value, a full table scan will be required (unless you have an index on the transaction_id column and can get it worked into the WHERE clause!). Also, since FIRST_ROWS and ALL_ROWS both invoke the cost based optimizer, if you don't have statistics generated on the table, Oracle will revert to rule based optimizer and ignore the hint.
2. Since the min function does a sort, you are going to get a sort done with either method.
3. Which method is most suitable depends on what you are trying to do. However, the simple sql statement will be less burdensome since it minimizes the amount of code you have to generate and maintain. Also, as previously pointed out, it involves less overhead on your system.
4. Finally, since they are both fairly trivial scenarios to code, why not just code them up and see firsthand if there is any significant difference? An ounce of experimentation is worth a pound of "expert opinions"! ;-)
Is there any internal logic in oracle that when i use a cursor
it marks all the rows produced by cursor stmt and when i open cursor and fetch them it will return marked rows and also if select stmt results in 100,000 rows it will take time to mark all the rows ? And also If i execute the same cursor as simple select stmt (instead of a cursor) oracle won't take time to mark them and it will be faster. This is not my opininon but somebody argued with me. Is this true ?
As to "why" they do a sort, I don't know. TJTWOW (That's Just The Way Oracle Works).
However, I tried several min/max statements, looked at the execution plans, and they all included sorts - so I would conclude it does a sort.