Shulou(Shulou.com)06/01 Report--

For the Web display of the data of the tables in the database, if there is no need to show the order, and because there are so many records that meet the conditions, the data has to be paged. Often users are not interested in all the data, or in most cases, they only look at the first few pages.

There are usually two paging techniques to choose from.

Select * from (Select rownum rn,t.* from table t) Where rn > & minnum and rncreate table test as select * from dba_objects

And insert the same data repeatedly.

SQL > insert into test select * from test

Finally, when you query the table, you can see that the number of records in the table is about 800000.

SQL > select count (*) from test COUNT (*)-831104

Now two paging methods are used, in the first paging method:

SQL > select * from (2 select rownum rn,t.* from test t) 3 where rn > 0 and rn select * from (2 select rownum rn,t.* from test t 3 where rownum 0; 50 rows selected. Time spent: 00: 00: 01.00 Execution Plan--- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=50 Bytes=9500) 10 VIEW (Cost=10 Card=50 Bytes=9500) 2 1 COUNT (STOPKEY) 3 2 TABLE ACCESS (FULL) OF 'TEST' (Cost=10 Card=65 Bytes=5590) Statistics -0 recursive calls 0 db block gets 82 consistent gets 0 physical reads 0 redo size.

The same result is obtained, and there are only 82 consistent readings. From the above example, we can see that by introducing rownum to the second layer, we get a completely different execution plan. Pay attention to the stopkey in the execution plan, which is a new operation introduced by 8i, which is optimized for extracting Top n.

From the above example, you can think again, because the function of stopkey affects the consistency of paging, will the more backward the page is, the slower the speed will be? This is also true, for example:

SQL > select * from (2 select rownum rn,t.* from test t 3 where rownum 9950; 50 rows selected. Elapsed time: 00: 00: 01.01 Statistics--- 0 recursive calls 0 db block gets 2616 consistent gets 0 physical reads 0 redo size.

When you select the lower data, the logical reading begins to get larger, and by the time you select to the last few pages, the consistent reading is similar to the above.

SQL > select * from (2 select rownum rn,t.* from test t 3 where rownum 799950; 50 rows selected. Elapsed time: 00: 00: 01.03 Statistics--- 0 recursive calls 0 db block gets 10242 consistent gets 0 physical reads 0 redo size.

Fortunately, however, most users only look at the first 5% of the data, but are not interested in looking at the last data. Through the second improved paging technology, the previous data can be easily and quickly displayed without making users feel slow.

Summary

The above is the whole content of this article on Oracle paging query performance optimization code, I hope it will be helpful to everyone. Welcome to refer to other related topics on this site, if you have any questions, you can leave a message at any time, the editor will reply you in time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.