Shulou(Shulou.com)05/31 Report--

This article mainly introduces "Oracle paging query sentence example analysis". In daily operation, I believe many people have doubts in Oracle paging query sentence example analysis. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "Oracle paging query sentence example analysis". Next, please follow the editor to study!

Performance differences between ORDER BY STOPKEY and ORDER BY in the last pages of a paged query:

SQL > CREATE TABLE T AS SELECT A.* FROM DBA_OBJECTS A, DBA_USERS B, TAB

The table has been created.

SQL > SELECT COUNT (*) FROM T

COUNT (*)

-

458064

SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS (USER,'T')

The PL/SQL process completed successfully.

SQL > SET AUTOT TRACE

SQL > SET TIMING ON

SQL > SELECT OBJECT_ID, OBJECT_NAME

2 FROM

3 (

4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME

5 FROM

6 (

7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP

8)

9 WHERE ROWNUM = 11

Ten rows have been selected.

Time spent: 00: 00: 00.03

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=20 Bytes=1840)

1 0 VIEW (Cost=13888 Card=20 Bytes=1840)

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)

4 3 SORT (ORDER BY STOPKEY) (Cost=13888 Card=458064 Bytes=18780624)

5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics

0 recursive calls

0 db block gets

5579 consistent gets

0 physical reads

0 redo size

694 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

10 rows processed

SQL > SELECT OBJECT_ID, OBJECT_NAME

2 FROM

3 (

4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME

5 FROM

6 (

7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP

8)

9)

10 WHERE RN BETWEEN 11 AND 20

Ten rows have been selected.

Time spent: 00: 00: 09.05

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458064 Bytes=42141888)

1 0 VIEW (Cost=13888 Card=458064 Bytes=42141888)

2 1 COUNT

3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)

4 3 SORT (ORDER BY) (Cost=13888 Card=458064 Bytes=18780624)

5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics

0 recursive calls

41 db block gets

5579 consistent gets

7935 physical reads

0 redo size

689 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

1 sorts (disk)

10 rows processed

For the first few pages of the paging query, ORDER BY STOPKEY has a great advantage over ORDER BY in performance, so for the last few pages of the paging query, is ORDER BY STOPKEY much lower than the normal way like other paging query techniques:

SQL > SELECT OBJECT_ID, OBJECT_NAME

2 FROM

3 (

4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME

5 FROM

6 (

7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP

8)

9 WHERE ROWNUM = 458051

Ten rows have been selected.

Time spent: 00: 00: 09.07

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458060 Bytes=42141520)

1 0 VIEW (Cost=13888 Card=458060 Bytes=42141520)

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)

4 3 SORT (ORDER BY STOPKEY) (Cost=13888 Card=458064 Bytes=18780624)

5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics

0 recursive calls

41 db block gets

5579 consistent gets

7933 physical reads

0 redo size

667 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

1 sorts (disk)

10 rows processed

SQL > SELECT OBJECT_ID, OBJECT_NAME

2 FROM

3 (

4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME

5 FROM

6 (

7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP

8)

9)

10 WHERE RN BETWEEN 458051 AND 458060

Ten rows have been selected.

Time spent: 00: 00: 10.01

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458064 Bytes=42141888)

1 0 VIEW (Cost=13888 Card=458064 Bytes=42141888)

2 1 COUNT

3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)

4 3 SORT (ORDER BY) (Cost=13888 Card=458064 Bytes=18780624)

5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics

0 recursive calls

41 db block gets

5579 consistent gets

7935 physical reads

0 redo size

649 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

1 sorts (disk)

10 rows processed

Unexpectedly, although the performance of the ORDER BY STOPKEY approach also degrades significantly in the last few pages of the paging query, compared with the normal ORDER BY, both belong to the same order of magnitude in terms of logical read, physical read, and execution time.

It seems that the ORDER BY STOPKEY sorting method will not have a significant performance degradation when the STOPKEY is close to the total number of sorting.

At this point, the study on "Oracle paging query sentence example analysis" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.