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

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

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

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

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

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

The previous examples have illustrated the performance improvements brought about by the standard writing of paging query statements.

Here is a brief summary and a brief explanation of when paging query statements do not improve performance.

The reason why a paging query statement can return results quickly is because its goal is to return the first result as quickly as possible. If there are 20 records per page and you turn to page 5, you only need to return the first 100 records to meet the query requirements, and perhaps tens of thousands of records also meet the query requirements, but due to paging restrictions, these data can be ignored in the current query, and only need to return the first 100 data as soon as possible. This is why FIRST_ROWS prompts are often used in standard paging query statements.

For row operations, you can return the result directly to the previous call while getting the result. But for the result set operation, Oracle must get all the data in the result set, so the ROWNUM information in the paging query can not be controlled. If the innermost subquery contains more than one of the following operations, the paging query statement does not show any performance benefits: UNION, UNION ALL, MINUS, INTERSECT, GROUP BY, DISTINCT, UNIQUE, and aggregate functions such as MAX, MIN, and parsing functions.

In addition to these operations, paging query has an obvious feature, that is, the smaller the number of pages processed, the higher the efficiency, and the later, the slower the query speed.

The methods used to improve the return speed of paging queries are for the first N records with a small amount of data. Whether it is index scanning, NESTED LOOP join, or ORDER BY STOPKEY, these methods bring performance improvement on the premise that the amount of data is relatively small. Once paged to the last few pages, you will find that these methods not only have no way to improve performance, but also have much lower performance than ordinary queries. You must be aware of this when using paging queries.

Finally, let's look at a few examples:

First, take a look at UNION ALL, GROUP BY, and parsing functions to invalidate the outer ROWNUM restrictions on the inner query.

SQL > SET AUTOT TRACE

SQL > SELECT / * + FIRST_ROWS * / 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 OBJECT_NAME

8)

9 WHERE ROWNUM = 11

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=20 Bytes=1840)

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

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=826 Card=6361 Bytes=502519)

4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)

5 4 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics

0 recursive calls

0 db block gets

23 consistent gets

0 physical reads

0 redo size

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

0 sorts (disk)

10 rows processed

This is how the paging query ROWNUM works. Let's take a look at what happens if the inner query includes set operations:

SQL > SELECT / * + FIRST_ROWS * / 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 UNION ALL

9 SELECT OBJECT_ID, OBJECT_NAME FROM T

10 ORDER BY OBJECT_NAME

11)

12 WHERE ROWNUM = 11

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=85 Card=20 Bytes=1840)

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

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=85 Card=12722 Bytes=1005038)

4 3 SORT (ORDER BY STOPKEY) (Cost=18 Card=12722 Bytes=267162)

5 4 UNION-ALL

6 5 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)

7 5 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)

Statistics

0 recursive calls

0 db block gets

322 consistent gets

0 physical reads

0 redo size

546 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 / * + FIRST_ROWS * / OBJECT_ID, OBJECT_NAME

2 FROM

3 (

4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME

5 FROM

6 (

7 SELECT / * + INDEX (T) * / OBJECT_ID, OBJECT_NAME FROM T

8 UNION ALL

9 SELECT / * + INDEX (T) * / OBJECT_ID, OBJECT_NAME FROM T

10 ORDER BY OBJECT_NAME

11)

12 WHERE ROWNUM = 11

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1719 Card=20 Bytes=1840)

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

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=1719 Card=12722 Bytes=1005038)

4 3 SORT (ORDER BY STOPKEY) (Cost=1652 Card=12722 Bytes=267162)

5 4 UNION-ALL

6 5 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)

7 6 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

8 5 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)

9 8 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics

0 recursive calls

0 db block gets

24004 consistent gets

0 physical reads

0 redo size

546 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

If it is not obvious that ROWNUM does not work in the case of full table scans, by using HINT and letting Oracle use index scanning, the effect that ROWNUM does not work is quite amazing.

SQL > SELECT / * + FIRST_ROWS * / 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 GROUP BY OBJECT_ID, OBJECT_NAME

