Hi All
Help :)
I need to do a browse in an Oracle 8i
table that has more than 800,000 rows
using CF.
I'm displaying 100 rows at a time with
next/previous button and using SELECT * FROM table
and looping over it using
<cfloop query="pagesearch" startrow="#r_begin#" endrow="#r_end#">
My problem is that this works fine with
small tables but hangs with long ones (800,000 rows)
because the app server seems to fetch all rows
from the database server at once,
causing app server to hang.
I've seen Oracle issues about SELECTing
a small number of rows using rownum/rowid
but all issues seems not to work in a
SELECT statement with ORDER clauses.
Some of queries tested were (found somewere in web):
===================================================================
Can one retrieve only rows X to Y from a table?
SELECT * FROM (
SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101
) WHERE RN between 91 and 100 ;
Note: the 101 is just one greater than the maximum row of the required
rows (means x= 90, y=100, so the inner values is y+1).
Another solution is to use the MINUS operation. For example, to display
rows 5 to 7, construct a query like this:
SELECT *
FROM tableX
WHERE rowid in (
SELECT rowid FROM tableX
WHERE rownum <= 7
MINUS
SELECT rowid FROM tableX
WHERE rownum < 5);
===================================================================
I even tried to make a select * from (subquery) where rownum ...
I am not sure if Oracle queries will help or if is there anything on
CF Server that I can use to make it not fetch everything and just
those columns used in parameters MAXROWS
and in the loop statement
<cfloop query="pagesearch" startrow="#r_begin#" endrow="#r_end#">
I miss something like startrow/endrow in the query statement.
Please any ideas?
Thanks in advance
Orison Almeida
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
------------------------------------------------------------------------------
To unsubscribe, send a message to [EMAIL PROTECTED] with
'unsubscribe' in the body or visit the list page at www.houseoffusion.com
