Re: Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement

user00_at_hotmail.com (Steve) wrote in
news:aae1fe62.0209161432.303036c3_at_posting.google.com:

> [ how to efficiently browse in an RDBMS ]

Hey Steve,

I've been meaning to write a white paper on this very topic. I'll give you a 50,000' view... :)

The idea is to create a '[pad]' table which houses a list of 'id' (I use surrogate ID's in all my tables! <G>) The [pad] table is a recursive table which allows us to have as many rows as needed to store the ID's that we need:

  /* avoid fragmentation by using CHAR's. Map the size of each row

     to create even multiples of rows per data page */   [pad]

     id           NUMERIC(16)
     scratch_1    CHAR(...)
     scratch_2    CHAR(...)
     parent_id    NUMERIC(16)

When we wish to browse through a list of rows across many different tables, we generate a list of surrogate ID's off of the main 'driver' table. Using Oracle's bulk-collects, we can efficient snarf, say 400 id's -- after all, who wants to browse through more than 400 rows??! <g>

We create an entry or entries in the [pad] table:

   233, 444, 2333

We return to the front-end the first page of rows _and_ the [pad].id value.

If the front-end wishes to view the next page, they send the stored procedure the [pad].id and the row ranges to view.

The underlying packages fetch from the [pad] table the list of ID's and use dynamic SQL (Oracle and Sybase ASE support this) to retrieve that set of data:

     /* Use an ORDER BY to reflect the orignal set of data */
     select .... from .... where ... a.id in ('233, 444, 2333')
     order by ...

HTH! :) ps: My plan was to really write this up by Oct 1st:

     http://www.hpdbe.com/white_papers/index.html

-- 
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com