Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2003
    Posts
    12

    Unanswered: Problems using last() ResultSet method

    Hi,

    I'm retrieving a great number of rows in a ResultSet and after I want to move to the last row using the last() method but the Jdbc driver reads all rows before placing in the last row.

    Can someone tell me how to optimize it or another way to get the same result?

    Thanks and regards
    Jlgadan

  2. #2
    Join Date
    May 2003
    Location
    France
    Posts
    112
    I'm not a JAVA specialist, but can't you re-order sql data to have the last in first in your resultset ?

  3. #3
    Join Date
    Mar 2003
    Posts
    12
    I tried to access changing the order using DESC option but Oracle takes some time before retrieving the first row.

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Would hinting the statement for first_rows (using the DESC) help?

    select /*+ FIRST_ROWS */
    col1,col2,col3
    ....
    from....
    order by ... DESC

    I suspect though that this is sidestepping rather than correcting the problem.

    HTH
    Bill

  5. #5
    Join Date
    Mar 2003
    Posts
    12
    I'm going to explain better my problem, I'm using scrollable cursors in Java and need to have all the rows from the cursor, if I got only the first ones, I'd lose the rest of them.

    Thanks for your help.

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi, the first rows hint doesn't tell Oracle to only return the first rows :-)

    It just changes it's target for an initial response back to you.

    Ie, in a batch processing job of thousands of records, there are no users waiting for a response so you want the job done as quick as possible. The hint would be /*+ ALL_ROWS */ which tells Oracle to aim to complete the whole job as soon as possible.

    When a user is waiting for something on screen, they will usually only see the first top <n> rows of a grid. While they're looking at that Oracle can continue to get the rest of the rows in the background. All rows are still returned but the *initial response* is quicker. The FIRST_ROWS hint will still return all rows in the cursor.

    Try it, see what happens! Maybe in a Java resultset it won't be any difference if the ResultSet insists on getting all rows anyway, I don't know.

    I can toggle performance for users on systems who (stupidly) query an archive table returning 20,000 rows from 1 minute to 1 second using this Oracle hint combined with my client side dataset property of

    DataSet.QueryAllRecords := True
    or
    DataSet.QueryAllRecords := False

    All rows are still in the dataset.

    HTH
    Bill

  7. #7
    Join Date
    Mar 2003
    Posts
    12
    Thanks.

    When I explained my problem with the last() method was for these reasons:

    1.- In my application the user can ask for values greater than
    one gived :
    SELECT FIELD1, FIELD2
    FROM TABLE1
    WHERE FIELD1='VAL1' AND FIELD2>'VAL2'
    ORDER BY FIELD1, FIELD2

    In that case It's obtined a Cursor.

    2.- After he can ask for the previos row at the first of the cursor. I create a new resultset using '<=' comparator instead of '>' comparator :

    SELECT FIELD1, FIELD2
    FROM TABLE1
    WHERE FIELD1='VAL1' AND FIELD2<='VAL2'
    ORDER BY FIELD1, FIELD2

    Then he gets the previous row placing in the last row of the new cursor.

  8. #8
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Ah, now I see why DESC is no good for you. Also you would need /*+ ALL_ROWS */ as you need the last row / entire result set as quick as possible.

    Unfortunately I would think there is little you can do to improve the performance using the method you have for paging, some ideas....

    The narrower the resultset (ie fewer columns/bytes per row) the quicker the result set can be passed along the wire to your machine. The smaller the result set (ie fewer rows) will also speed things up. I imagine you already know those bits though :-)

    Has the query itself been tuned Oracle side to see what the performance is like?

    HTH
    Bill

  9. #9
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Thumbs up Re: Problems using last() ResultSet method

    Originally posted by jlgadan
    Hi,

    I'm retrieving a great number of rows in a ResultSet and after I want to move to the last row using the last() method but the Jdbc driver reads all rows before placing in the last row.

    Can someone tell me how to optimize it or another way to get the same result?

    Thanks and regards
    Jlgadan

    I guess, you have to start from the statement's explain plan. See what it's and then start tuning from there.

    Hope that helps,

    clio_usa - OCP - DBA
    dbaclick.com

Posting Permissions

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