RE: DBIx::Recordset and Oracle
- From: darren@xxxxxxxxxxxxxxxx (Darren Duncan)
- Date: Wed, 27 Jul 2005 03:04:57 -0700
At 10:17 AM +0100 7/27/05, Tielman de Villiers wrote:
One of the biggest reasons I chose to use DBIx::Recordset is for its "start" (aka offset) and "max" (aka limit) search parameters. Most of the web applications I work with, simply requires "paginated selects" from a multitude of databases. Using the DBI posed no problems as long as the databases had "OFFSET and LIMIT" capabilites, but the notable exceptions were Informix and Oracle. My choice was then to either select all rows and do the "limiting and offsetting" at the application level, or having seperate SQL for each database. DBIx::Recordset seemed to be a good alternative, and I have been using it for a while now without problems. I only hit upon this latest "problem" (of DBIx::Recordset collecting metadata) when I was testing against a rather large and better secured (ie, user permissions, schemas, etc) database.
Well, the best solution is still to get Oracle to do all the work. Back around 4 years ago, I used a double-subquery to emulate the non-existent limit/offset functionality, when I wanted paginated selects with Oracle 8i.
Here's an Oracle-native string SQL example, which may contain errors:
SELECT foo, bar
FROM (
SELECT foo, bar, rownum AS my_row_num
FROM (
SELECT foo, bar
FROM baz
ORDER BY foo
) AS baz
) AS baz
WHERE my_row_num >= :min_index AND my_row_num <= :max_indexAs I recall, 'rownum' is the name of a special Oracle variable that gives the ordinal value for a row in the source table, prior to sorting; if not, rename that keyword as appropriate.
Note that getting ordered results from a table is always slow when what you are sorting is a large set, even if you only return a few of them using the method I described. It's always a good practice to filter with WHERE down to, say 1000 rows which are the ones that get paginated.
I suspect this won't be necessary with newer Oracles, since the SQL:2003 standard includes a "window clause", part of which defines pagination I think.
-- Darren Duncan .
- Follow-Ups:
- RE: DBIx::Recordset and Oracle
- From: Job Miller
- RE: DBIx::Recordset and Oracle
- References:
- RE: DBIx::Recordset and Oracle
- From: Catharine Drozdowski
- RE: DBIx::Recordset and Oracle
- From: Darren Duncan
- RE: DBIx::Recordset and Oracle
- Prev by Date: RE: DBIx::Recordset and Oracle
- Next by Date: RE: DBIx::Recordset and Oracle
- Previous by thread: RE: DBIx::Recordset and Oracle
- Next by thread: RE: DBIx::Recordset and Oracle
- Index(es):
Relevant Pages
|
