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

Oracle Rownum paging rewriting

-explanation: the case comes from "harvest, not just SQL optimization".

Create test data:

-drop table test_rownum purge

SQL > create table test_rownum as select * from dba_objects

SQL > select count (*) from test_rownum;-75793

SQL > alter session set statistics_level = all

SQL > set linesize 1000

SQL > set pagesize 500

Paged writing 1:

SQL > select * from (select t. *, rownum as rn from test_rownum t) a where a.rn > = 1 and a.rn select * from table (dbms_xplan.display_cursor (null, null, 'allstats last'))

Paged writing 2:

SQL > select * from (select t. *, rownum as rn from test_rownum t where rownum = 1)

View the execution plan:

SQL > select * from table (dbms_xplan.display_cursor (null, null, 'allstats last'))

Summary: the buffer of writing method 1 is 1080, the scanning real data is 75793, the buffer of writing method 2 is only 5, and the scanning real data is 10, which is much better than that of writing method 1.

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

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.