This discussion is archived
9 Replies Latest reply: Oct 20, 2008 5:28 AM by 665115 RSS

How to use OCI Serverside Scrollable ResultSet via JDBC-OCI

665115 Newbie
Currently Being Moderated
Hello,

We have the difficult requirement to scroll absolut in a very large ResultSet of 10 million rows. The Problem of the thin driver is that it transports the resultset to the jdbc driver which is not suitable for this.

So I tried the OCI in that way:
PreparedStatement preparedStatement = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE , ResultSet.CONCUR_READ_ONLY );
ResultSet resultSet = preparedStatement.executeQuery( );


-> The problem is when I scroll absolut to row 9.000.000 the memory of the OCI Client increases very very very high. My assumption is that the OCI in usage with JDBC transports the Resultset to the client too. I heard from complete serverside managed resultsets, implemented by the OCI, but I am not able to configure that in combination with jdbc.

We have an Oracle XE Server. I tried XE Client and instant client 10 & 11.. Every client increases its memory usage about 500mb :-(

pse Help.

Regards, Andre, Berlin
  • 1. Re: How to use OCI Serverside Scrollable ResultSet via JDBC-OCI
    665115 Newbie
    Currently Being Moderated
    the next question is when Oracle finally implements the control of serverside scollable resulsets in the thin client. Since 9g scollable resultsets are supported by the DBMS and the driver should use that feature . , but thats another item..
  • 2. Re: How to use OCI Serverside Scrollable ResultSet via JDBC-OCI
    Joe Weinstein Expert
    Currently Being Moderated
    Hi. I am going to be frank with you so I might help you the most. This 'requirement'
    is an example of the number 1 mistake in any DBMS application, treating the DBMS
    like a dumb filesystem, and ignoring location of data. The number 1 rule of DBMS
    applications is "don't move the data". Please try to convince me why the client
    need millions of rows, and to scroll through them!! You should instead write
    set-based queries to find what you really need, and let them 'scroll through the
    data' where it is, in the DBMS. Ideally you should only have the DBMS return the
    specific data you are going to display or pass on to the current user.
    Build your saw-mill where the trees are.

    Joe
  • 3. Re: How to use OCI Serverside Scrollable ResultSet via JDBC-OCI
    665115 Newbie
    Currently Being Moderated
    Hello Joe, Thanks for reply !

    Well, I'm currently working at a monitoring application that is able to display all data that is happen and all columns are free sortable. While the user can scroll in its
    view (resultset) new data are inserted in the table.

    The problem of selecting in sets (like select LIMIT) using :
    SELECT ${column.names} FROM (SELECT ROW_NUMBER() OVER(${order}) rownum1, ${column.names} FROM ${table.names} ${where}) inline_view1 WHERE rownum1 BETWEEN ${offset} AND ${offset} + ${amount}
    is that if the user scrolls new data are inserted. And if the list is not sorted by time (another column) , the oldest data can may placed at first row and are deleted by our application that garanties the data window in days.. (all data older that48 hours will be deleted, may while a user take a view to this data)

    So a new select of the new range of the offset and amount that is needed by scrolling is not available at this offset and amount anymore. !-)

    So the user has to scroll in One resultSet.

    Thats the reason why to scroll in 10Million of rows in a hyper performance DBMS System. Well the DBMS provides the resultset in 15seconds, only the transport of the data is time expensive..

    in hope for sollution.
  • 4. Re: How to use OCI Serverside Scrollable ResultSet via JDBC-OCI
    Joe Weinstein Expert
    Currently Being Moderated
    Using clever SQL you can fetch and display the data a page (or some small subset of
    the data) at a time. Rerun your base query every time they alter the sort order,
    fetching the first display amount, and when they scroll beyond the current data,
    get the next batch (based on the sort order and current last value). This will take
    some skillful SQL, but it can be done.
  • 5. Re: How to use OCI Serverside Scrollable ResultSet via JDBC-OCI
    665115 Newbie
    Currently Being Moderated
    Joe, I am very fit in sql, doing that since 10 years... I will think about +(based on the sort order and current last value)+ but i agree that it will be a strange sql.. I have to identify the current rownum of last value and compare that to the amount i want to have within one select :) Oh man Ohm man..

    Ok. Many thanks Joe. Its a workarround, may a solution, but is there no solution of complete serverside resultset handling may via sql-functions ore some else ? The background is that I came from the DB2 area. They got only one jdbc driver. everything is serverside. The driver is useable for middle tiers and in large computing centres of cause..

    In our product we support all jdbc databases. For every DBMS that supports serverside resultsets via JDBC we enable our ResultSetHolderImpl.. I would be very glad if we can add Oracle to this list.

    The disadvantages of selecting in sets is that scrolling will be slow due to resultset rebuilding, but I will check out your idea.

    --------

    many thanks, Andre
  • 6. Re: How to use OCI Serverside Scrollable ResultSet via JDBC-OCI
    665115 Newbie
    Currently Being Moderated
    Ups. Joe I think its not a solution. Our server application deletes old data in background and if the last value is the dataset that is deleted we have no chance.

    I think that a typical example of the limitations of oracle, but i'm in hope :) .. The only sollution is one resultset, serverside.

    regards

    Edited by: user10426085 on 15.10.2008 09:43
  • 7. Re: How to use OCI Serverside Scrollable ResultSet via JDBC-OCI
    Joe Weinstein Expert
    Currently Being Moderated
    I think you can work around that. Use an inquality for the search. (get me the next batch
    of rows whose value is (greater than/less than) the last one you displayed. Whether that
    value still exists doesn't matter.
    And caching all the query result in the driver/client space is no protection against the
    DBMS deleting data.

    Edited by: joe.weinstein@oracle.com on Oct 15, 2008 10:18 AM
  • 8. Re: How to use OCI Serverside Scrollable ResultSet via JDBC-OCI
    665115 Newbie
    Currently Being Moderated
    I will think about Whether that value still exists doesn't matter this night an will be back in 15 hours in *this post.
    And caching all the query result in the driver/client space is no protection against the
    DBMS deleting data.
    I don't want to cache the resultset on client side. Serverside resultset (@DBMS) over the complete table is my target and than data can be deleted. I use TYPE_SCROLL_INSENSITIVE cursor, so data changes will not modify my resultset and scrolling within the resultset is possible, because its build over the complete table like a snapshot and will be closed only when the user explizit refreshs the resultset or leaves the monitoring app..

    When I request a resultset via JDBC:OCI and scrolling absolut to the last row Oracle transports All rows to the client, but I just fetch one row.. Thats not a problem in web apps etc, but in mass data usecase.. hm.. thinking about the doesn't matter and will be back


    Regards

    Edited by: user10426085 on 15.10.2008 10:55

    but wat's in the case when you have 30mill entries in the table? Than its not scrollable.. I have the situation that our monitoring requierements are not manageable by Oracle. DB2 and MSSql did it. Here must be a solution.

    Edited by: user10426085 on 15.10.2008 14:06
  • 9. Re: How to use OCI Serverside Scrollable ResultSet via JDBC-OCI
    665115 Newbie
    Currently Being Moderated
    I will test REF_CURSOR via stored procedure if the reference will be used in the resultset.

    - > No chance. OCI_STMT_SCROLLABLE_READONLY is not supported in the JDBC:OCI Bridge

    arrrg, It would be very nice if a Oracle developer can help this out

    Edited by: user10426085 on 16.10.2008 13:38

    For everybody that need Scrolable Serversides via JDBC :

    http://ruby-oci8.rubyforge.org/en/
    ( in combination with jruby, merci to the oracle driver developers )

    Edited by: user10426085 on 17.10.2008 12:23

    One mor possibility to use OCI_STMT_SCROLLABLE_READONLY ( see DBI Module -> OracExecModes )
    http://activeperl.softonic.de/

    Edited by: user10426085 on 20.10.2008 05:27

Legend

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