Cursor Question

[tamilselvan]

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.

[Victoria]

>>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

[shailendra]

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