9 ORDER BY OBJECT_NAME

10)

11 WHERE ROWNUM = 11

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=43 Card=20 Bytes=1840)

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

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=43 Card=6361 Bytes=502519)

43 SORT (GROUP BY STOPKEY) (Cost=43 Card=6361 Bytes=133581)

5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)

Statistics

0 recursive calls

0 db block gets

161 consistent gets

0 physical reads

0 redo size

673 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 / * + FIRST_ROWS * / OBJECT_ID, OBJECT_NAME

2 FROM

3 (

4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME

5 FROM

6 (

7 SELECT OBJECT_ID, OBJECT_NAME, ROW_NUMBER () OVER (ORDER BY OBJECT_NAME)

8 FROM T

9 ORDER BY OBJECT_NAME

10)

11 WHERE ROWNUM = 11

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=20 Bytes=1840)

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

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=826 Card=6361 Bytes=502519)

4 3 WINDOW (BUFFER) (Cost=826 Card=6361 Bytes=133581)

5 4 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)

6 5 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics

0 recursive calls

0 db block gets

12002 consistent gets

0 physical reads

0 redo size

597 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

The above example shows which types of queries the optimization of paging queries does not work for. Let's take a look at the performance of paging queries at the end of paging.

SQL > SET AUTOT OFF

SQL > SELECT COUNT (*) FROM T

COUNT (*)

-

12722

SQL > SET AUTOT TRACE

SQL > SELECT / * + FIRST_ROWS * / OBJECT_ID, OBJECT_NAME

2 FROM

3 (

4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME

5 FROM

6 (

7 SELECT OBJECT_ID, OBJECT_NAME

8 FROM T

9 ORDER BY OBJECT_NAME

10)

11 WHERE ROWNUM = 11

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=20 Bytes=1840)

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

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=826 Card=6361 Bytes=502519)

4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)

5 4 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics

0 recursive calls

0 db block gets

23 consistent gets

0 physical reads

0 redo size

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

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

8 FROM T

9 ORDER BY OBJECT_NAME

10)

11)

12 WHERE RN BETWEEN 11 AND 20

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=6361 Bytes=585212)

1 0 VIEW (Cost=43 Card=6361 Bytes=585212)

2 1 COUNT

3 2 VIEW (Cost=43 Card=6361 Bytes=502519)

43 SORT (ORDER BY) (Cost=43 Card=6361 Bytes=133581)

5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)

Statistics

0 recursive calls

0 db block gets

161 consistent gets

0 physical reads

0 redo size

597 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

First of all, let's see that paging queries perform much better than without ROWNUM, but if you set the paging range to between 12710 and 12720, compare the efficiency of the two queries.

SQL > SELECT / * + FIRST_ROWS * / OBJECT_ID, OBJECT_NAME

2 FROM

3 (

4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME

5 FROM

6 (

7 SELECT OBJECT_ID, OBJECT_NAME

8 FROM T

9 ORDER BY OBJECT_NAME

10)

11 WHERE ROWNUM = 12711

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=6361 Bytes=585212)

1 0 VIEW (Cost=826 Card=6361 Bytes=585212)

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=826 Card=6361 Bytes=502519)

4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)

5 4 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics

0 recursive calls

0 db block gets

12001 consistent gets

0 physical reads

0 redo size

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

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

8 FROM T

9 ORDER BY OBJECT_NAME

10)

11)

12 WHERE RN BETWEEN 12711 AND 12720

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=6361 Bytes=585212)

1 0 VIEW (Cost=43 Card=6361 Bytes=585212)

2 1 COUNT

3 2 VIEW (Cost=43 Card=6361 Bytes=502519)

43 SORT (ORDER BY) (Cost=43 Card=6361 Bytes=133581)

5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)

Statistics

0 recursive calls

0 db block gets

161 consistent gets

0 physical reads

0 redo size

612 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

It is not difficult to find that for the second query, the efficiency is exactly the same as the first execution, but the efficiency of the paging query is greatly reduced, and the efficiency is much lower than that of the query without using ROWNUM.

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.