Oracle paging query format (11) 01/16 Update SLTechnology News&Howtos

Oracle paging query format (11)

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Servers >

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

The paging query statement of Oracle can basically be applied according to the format given in this article.

This article introduces the parsing function for paging.

Oracle paging query format (1): http://yangtingkun.itpub.net/post/468/100278

Oracle paging query format (2): http://yangtingkun.itpub.net/post/468/101703

Oracle paging query format (3): http://yangtingkun.itpub.net/post/468/104595

Oracle paging query format (4): http://yangtingkun.itpub.net/post/468/104867

Oracle paging query format (5): http://yangtingkun.itpub.net/post/468/107934

Oracle paging query format (6): http://yangtingkun.itpub.net/post/468/108677

Oracle paging query format (7): http://yangtingkun.itpub.net/post/468/109834

Oracle paging query format (8): http://yangtingkun.itpub.net/post/468/224557

Oracle paging query format (9): http://yangtingkun.itpub.net/post/468/224409

Oracle paging query format (10): http://yangtingkun.itpub.net/post/468/224823

Oracle introduced the analysis function from 8i, and the analysis function in 9i has been further developed and has been very stable.

The function of paging can also be realized by using the function of analysis function.

The first step is to construct a large table as a test table for a paged query:

SQL > CREATE TABLE T AS

2 SELECT / * + NO_MERGE (A) NO_MERGE (B) * / *

3 FROM DBA_SEQUENCES A, DBA_OBJECTS B

The table has been created.

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

The PL/SQL process completed successfully.

SQL > SELECT COUNT (*) FROM T

COUNT (*)

4584838

SQL > SET TIMING ON

SQL > SET AUTOT 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

8 ORDER BY OBJECT_NAME

10 WHERE ROWNUM = 11

OBJECT_ID OBJECT_NAME

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

Ten rows have been selected.

Time spent: 00: 00: 02.00

Execution Plan

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=20 Bytes=1840)

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

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=34093 Card=4584838 Bytes=362202202)

4 3 SORT (ORDER BY STOPKEY) (Cost=34093 Card=4584838 Bytes=132960302)

5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)

Statistics

0 recursive calls

0 db block gets

96666 consistent gets

56154 physical reads

0 redo size

543 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 OBJECT_NAME, OBJECT_ID

5 ROW_NUMBER () OVER (ORDER BY OBJECT_NAME) RN

6 FROM T

8 WHERE RN BETWEEN 11 AND 20

OBJECT_ID OBJECT_NAME

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

Ten rows have been selected.

Time spent: 00: 00: 02.09

Execution Plan

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=4584838 Bytes=421805096)

1 0 VIEW (Cost=34093 Card=4584838 Bytes=421805096)

2 1 WINDOW (SORT PUSHED RANK) (Cost=34093 Card=4584838 Bytes=132960302)

32 TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)

Statistics

0 recursive calls

0 db block gets

96666 consistent gets

56165 physical reads

0 redo size

548 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

Take a closer look at the standard paging SQL and the paging SQL that uses analysis functions, and there are many interesting differences.

First of all, the results of the two are different. But it doesn't really matter, because they are two different SQL, and the results of both meet the query criteria. There are two reasons for this, one is that the two SQL use different sorting algorithms, and the other is that the sort fields are not unique. Solving this problem is actually very simple, just make sure that the sorting result is unique.

In terms of performance, there is no essential difference between the two, and since there is no index on the column of ORDER BY, both SQL must FULL TABLE SCAN the table. The key is whether the two SQL can reflect the characteristics of paging-STOP KEY. Since the query currently executed by the user may only return the first 100 records, there is no need for paging SQL to sort all the data completely, just find the smallest or maximum 100 records and return the results. You can do this in both SQL, where the standard paging is SORT (ORDER BY STOPKEY), and the parsing function is WINDOW (SORT PUSHED RANK). Paging conditions are basically satisfied as long as you have the ability to push STOP KEY into the sort operation.

From the structure of SQL, standard paging requires 3 layers of nesting, while analysis functions need only 2 layers. But that doesn't mean that if the analysis function is reduced by one layer of nesting, it is necessarily more efficient than the standard pagination. In fact, multiple tests show that the standard paging seems to be a little faster. Compared with the incorrect paging method, both SQL are efficient enough that the previous difference between the two is almost negligible:

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

8 ORDER BY OBJECT_NAME

10)

11 WHERE RN BETWEEN 11 AND 20

OBJECT_ID OBJECT_NAME

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

Ten rows have been selected.

Time spent: 00: 00: 13.18

Execution Plan

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=4584838 Bytes=421805096)

1 0 VIEW (Cost=34093 Card=4584838 Bytes=421805096)

2 1 COUNT

3 2 VIEW (Cost=34093 Card=4584838 Bytes=362202202)

4 3 SORT (ORDER BY) (Cost=34093 Card=4584838 Bytes=132960302)

5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)

Statistics

0 recursive calls

50 db block gets

96666 consistent gets

96806 physical reads

0 redo size

553 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

The above is the use of the wrong paging method, so that Oracle sorts all the data, the time required is more than 6 times the correct paging method.

Finally, set the paging to the last section and check the performance of the two SQL:

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

8 ORDER BY OBJECT_NAME

10 WHERE ROWNUM = 4584811

OBJECT_ID OBJECT_NAME

28423 xml-extension-type24_T

Ten rows have been selected.

Time spent: 00: 00: 12.92

Execution Plan

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=4584820 Bytes=421803440)

1 0 VIEW (Cost=34093 Card=4584820 Bytes=421803440)

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=34093 Card=4584838 Bytes=362202202)

4 3 SORT (ORDER BY STOPKEY) (Cost=34093 Card=4584838 Bytes=132960302)

5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)

Statistics

0 recursive calls

50 db block gets

96666 consistent gets

96810 physical reads

0 redo size

533 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 OBJECT_NAME, OBJECT_ID

5 ROW_NUMBER () OVER (ORDER BY OBJECT_NAME) RN

6 FROM T

8 WHERE RN BETWEEN 4584811 AND 4584820

OBJECT_ID OBJECT_NAME

28423 xml-extension-type24_T

Ten rows have been selected.

Time spent: 00: 00: 18.78

Execution Plan

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=4584838 Bytes=421805096)

1 0 VIEW (Cost=34093 Card=4584838 Bytes=421805096)

2 1 WINDOW (SORT PUSHED RANK) (Cost=34093 Card=4584838 Bytes=132960302)

32 TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)

Statistics

0 recursive calls

48 db block gets

96666 consistent gets

76497 physical reads

0 redo size

533 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

Since there are only 4584838 records in the table, this page is basically the last one or two pages, which can be said to be an extreme example. Comparing the efficiency of the two, it is found that the standard paging efficiency is nearly 50% higher than the analysis function. Although the examples are extreme, they can also illustrate some problems. First of all, the sorting algorithm by ORDER BY plus ROWNUM should be different from the analysis function. Secondly, the function of the analysis function is very powerful, ORDER BY is only one of them, and the analysis function can also complete more complex operations such as partition and window operation, so it is understandable that the efficiency is lower than that of simple sorting. Of course, in order to keep the version consistent with the previous tests, the test was carried out on 920, and 10g or 11g did not rule out further optimization of the paging of the analysis function.

Finally, it needs to be mentioned that using the method of analyzing functions, you can't do without ORDER BY statements, while the standard paging method can. Of course, there is no ORDER BY can be regarded as a real page, it is a matter of opinion.

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.