Oracle paging query statement (5)

The previous examples demonstrate the performance improvement caused by the standard writing of paging query statements.

Here, we will briefly describe when paging query statements cannot improve performance.

The paging query statement can return results quickly because it aims to return the first result as quickly as possible. If there are 20 records on each page and the current page is 5th, you only need to return the first 100 records to meet the query requirements, there may be tens of thousands of records that meet the query conditions. However, due to paging restrictions, you can ignore the data in the current query. Instead, you only need to return the first 100 data records as soon as possible. This is why first_rows is often used in standard paging query statements.

For row operations, the results can be directly returned to the previous layer for calling when the results are obtained. However, for result set operations, Oracle must obtain all the data in the result set. Therefore, the rownum information contained in the paging query cannot be left or right. If the inmost subquery contains more than one of the following operations, the paging query statement cannot reflect any performance advantages: union, Union all, minus, intersect, group by, distinct, unique, and aggregate functions such as Max, Min, and analysis functions.

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

Paging query is used to increase the return speed for the first N records with a small amount of data. Whether it is index scanning, nested loop connection, or order by stopkey, the premise of these methods to improve performance is that the data volume is small. Once the pages are displayed, we will find that these methods not only have no way to improve the performance, but also have a much lower performance than normal queries. When using paging queries, you must be aware of this.

Finally, let's look at several examples:

First, let's take a look at Union all, group by, and the analysis function to make the rownum limit of the outer layer invalid for the inner layer 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 <= 20
10)
11 where rn> = 11;

10 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 cballs
0 dB block gets
23 consistent gets
0 physical reads
0 redo size
597 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
10 rows processed

This is the case where rownum is used in paging queries. Let's take a look at the case where the inner layer queries include the set operation:

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 <= 20
13)
14 where rn> = 11;

10 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 cballs
0 dB block gets
322 consistent gets
0 physical reads
0 redo size
546 bytes sent via SQL * Net to client
503 bytes encoded ed 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 <= 20
13)
14 where rn> = 11;

10 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 cballs
0 dB block gets
24004 consistent gets
0 physical reads
0 redo size
546 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (Disk)
10 rows processed

If the rownum does not work in a full table scan, and the hint is used to enable Oracle to use an index scan, then the rownum does not work.

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 <= 20
12)
13 where rn> = 11;

10 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)
4 3 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 cballs
0 dB block gets
161 consistent gets
0 physical reads
0 redo size
673 bytes sent via SQL * Net to client
503 bytes encoded ed 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 <= 20
12)
13 where rn> = 11;

10 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 cballs
0 dB block gets
12002 consistent gets
0 physical reads
0 redo size
597 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (Disk)
10 rows processed

The example above illustrates the types of queries that the optimization of paging query does not work for. Let's take a look at the performance of paging query at the end of page turning.

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 <= 20
12)
13 where rn> = 11;

10 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 cballs
0 dB block gets
23 consistent gets
0 physical reads
0 redo size
597 bytes sent via SQL * Net to client
503 bytes encoded ed 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;

10 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)
4 3 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 cballs
0 dB block gets
161 consistent gets
0 physical reads
0 redo size
597 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (Disk)
10 rows processed

First, you can see that the paging query performance is much better than that without rownum. However, if you set the paging range to between 12710 and 12720, at this time, we will compare the efficiency of the two types of 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< = 12720
12)
13 where rn >=12711;

10 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 cballs
0 dB block gets
12001 consistent gets
0 physical reads
0 redo size
612 bytes sent via SQL * Net to client
503 bytes encoded ed 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 rnbetween 12711 and 12720;

10 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)
4 3 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 cballs
0 dB block gets
161 consistent gets
0 physical reads
0 redo size
612 bytes sent via SQL * Net to client
503 bytes encoded ed 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 the efficiency of the second query is the same as that of the first query, but the efficiency of the paging query is greatly reduced, and the efficiency is far lower than that of the query without rownum.