Web paging method when using Oracle Database (1)

With the development of Internet technology, Web has been increasingly applied to all walks of life. Traditional applications based on the big machine or C/S structure are gradually replaced by the B/SBrowser/Server structure. The database, as a container for storing a large amount of information, enables WEB applications to provide more colorful, timely, and personalized information.

In WEB applications, we often encounter the need to search for data records that meet a certain feature from the database and then display them to specific users. There are often so many records that meet the conditions. On the one hand, the display on the same page is very bloated and impractical, and on the other hand, users are usually not interested in them, they seem to be more concerned with several records that appear in certain starting positions by certain rules. This requires us to paging the data that meets the conditions, place the records that users are more interested in on the home page, and give us the freedom to continue browsing or skip reading) to the specified page or even the last page. Here, we hope to discuss with you the WEB paging method when using ORACLE databases.

We say that a good paging method should meet the following requirements:

1. The minimum amount of data processed by the database;

2. The minimum amount of data transmitted between the database and the WEB application server.

Assume that we have the following businesses: industry product table, with 0.1 million records. The fields include the product name, industry, and market price. If you want to select an industry, list all the products in the industry and sort them by product name. If you want to select more than 20 products, click the 20 products on each page:

rudolf@TEST902>create table t nologging2　as select object_name product_name,mod(object_id,4)*10 category,3object_id price,rpad('a',300,'b') supplier4 from all_objects order by 2,15　/Table created.rudolf@TEST902>select count(*) from t;COUNT(*)----------21110

With the preceding statements, we quickly generate an industry product table. Among them, all_objects is a system table of oracle. We can often generate test data using a similar method ). Next, we created an index and analyzed the table using CBO. The analysis showed that the table shared 1039 data blocks:

rudolf@TEST902>create index t_category_pname_ind on t (category,product_name)2　nologging3　tablespace indx4　/Index created.rudolf@TEST902>analyze table t compute statistics2　for table3　for all indexes4　for all indexed columns5　/Table analyzed.rudolf@TEST902>select table_name,blocks, empty_blocks from user_tables where table_name = 'T';TABLE_NAME BLOCKS EMPTY_BLOCKS------------------------------ ---------- ------------T1039　113

To facilitate discussion, let's first look at the traditional practices:

rudolf@TEST902>select * from2　 ( select rownum rnm, a.* from3 ( select * from t where category = &category_id4 order by product_name5 ) a6　) where rnm between &minrnm and &maxrnm7

Here we use three variables. category_id indicates the industries that interest users, while minrnm and maxrnm simulate the minimum and maximum row numbers passed in when web programs control pages. We hope to select the industry 20, which belongs to all product information on the 289th page. We assume that the preceding statement will be executed in the following steps:

1. Retrieve all records that meet category = & category_id;

2. sort by product_name;