I have a table with 100,000 rows. I have a col status_cd with possible values of ( 'I' OR 'P'). I want to get min transaction_id of column with value P.
My question is , is it better to have a either
1. Cursor c1 is select * from table where status_cd = 'P'
order by transaction_id desc ;
open cursor and get first row and exit from cursor
2. select min(transcation_id) from table where status_cd = 'P' ;
Which one runs faster and why if i have index on status_cd.
Thanks in advance
First thing, if you want "min", you should have your cursor def as "order by trans_id asc" and not "order by trans_id desc". I think this just a minor thing.
On performance front, I think both will be the same. You may get better result if you have the IOT.
I think the second choice will be better. There are overheads in fetching from a cursor and also the first query will do a sort which will be time consuming.
Using Hint clause FIRST_ROW in the select statement will be faster than any other methods. The 2nd choice is using cursor.
Can i use hint FIRST_ROWS even if i use a MIN function in the select statemtent?
If the STATUS_ID column values are evenly distributed, index won't help performance. In this case, avoid using index.
You can use FIRST_ROWS hint in any SELECT statement.
A couple of points here -
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 ?
Why should the MIN function do a sort? I think one scan is good enough the find the min/max value. Does Oracle say that they do a sort for finding the MIN/MAX? Correct me if I'm wrong.
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.