Cursor Question

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.

Thanks.

Syed

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 ?

Carp,

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.

Thanks.

Syed

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.

select min(transcation_id) from table where status_cd = 'P' ;
is the fastest execution statement.

I have tested a customer table with 1M rows out of which 900000 rows have status "A" active and 100000 rows have "I" inactive.
When I use MIN function, the optimizer selects SORT AGGREGATE, where in the "SELECT * from CUSTOMER where status= 'A' order by some_column DESC; " statement the optimizer selects SORT ORDER BY.

The FIRST_ROWS hint does not help in this situation, even if I use cursor with DESCending column.

>>select min(transcation_id) from table where status_cd = 'P' ;

You may also try create a compound index on status_cd and
transaction_id and then get the min(transaction_id) using the hint INDEX_ASC.

Create index stat_trans_ix on table( status_cd , transaction_id) ;

Select /*+ INDEX_ASC ( a stat_trans_ix ) */
transaction_id from table a
where status_cd = 'P' and transaction_id > 0 and rownum = 1;


If you dont want transaction_id to be part of the index, then
use
select min(transcation_id) from table where status_cd = 'P' ;
Oracle has a very powerful sorting algorithm .

Hope this helps
Victoria

u can do one thing
write on sql promt
set timing on
and execute both the things
and see which one will take less time

One more thing you should use bitmap index on that column