Hi, Yes, indeed. Postgres has something similar.
Regards, Freddy. -----Mensaje original----- De: Pilgrim, Peter [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 16 de junio de 2004 11:51 Para: 'Struts Users Mailing List' Asunto: RE: Caching data from resultset > -----Original Message----- > From: Kies, Daniel [mailto:[EMAIL PROTECTED] > Sent: 14 June 2004 17:52 > To: 'Struts Users Mailing List' > Subject: RE: Caching data from resultset > > > I recently implemented pagination for resultsets using Oracle > 9i. Instead > of loading up the entire resultset into memory, I just > queried based on the > records that the user requested. > > 1) Before getting the records, I first counted the records > that were coming > back in the result. That way I can say to the user...showing > results 26-50 > of 2,183. > 2) For the first resultest, I would run my query appended > with a "rownum > between 1 and 25" > 3) If the user paginates, then I throw the pagination numbers > into the query > so the query would be appended with "rownum between x and y" > MySQL supports the same idea. It has a great finite set optimisation SELECT a,b,c,d * FROM A, B, C WHERE ... ORDER BY ... GROUP BY ... LIMIT 500, 100 This will retrieve just rows number from 500 to but including 600. I just love MySQL for this sort of optimisation. It is so easy you can RAD up large result set or page by page interator in a couple JSTL:sql custom actions. I dont know if Postgres has it. > This is a pretty simple solution to pagination with Oracle. > You may have > some issues getting your resultset ordered properly coming > from Oracle, but > using inline views should take care of that. > If you search the Internet on Oracle 9i and ROWNUM you will find that the ROWNUM is quite confusing for joining up several tables especially those queries with outer join. Unfortunately there no ANSI SQL for this type of query of counting rows or more accurately getting the database server to return a selected view of the data. Every database server seems to have a different way. Also check out the ``Value List Handler'' pattern in J2EE blueprints. I wrote an extension of this patern using Oracle 9i for a client last year, but it got bollocked for a brain dead API that some other developer achieved. Ah well you can't win 'em all. > The advantage to doing this is scaleability. I am running > this code on top > of a Data Warehouse where every milisecond counts. Using > rownum for the > resultset off the database limits query execution time and allows for > resultsets of any size. Using resultsets with a rownum will > allow Oracle to > cache the SQL you are using to execute quicker. > > Let Oracle do the work. > > My $ .02 In my last contract I could not agree more, to let Oracle do the query if you can code right a ROWNUM SQL statement. I think we decided delegate the database query to actual specific PL/SQL stored procedures. The database guys added some another column `position' and did some magic to get the row number to work. Oh yes I remember now, that rownum extension for Oracle applies to the final virtual cartesian product for all the joined database tables specified in a SQL query. -- Peter Pilgrim Operations/IT - Credit Suisse First Boston, 10 South Colonnade, London E14 4QJ, United Kingdom Tel: +44 (0)207 883 4447 ============================================================================== This message is for the sole use of the intended recipient. If you received this message in error please delete it and notify us. If this message was misdirected, CSFB does not waive any confidentiality or privilege. CSFB retains and monitors electronic communications sent through its network. Instructions transmitted over this system are not binding on CSFB until they are confirmed by us. Message transmission is not guaranteed to be secure. ============================================================================== --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
