I have a question about the connection pooling and such. I have to query a large (multi terra-byte oracle database). The issue is that the data that can be returned is in the millions of records, I only want to display a couple hundred of results at a time when the user gets to the end of the records go and fetch the next set. The user is able to sort on the entire (million plus) set of records, so I am not sure about how to go about doing this. One thought from a college was to create temp table/view but they said since we were closing the connection each time the cursor would not work. Any ideas, am in desperate need tight deadline and performance is has to be fast.
To move the cursor, you can use the following methods: 1.relative(int rows) - moves the cursor relative to its current position. 2.absolute(int row) - positions the cursor on the row-th row of the ResultSet object. Once you have a scrollable ResultSet object, srs in the previous example, you can use it to move the cursor around in the result set. Since the cursor is initially positioned just above the first row of a ResultSet object, the first call to the method next moves the cursor to the first row and makes it the current row. & Dont close the connection..until the page close.. let's try -Vijay
But if you don't close the connection and it is going to hold back lots of system resources. I think it depends on your requirement. For example if you need performance and you don't care about the size, you can do what your colleague tells you, simply do it once for all, query, sort and put them in a temporary table but this is not real-time (any update committed to the table would not be updating the temporary table unless you check the record size in original table before you query from temp table to make sure the table hasn't changed). If your constraint is the space, you just query and sort everytime but you are using up all your CPU usage on database servers. Scroll doesn't help you much as you need to sort the records unless you sort them and put them in temp table, then you scroll.
Dont close the connection..until the page close.. let's try
Maybe this Design Pattern can help you: http://www.corej2eepatterns.com/Patterns2ndEd/ValueListHandler.htm