This discussion is archived
1 Reply Latest reply: Oct 7, 2012 8:53 AM by rp0428 RSS

How to read data in slots in JDBC

719110 Newbie
Currently Being Moderated
I am try to make a join on couple of tables.
Now i fetch data that is large and I need to break my sql so that it picks data in slots ie if i have 20k records , i will execute sql to fetch first 5k then next 5k and so on.

i tried to do it with rounum but fails to achieve it.
Can anyone guide me or a pointer will be apprciated.
  • 1. Re: How to read data in slots in JDBC
    rp0428 Guru
    Currently Being Moderated
    >
    I am try to make a join on couple of tables.
    Now i fetch data that is large and I need to break my sql so that it picks data in slots ie if i have 20k records , i will execute sql to fetch first 5k then next 5k and so on.

    i tried to do it with rounum but fails to achieve it.
    >
    First - there you won't know how many rows the query will select until you fetch them all.

    Second - if you are using JDBC then you can control how many you fetch at a time using the 'setFetchSize' method. By default Oracle JDBC retrieves 10 rows at a time but you can alter this. Note that larger fetch sizes will required a larger buffer so there is a practical limit.

    See the 'Fetch Size' and 'Setting the Fetch Size' sections of the JDBC Developer's Guide.
    http://docs.oracle.com/cd/B28359_01/java.111/b31224/resltset.htm#sthref1001
    >
    Setting the Fetch Size

    The following methods are available in all Statement, PreparedStatement, CallableStatement, and ResultSet objects for setting and getting the fetch size:

    •void setFetchSize(int rows) throws SQLException
    •int getFetchSize() throws SQLException

    To set the fetch size for a query, call setFetchSize on the statement object prior to running the query. If you set the fetch size to N, then N rows are fetched with each trip to the database.

    After you have run the query, you can call setFetchSize on the result set object to override the statement object fetch size that was passed to it. This will affect any subsequent trips to the database to get more rows for the original query, as well as affecting any later refetching of rows.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points