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

This article introduces the relevant knowledge of "what is the Oracle paging query format?". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Because of the above set operations, Oracle must process all the data before submitting all the results to the user. Even if ROWNUM,Oracle is included, it is only after all the data has been processed that the required data is returned.

However, it is not that the standard writing mentioned above does not make sense here, but that the most time-consuming parts have been dealt with. It doesn't make much sense to limit through ROWNUM.

Although standard paging doesn't make much sense for operations such as GROUP BY, if you need to calculate the total before performing paging, you can modify the paging query slightly to combine the total calculation with the SQL on the first page of the query to avoid calculating the total at once.

SQL > ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'

The session has changed.

SQL > CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A

The table has been created.

SQL > ALTER TABLE T ADD PRIMARY KEY (ID)

The table has changed.

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

The PL/SQL process completed successfully.

SQL > SET AUTOT ON

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT OBJECT_TYPE, CREATED, COUNT (*)

8 FROM T

9 GROUP BY OBJECT_TYPE, CREATED

10 ORDER BY CREATED

11) A

12 WHERE ROWNUM

< 20 13 ) 14 WHERE RN >

= 10

RN OBJECT_TYPE CREATED COUNT (*)

--

10 TABLE 2003-11-13 01:41:01 16

11 CLUSTER 2003-11-13 01:41:02 3

12 INDEX 2003-11-13 01:41:02 31

13 LOB 2003-11-13 01:41:02 2

14 SEQUENCE 2003-11-13 01:41:02 4

15 TABLE 2003-11-13 01:41:02 20

16 INDEX 2003-11-13 01:41:03 16

17 LOB 2003-11-13 01:41:03 6

18 SEQUENCE 2003-11-13 01:41:03 2

19 SYNONYM 2003-11-13 01:41:03 1

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=19 Bytes=874)

1 0 VIEW (Cost=97 Card=19 Bytes=874)

2 1 COUNT (STOPKEY)

32 VIEW (Cost=97 Card=32185 Bytes=1062105)

4 3 SORT (GROUP BY STOPKEY) (Cost=97 Card=32185 Bytes=547145)

54 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics

0 recursive calls

0 db block gets

232 consistent gets

0 physical reads

0 redo size

759 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 *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT OBJECT_TYPE, CREATED, COUNT (*)

8 FROM T

9 GROUP BY OBJECT_TYPE, CREATED

10 ORDER BY CREATED

11) A

12)

13 WHERE RN > = 10 AND RN

< 20; RN OBJECT_TYPE CREATED COUNT(*) ---------- ------------------ ------------------- ---------- 10 TABLE 2003-11-13 01:41:01 16 11 CLUSTER 2003-11-13 01:41:02 3 12 INDEX 2003-11-13 01:41:02 31 13 LOB 2003-11-13 01:41:02 2 14 SEQUENCE 2003-11-13 01:41:02 4 15 TABLE 2003-11-13 01:41:02 20 16 INDEX 2003-11-13 01:41:03 16 17 LOB 2003-11-13 01:41:03 6 18 SEQUENCE 2003-11-13 01:41:03 2 19 SYNONYM 2003-11-13 01:41:03 1 已选择10行。 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=32185 Bytes=1480510) 1 0 VIEW (Cost=97 Card=32185 Bytes=1480510) 2 1 COUNT 3 2 VIEW (Cost=97 Card=32185 Bytes=1062105) 4 3 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145) 5 4 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 232 consistent gets 0 physical reads 0 redo size 759 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 由于查询操作中GROUP BY操作是耗时最大的SQL，因此标准分页方式在这里所带来的性能提升十分有限。但是，如果在执行分页查询前需要执行COUNT(*)的话，那么可以考虑将COUNT(*)的结果和分页结果一起返回，从而减少了一次计算总数所需的时间。 SQL>

SELECT COUNT (*)

2 FROM

3 (

4 SELECT OBJECT_TYPE, CREATED, COUNT (*)

5 FROM T

6 GROUP BY OBJECT_TYPE, CREATED

7 ORDER BY CREATED

8)

COUNT (*)

-

3570

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=1)

1 0 SORT (AGGREGATE)

21 VIEW (Cost=97 Card=32185)

32 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)

4 3 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics

0 recursive calls

0 db block gets

232 consistent gets

0 physical reads

0 redo size

377 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)

1 rows processed

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT OBJECT_TYPE, CREATED, COUNT (*)

8 FROM T

9 GROUP BY OBJECT_TYPE, CREATED

10 ORDER BY CREATED

11) A

12 WHERE ROWNUM

