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

This article uses several examples to illustrate the efficiency of paging queries. First, construct a larger table as the test table:

SQL > CREATE TABLE T AS SELECT * FROM DBA_OBJECTS, DBA_SEQUENCES

The table has been created.

SQL > SELECT COUNT (*) FROM T

COUNT (*)

-

457992

First, compare the differences between the two paging methods:

SQL > SET AUTOT ON

SQL > COL OBJECT_NAME FORMAT A30

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

The PL/SQL process completed successfully.

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

8)

9)

10 WHERE RN BETWEEN 11 AND 20

OBJECT_ID OBJECT_NAME

5807 ALL_APPLY_PROGRESS

1769 ALL_ARGUMENTS

2085 ALL_ASSOCIATIONS

4997 ALL_AUDIT_POLICIES

4005 ALL_BASE_TABLE_MVIEWS

5753 ALL_CAPTURE

5757 ALL_CAPTURE_PARAMETERS

5761 ALL_CAPTURE_PREPARED_DATABASE

5765 ALL_CAPTURE_PREPARED_SCHEMAS

5769 ALL_CAPTURE_PREPARED_TABLES

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=457992 Bytes=42135264)

1 0 VIEW (Cost=864 Card=457992 Bytes=42135264)

2 1 COUNT

32 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)

Statistics

0 recursive calls

0 db block gets

8979 consistent gets

7422 physical reads

0 redo size

758 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 FROM T

8)

9 WHERE ROWNUM = 11

OBJECT_ID OBJECT_NAME

5807 ALL_APPLY_PROGRESS

1769 ALL_ARGUMENTS

2085 ALL_ASSOCIATIONS

4997 ALL_AUDIT_POLICIES

4005 ALL_BASE_TABLE_MVIEWS

5753 ALL_CAPTURE

5757 ALL_CAPTURE_PARAMETERS

5761 ALL_CAPTURE_PREPARED_DATABASE

5765 ALL_CAPTURE_PREPARED_SCHEMAS

5769 ALL_CAPTURE_PREPARED_TABLES

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=20 Bytes=1840)

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

2 1 COUNT (STOPKEY)

32 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)

Statistics

0 recursive calls

0 db block gets

5 consistent gets

0 physical reads

0 redo size

758 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

The execution efficiency of the two is very different, one requires more than 8000 logical reads, while the other only needs 5 logical reads. Looking at the execution plans of the two, we can find that the only difference between the two execution plans is that the second query uses STOPKEY in the COUNT step, that is, Oracle will ROWNUM 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

8)

9 WHERE ROWNUM = 457980

OBJECT_ID OBJECT_NAME

7128 XCF_I_HANDLE_STATUS

7126 XCF_P

7127 XCF_U1

7142 XDF

7145 XDF_I_DF_KEY

7146 XDF_I_HANDLE_STATUS

7143 XDF_P

7144 XDF_U1

TEST.YANGTINGKUN

TEST4.YANGTINGKUN

YANGTK.YANGTINGKUN

11 lines have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=457990 Bytes=42135080)

1 0 VIEW (Cost=864 Card=457990 Bytes=42135080)

2 1 COUNT (STOPKEY)

32 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)

Statistics

0 recursive calls

0 db block gets

8979 consistent gets

7423 physical reads

0 redo size

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

11 rows processed

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.