DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Cursor Question

  1. #11
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.





  2. #12
    Join Date
    May 2000
    Posts
    58
    >>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

  3. #13
    Join Date
    Sep 2000
    Posts
    305
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width