Eric,
Beena gives you the correct method to code a scrolling cursor type
function. However, the overhead can be trivial, even with a +1,000,000
row table.
The secret is to make sure that the dbms does not need to make an image
of the complete query. How do you know if it did/didn't? Have your DBA
do a showplan on the query you are going to use for scrolling and make
sure that there aren't sorts being performed.
If you have an index that matches all of the conditions for your WHERE
and ORDER BY clause, you should see great performance no matter how big
the table is.
John
Eric Knapp wrote:
>
> At 11:47 AM 3/26/99 -0500, you wrote:
> >Hi
> >Does anyone have a example of using cursors with JDBC.
> >The issue we are trying to solve is using a servlet to display only
> >10 rows at a time to the user from a table that may contain a million
> >records.
> >The table is in an oracle database.
> >The oracle driver apis have not been very helpful.
> >Any information will be greatly appreciated
> >
>
> Beena,
>
> This is not an easy problem to solve, especially with Oracle. Oracle does
> not have the concept of scrolling cursors. Also, JDBC does not have
> scrolling cursors due to the fact that very few RDBMS products support
> them. There are several approaches. The brute force method is to open a
> cursor on the entire table with an order by clause on the primary key. Then
> fetch the first ten records and save the last key value. Then close the
> cursor. When the next page is asked for submit a query that asks for the
> data that is greater than the last saved key. Repeat for paging. The
> problem with this approach is that each cursor that you open will create an
> image of the complete query. This has too much overhead with a million
> records. It can also be slow.
>
> Another approach works if your table is very static. You can make another
> table based on your main table that only contains a sequence number and the
> primary key of the main table. You then query the data asking for records
> 1-10. The next page gets records 11-20 and so forth. This works great if
> your table doesn't change very often, like every night. You then just have
> to rebuild the lookup table. If your table changes all the time in a
> transaction environment this won't work.
>
> The approach that I'm using is to use the Oracle ConText cartridge. This
> wonderful add-on to Oracle allows me to perform many useful functions that
> are very dificult to do with just plain Oracle. Some of those are very low
> overhead query hit counts, the ability to do next/previous sections of a
> query result set, intelligent queries on the text content within large text
> fields, thesaurus expansion of keyword searching, to name just a few. The
> major drawbacks of the ConText cartridge is that you have to pay extra for
> it and in order to use it you MUST go to Oracle training for it. There just
> isn't any good documentation on ConText. I have been to the training
> recently and it is of high quality.
>
> -Eric
>
> ___________________________________________________________________________
> To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
> of the message "signoff SERVLET-INTEREST".
>
> Archives: http://archives.java.sun.com/archives/servlet-interest.html
> Resources: http://java.sun.com/products/servlet/external-resources.html
> LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
