Question: I have an application where I
must paginate the results, like Google results. I create a
cursor for the SQL, and I display the max results like this:
"Displaying rows 1 through 25 of 1,497"
Currently I am doing this by submitting a second query: select
count(*) from the table.
This counting of the estimated total
results is very time-consuming. What is a better way to do
display the max number of results in pagination with SQL?
Answer: In some pagination algorithms, you
use a loop based on a result set, using an array (collection) based
on an indexed position, placing the results into a temp table or
PL/SQL array and selecting from RAM. But there are other
approaches to pagination, see here,
how to
paginate Oracle SQL results.
Take Google for example.
Google does not truly know the actual number of results because it
is constantly changing, and they use a "guessing algorithm".
For displaying the "max results" from the paginated query, you need
to "guess" this because it is not practical to do a count(*).
There are many approaches to "estimating the actual total results:
- Cheating: I have seen Bangalore
Bargains generate a random number in cases where the end-users
don't care! A truly shoddy and dishonest approach.
- Fetch it all: If you have the RAM,
you can store all of the result pages in a large PL/SQL array
and quickly count the number of rows returned from the PL/SQL
collection.
- Data Dictionary: You can start with
use num_rows from dba_tables (stale from the last run of
dbms_stats), and use additional data for subtracting from
the histograms in the query using the value range counts in
dba_histograms.
- Stale tolerated materialized view:
Another popular approach to guessing the max results from
pagination is to drop and re-create create a
stale-tolerated materialized view each night, counting the
number of rows based on the most common query values. The
Materialized view gets stale during the day, but nobody expects
a perfectly accurate count.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
