It's okay to steal the music today, and the post is slowly read slowly. How much can you understand?
Original source: http://www.cnoug.org/viewthread.php? TID = 38 & highlight =% B7% D6% D2% B3
With the development of Internet technology, Web has become more and more applicable to all walks of life. Traditional large or C / S structures are also gradually replaced by the B / S (Browser / Server) structure. And the database, as a container that holds a lot of information, allowing web applications to provide more productive, timely, and personalized information. In web applications, we often encounter data records that need to be searched from the database to satisfy a feature, and then display to a specific user. Often these recordings are so many records, on the one hand, the same page shows an abnormal bloated and unrealistic, and the other aspect is usually not interested in them. They seem to be more concerned about sorting according to certain rules. Several records of some start locations. This requires us to page the data that meet the conditions, put the user more concerned on the home page, and give the freedom to continue to browse (or jump reading) to the specified page or even the last page. Here, we hope to discuss the web paging method when using the Oracle database. We said, a good paging method should meet the following requirements: 1. The data amount of the database processing is minimized; 2. The data amount between the database and the web application server is minimized; assuming that we have the following business: Industry products Table, 100,000 records, fields including product name, industry, market price. When you need to select a certain industry, list all products under the industry, and sort by product name, more than 20 pairs, press each page: create "> rudolf @ TEST902> CREATE TABLE T NOLOGGING 2 As SELECT OBJECT_NAME PRODUCT_NAME, MOD (Object_ID, 4) * 10 Category, 3 Object_ID Price, RPAD ('A', 300, 'B') Supplier 4 from all_Objects Order by 2,1 5 / Table Created. Select> Rudolf @ TEST902> SELECT Count *) from T; count (*) ---------- 21110 With the above statement, we quickly generate a industry product table, where all_Objects is a system table for Oracle (we can often generate similar ways Test Data).
Next, we created an index and analyzed the table using CBO, and analyzed the table to share 1039 data blocks: create "> rudolf @ TEST902> CREATE INDEX T_CATEGORY_PNAME_IND ON T (Category, Product_name) 2 NOLOGGING 3 TABLESPACE INDX 4 / Index created. analyze "> rudolf @ TEST902> analyze table t compute statistics 2 for table 3 for all indexes 4 for all indexed columns 5 / Table analyzed. select"> rudolf @ TEST902> select table_name, blocks, empty_blocks from user_tables where table_name = 'T'; Table_name Blocks EMPTY_BLOCKS ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- T 1039 113 In order to facilitate discussion, let's take a look at the traditional practice: SELECT "> rudolf @ TEST902> SELECT * FROM 2 (Select Rownum RNM, a. * From 3 (Select * From T where category = & category_id 4 ORDER BY Product_Name 5) A 6) Where RNM Between & MinRnm and & MaxRnm 7 Here we use three variables, where category_id represents the industry interested in industries, while MinRNM, M AxRnm then simulates the minimum and maximum line number incorporated when the web program controls paging. We want to select the industry as 20, belonging to all product information on page 289.
We speculate that the above statement will be performed as follows: 1. Remove all records that satisfy Category = & Category_ID 2. Press Product_name to sort 3. Data set between the results of the set of items in the result set "> rudolf @ TEST902 > set autot trace / "> rudolf @ TEST902> / Enter Value for category_id: 20 Enter Value for minRNM: 4981 Enter Value for MAXRNM: 5000 20 Rows SELECTED. EXECUTION Plan ------------------------------------------------------------------------------------ -------------------------------------------- 0 Select Statement Optimizer = first_ROWS (COST = 436 card = 5263 bytes = 1094704) 1 0 View (cost = 436 card = 5263 bytes = 1094704) 2 1 count 3 2 View (cost = 436 card = 5263 bytes = 1026285) 4 3 sort (Order by) (ORDER BY) COST = 436 card = 5263 bytes = 1010496) 5 4 Table access (by index rowid) of 't' (COST = 284 card = 5263 bytes = 1010496) 6 5 index (Range scan) of 't_category_pname_ind' (non-unique) (COST = 31 C Ard = 5263) Statistics -------------------------------------------------------------------------------------------------------------------------------- ------------------ 0 Recursive Calls 0 DB Block gets 284 consistent gets 0 physical reads 0 redo size 1829 bytes sent via SQL * Net to client 514 bytes received via SQL * Net from client 3 SQL * Net roundtrips to / from client 1 sorts (memory) 0 sorts (disk) 20 rows processed We can read the plan according to the number of the second column of the plan, that is, the number of digitally executed, such as "5 index (Range Scan), the number is equal, and executed from top to bottom. The above executive plan shows the same order as us, we see a total of 5,263 records that meet WHERE conditions (Card = 5263 in step 4), which are all taken, and participate in sort (step 3), and All 5263 records have been processed before returning the result set to the user.
However, in fact, the user seems to only care about this page, 20 records. Obviously it is far apart from our minimum requirements for database processing. In the analysis section, 284 uniform reads further illustrate database processing of all records (1039 data blocks, a total of 4 industries with similar products, each industry). Now, we convert the above sentences: SELECT "> rudolf @ TEST902> SELECT * FROM T 2 where category = & category_id 3 Order by Product_name 4 will meet all records of the condition to the client (here is a web application server), then Use the programming language to set the result set. With Java as an example, you can use the ResultSet object method Absolute directly locate records to easily bring the result set. However, it is clear that it is even satisfied with the minimum traffic between the data volume between the database and the Web application server. In many cases, it will significantly affect performance, and even cause a memory overflow of web application servers. Methods We have introduced our method. Method 1: Similar to the traditional approach, we list our method: SELECT "> rudolf @ TEST902> select * from 2 (select rownum rnm, a. * from 3 (select * from t where category = & category_id 4 order by category, product_name 5) a where rownum <= & maxrnm 6) where rnm> = & minrnm 7 Unlike traditional practices, we moved to the second layer from the third layer of judgment from the larger line. Although it is simple, it expresses a completely different intention. Interior view: SELECT ROWNUM RNM, A. * From (select * from t where category = & category_id order by category, product_name) a where rownum <= & maxRNM is a new operation introduced in 8i, in the execution plan, it is reflected in StopKey. This operation is specifically optimized for extracting TOP N. It requires the sort field to pre-establish an index. Since the index is sorted, the problem is taken, it becomes the n-index keyword from the index, and then quickly find the record according to the index. And return to the user. This effectively avoids the case of retrieving all records.
SET "> rudolf @ TEST902> SET Autot Test902> Set Verify Off Enter Value for Category_ID: 20 Enter Value for MaxRNM: 20 Enter Value for MinRNM: 1 20 Rows SELECTED. EXECUTION PLAN ------------------------------------------------------ -------------------------------------------------- - 0 SELECT Statement Optimizer = first_ROWS (COST = 284 card = 20 byte s = 4160) 1 0 View (COST = 284 card = 20 bytes = 4160) 2 1 count (stopkey) 3 2 View (cost = 284 card = 5263 Bytes = 1026285) 4 3 Table Access (by index rot = 5263 bytes = 1010496) 5 4 index (Range scan) of 't_category_pname_ind' (cost = 31 card = 5263) STATISTICS ----------------------------------------------- ----------- 0 Recursive Calls 0 DB Block Gets 7 Consistent Gets 0 Physical Reads 0 Redo Size 1848 BYTES SENT VIA SQL * NET To Client 514 Bytes Received Via SQL * Net from C Lient 3 SQL * NET ROUNDTRIPS TO / FROM Client 0 Sorts (Memory) 0 Sorts (Disk) 20 Rows Processed should be combined with Table Access (by index rot), so that Table Access (by Index Rowid actually only processes the & maxRnm record, 20.
Its execution plan can be explained to: RNM: = 1; for Rec in (Select * from t where category = & category_id order by category, product_name) loop rnm: = RNM 1; if rnm> [$ maxRnm kilns] end IF; Fetch Rec; End loop; Filter Rec WHERE ROWNUM <[$ minRNM] Compared to traditional methods, it greatly reduces the pressure of database processing: 284 uniform readings are 7, and performance is therefore improved. Maybe you noticed, when the user keeps turning backwards, making & maxm When the number of records that meet the conditions, its performance has gradually reduced the level similar to traditional methods: set "> rudolf @ TEST902> set autot trace statistics select "> rudolf @ TEST902> select * from 2 (select rownum rnm, a. * from 3 (select * from t where category = & category_id 4 order by category, product_name 5) a where rownum <= & maxrnm 6) where rnm > = & minRNM 7 / Enter Value for category_id: 20 Enter Value for MAXRNM: 5000 Enter Value for MinRNM: 4981 20 Rows SELECTED. statistics ------------------------------------------------------------------------------------ ------------------------------------ 0 Recursive Calls 0 DB Block Gets 275 Consistent Gets 0 Physical Reads 0 redo size 1829 bytes sent via SQL * Net to client 514 bytes received via SQL * Net from client 3 SQL * Net roundtrips to / from client 0 sorts (memory) 0 sorts (disk) 20 rows processed rudolf @ TEST902> we see, When users browse to pages 249, this approach uses 275 uniform reads, which is very close to 284 conventional approaches. Fortunately, in many applications, 98% of users will only care about the data of the top 5, so that these applications can still benefit from this method. When we change the ORDER BY clause to ORDER BY ... DESC, create a reverse index, we can even change some users to the last 5 pages of data to change the top 5 pages.
Despite this, there are some applications, users browsing the page more likely to be random, then we can use the second method: SELECT "> rudolf @ TEST902> SELECT * from 5 WHERE ROWID IN 3 select rid from 4 (select rownum rno, rowid rid from 5 (select rowid from t 6 where category = & category_id 7 order by category, product_name 8) where rownum <= & maxrnm 9) where rno> = & minrnm 10) 11 in this method In the middle, we consider the index compared with the table, the body is large and small (we can think of it as a small table), so we try to search first in the index to search for a physical location of a page, and then according to these physical locations (ROWID) directly takes out the corresponding record directly, we believe that it will eliminate the high cost of index Range Scan in the previous method (to a moment CBO even think it is higher than Full Table Scan Full Table scan.
ENTER VALUE for category_id: 20 Enter Value for MINRNM: 5000 Enter Value for MinRnm: 4981 20 Rows SELECTED. EXECUTION Plan ---------------------------------------------------------------------------------------------------------------------------- -------------------------------- 0 Select Statement Optimizer = first_ROWS (COST = 5054 card = 5000 BYtes = 1095000) 1 0 nested loops (cost = 5054 card = 5000 BYtes = 1095000) 2 1 View (cost = 31 card = 5000 bytes = 100000) 3 2 Sort (unique) 4 3 count (stopkey) 5 4 View (COST = 31 card = 5263 Bytes = 36841) 6 5 index (Range Scan) of 't_category_pname_ind' (COST = 31 C Ard = 5263 BYtes = 178942) 7 1 Table Access (by user rowid) of 't' (COST = 1 Card) = 1 Bytes = 199) Statistics ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------- 0 Recursive Calls 0 DB Block Gets 50 Consistent Gets 0 Physical Reads 0 Redo Size 1551 Bytes Sent Via Sql * Net To Client 503 BYtes Recei VED VIA SQL * Net from Clom Cliant 2 SQL * Net RoundTrips To / from Clom Clism 1 Sorts (Memory) 0 Sorts (Disk) 20 Rows Processed We can see the execution logic of the statement: RNM: = 1; for Rec in (SELECT * FROM t_category_pname_ind where category = & category_id order by category, product_name) loop rnm: = rnm 1; if rnm> [$ maxrnm then exit loop] end if; fetch rowid; end loop; filter rowid array where rownum <[$ minrnm] select * From T WHERE ROWID IN (ROWID Array); Basically, regardless of the user browsing which page, the data processing volume of the database is relatively similar.