< 20 13 ) 14 WHERE RN >

= 10

RN OBJECT_TYPE CREATED COUNT (*)

--

10 TABLE 2003-11-13 01:41:01 16

11 CLUSTER 2003-11-13 01:41:02 3

12 INDEX 2003-11-13 01:41:02 31

13 LOB 2003-11-13 01:41:02 2

14 SEQUENCE 2003-11-13 01:41:02 4

15 TABLE 2003-11-13 01:41:02 20

16 INDEX 2003-11-13 01:41:03 16

17 LOB 2003-11-13 01:41:03 6

18 SEQUENCE 2003-11-13 01:41:03 2

19 SYNONYM 2003-11-13 01:41:03 1

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=19 Bytes=874)

1 0 VIEW (Cost=97 Card=19 Bytes=874)

2 1 COUNT (STOPKEY)

32 VIEW (Cost=97 Card=32185 Bytes=1062105)

4 3 SORT (GROUP BY STOPKEY) (Cost=97 Card=32185 Bytes=547145)

54 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics

0 recursive calls

0 db block gets

232 consistent gets

0 physical reads

0 redo size

759 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

Compared with the above two queries, the following two methods can be implemented through a SQL statement:

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT COUNT (*) OVER () CNT, OBJECT_TYPE, CREATED, COUNT (*) CN

8 FROM T

9 GROUP BY OBJECT_TYPE, CREATED

10 ORDER BY CREATED

11) A

12 WHERE ROWNUM

< 20 13 ) 14 WHERE RN >

= 10

RN CNT OBJECT_TYPE CREATED CN

--

10 3570 TABLE 2003-11-13 01:41:01 16

11 3570 CLUSTER 2003-11-13 01:41:02 3

12 3570 INDEX 2003-11-13 01:41:02 31

13 3570 LOB 2003-11-13 01:41:02 2

14 3570 SEQUENCE 2003-11-13 01:41:02 4

15 3570 TABLE 2003-11-13 01:41:02 20

16 3570 INDEX 2003-11-13 01:41:03 16

17 3570 LOB 2003-11-13 01:41:03 6

18 3570 SEQUENCE 2003-11-13 01:41:03 2

19 3570 SYNONYM 2003-11-13 01:41:03 1

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=19 Bytes=1121)

1 0 VIEW (Cost=97 Card=19 Bytes=1121)

2 1 COUNT (STOPKEY)

32 VIEW (Cost=97 Card=32185 Bytes=1480510)

4 3 WINDOW (BUFFER) (Cost=97 Card=32185 Bytes=547145)

54 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)

6 5 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics

0 recursive calls

0 db block gets

232 consistent gets

0 physical reads

0 redo size

808 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

10 rows processed

SQL > SELECT *

2 FROM

3 (

4 SELECT COUNT (*) OVER () CNT, ROWNUM RN, A.*

5 FROM

6 (

7 SELECT OBJECT_TYPE, CREATED, COUNT (*) CN

8 FROM T

9 GROUP BY OBJECT_TYPE, CREATED

10 ORDER BY CREATED

11) A

12)

13 WHERE RN > = 10 AND RN < 20

CNT RN OBJECT_TYPE CREATED CN

--

3570 10 TABLE 2003-11-13 01:41:01 16

3570 11 CLUSTER 2003-11-13 01:41:02 3

3570 12 INDEX 2003-11-13 01:41:02 31

3570 13 LOB 2003-11-13 01:41:02 2

3570 14 SEQUENCE 2003-11-13 01:41:02 4

3570 15 TABLE 2003-11-13 01:41:02 20

3570 16 INDEX 2003-11-13 01:41:03 16

3570 17 LOB 2003-11-13 01:41:03 6

3570 18 SEQUENCE 2003-11-13 01:41:03 2

3570 19 SYNONYM 2003-11-13 01:41:03 1

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=32185 Bytes=1898915)

1 0 VIEW (Cost=97 Card=32185 Bytes=1898915)

21 WINDOW (BUFFER) (Cost=97 Card=32185 Bytes=1062105)

3 2 COUNT

4 3 VIEW (Cost=97 Card=32185 Bytes=1062105)

54 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)

6 5 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics

0 recursive calls

0 db block gets

232 consistent gets

0 physical reads

0 redo size

808 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

10 rows processed

The first method uses the standard paging method, which is relatively more efficient, but the first method needs to modify the original SQL, while the second method does not need to modify the original SQL, but can be implemented by adding some code outside the original SQL.

This is the end of the content of "what is the format of Oracle paging query". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.