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

The final example shows that the inner loop contains sorting:

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 changed.

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

The PL/SQL process completed successfully.

Let's test the paging query that contains the sort operation. You can simply divide the query into two different cases, the first sort column is the index column, which can be read by the index, and the second sort column has no index.

The first case can be subdivided into two cases: full index scan and locating to table record through index scan.

In either case, sorting can be avoided through a full scan of the index. Look at 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 = 11

Ten 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 calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

576 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

In this case, the query results can be completely obtained through the index, so the table scan can be avoided, and because the index is already sorted, even the sorting operation is omitted through the full scan of the index.

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 = 11

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

43 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 calls

0 db block gets

81 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

Since you can't get query results from index scans alone, here Oracle chooses table scans. This is determined by initialization parameter settings. Therefore, it is recommended that you use FIRST_ROWS prompts 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 = 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

22 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

0 sorts (memory)

0 sorts (disk)

10 rows processed

After using the FIRST_ROWS prompt, Oracle does not need to scan the whole table and avoids sorting operations.

The last case discussed below is that the sort sequence is not an index column. Sorting is inevitable at this time, but with the paging format given, Oracle does not sort all the data, but only 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

Ten 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 calls

0 db block gets

81 consistent gets

0 physical reads

0 redo size

690 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 ROWNUM RN, OBJECT_ID, OBJECT_NAME

5 FROM

6 (

7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP

8)

9 WHERE ROWNUM = 11

Ten 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 calls

0 db block gets

81 consistent gets

0 physical reads

0 redo size

690 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

Observe the ORDER BY steps of two different writing methods, one is ORDER BY with STOPKEY, the other is without. When a large amount of data needs sorting, the efficiency of sorting with STOPKEY is much higher than that without STOPKEY.

SQL > INSERT INTO T SELECT T.* FROM T, USER_OBJECTS

407104 rows were created.

SQL > COMMIT

The submission is complete.

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 = 11

Ten rows have been selected.

Time spent: 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 calls

0 db block gets

6215 consistent gets

6013 physical reads

0 redo size

740 bytes sent via SQL*Net to client

385 bytes received 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

Ten rows have been selected.

Time spent: 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 calls

12 db block gets

6175 consistent gets

9219 physical reads

0 redo size

737 bytes sent via SQL*Net to client

385 bytes received 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 sort information in the statistics. For the first query statement, Oracle uses the ORDER BY STOPKEY method to sort, and the sorting operation only sorts the data needed by TOP N, so the sorting operation is placed in memory, while for the second query statement, the total sorting of the data is large, and the sorting operation has to be completed on disk, so it takes more time.

From the above example, we can see that the standard paging query format can still greatly improve the paging query performance for operations including sorting.

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.