Oracle paging query statement (4)

The final example shows the sorting of internal loops:

SQL> Create Table T as select * From dba_objects;

The table has been created.

SQL> Create index ind_t_object_name on T (object_name );

The index has been created.

SQL> ALTER TABLE t modify object_name not null;

The table has been changed.

SQL> exec dbms_stats.gather_table_stats (user, 'T ')

The PL/SQL process is successfully completed.

Next we will test the paging query that contains the sorting operation. You can simply divide the query into two different cases. The first sort column is the index column, which can be read using the index. The second sort column has no index.

The first case can be subdivided into two types: full index scan and index scan to locate table records.

In either case, you can use full index scanning to avoid sorting. See the following example:

SQL> set autot trace
SQL> select object_name
2 from
3 (
4 select rownum RN, object_name
5 from
6 (
7 select 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 = choose (cost = 26 card = 20 bytes = 1580)
1 0 view (cost = 26 card = 20 bytes = 1580)
2 1 count (stopkey)
3 2 view (cost = 26 card = 6361 bytes = 419826)
4 3 index (Full scan) of 'ind _ t_object_name '(NON-UNIQUE) (cost = 26 card = 6361 bytes = 108137)

 

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
3 consistent gets
0 physical reads
0 redo size
576 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

In this case, you can obtain the query results through the index, so you can avoid the generation of table scans. In addition, because the index is already sorted, you can use the full scan of the index, even the sorting operation is omitted.

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 object_name
8)
9 where rownum <= 20
10)
11 where rn> = 11;

10 rows have been selected.

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (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 (order 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
81 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

Because the query results cannot be obtained only through index scanning, table scanning is selected in Oracle. This is determined by the initialization parameter settings. Therefore, we recommend that you use the first_rows prompt when paging.

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
22 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
0 sorts (memory)
0 sorts (Disk)
10 rows processed

After the first_rows prompt is used, Oracle does not need to scan the entire table and avoids sorting.

In the last case, the sorting column is not an index column. In this case, sorting is inevitable. However, Oracle does not sort all data in the given paging format, but only sorts the first N records.

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;

10 rows have been selected.

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 64 card = 6361 bytes = 585212)
1 0 view (cost = 64 card = 6361 bytes = 585212)
2 1 count
3 2 view (cost = 64 card = 6361 bytes = 502519)
4 3 sort (order by) (cost = 64 card = 6361 bytes = 260801)
5 4 Table Access (full) of 'T' (cost = 9 card = 6361 bytes = 260801)

 

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
81 consistent gets
0 physical reads
0 redo size
690 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 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 <= 20
10)
11 where rn> = 11;

10 rows have been selected.

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 64 card = 20 bytes = 1840)
1 0 view (cost = 64 card = 20 bytes = 1840)
2 1 count (stopkey)
3 2 view (cost = 64 card = 6361 bytes = 502519)
4 3 sort (order by stopkey) (cost = 64 card = 6361 bytes = 260801)
5 4 Table Access (full) of 'T' (cost = 9 card = 6361 bytes = 260801)

 

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
81 consistent gets
0 physical reads
0 redo size
690 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

Observe the order by steps in two different ways. One is order by with stopkey, and the other is not. When a large amount of data needs to be sorted, the efficiency of sorting with stopkey is much higher than that without stopkey.

SQL> insert into T select T. * from T, user_objects;

You have created 407104 rows.

SQL> commit;

Submitted.

SQL> select object_id, object_name
2 from
3 (
4 select rownum RN, object_id, object_name from
5 (
6 select object_id, object_name from t order by timestamp
7)
8 where rownum <= 20
9)
10 where rn> = 11;

10 rows have been selected.

Used time: 00: 00: 03.78

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 64 card = 20 bytes = 1840)
1 0 view (cost = 64 card = 20 bytes = 1840)
2 1 count (stopkey)
3 2 view (cost = 64 card = 6361 bytes = 502519)
4 3 sort (order by stopkey) (cost = 64 card = 6361 bytes = 260801)
5 4 Table Access (full) of 'T' (cost = 9 card = 6361 bytes = 260801)

 

Statistics
----------------------------------------------------------
268 recursive cballs
0 dB block gets
6215 consistent gets
6013 physical reads
0 redo size
740 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
6 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 from
5 (
6 select object_id, object_name from t order by timestamp
7)
8)
9 where rn between 11 and 20;

10 rows have been selected.

Used time: 00: 00: 11.86

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 64 card = 6361 bytes = 585212)
1 0 view (cost = 64 card = 6361 bytes = 585212)
2 1 count
3 2 view (cost = 64 card = 6361 bytes = 502519)
4 3 sort (order by) (cost = 64 card = 6361 bytes = 260801)
5 4 Table Access (full) of 'T' (cost = 9 card = 6361 bytes = 260801)

 

Statistics
----------------------------------------------------------
26 recursive CILS
12 dB block gets
6175 consistent gets
9219 physical reads
0 redo size
737 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
1 sorts (Disk)
10 rows processed

Observe the execution time of the two query statements and the sorting information in the statistics. For the first query statement, Oracle uses the order by stopkey Method for sorting. The sorting operation only sorts the Top N data, so the sorting operation is stored in the memory, for the second query statement, the data is sorted in full order, and the sorting operation has to be completed on the disk due to the large amount of sorting data. Therefore, it takes a lot of time.

The preceding example shows the standard paging Query format. operations that include sorting can still greatly improve the paging query performance.