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

Today, I will talk to you about what the Oracle paging query format refers to. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

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

Generally speaking, there is no special advantage to replace the standard paging function except that SQL nesting can be written one less layer.

However, all the data in the previous test was obtained by a full table scan, so if there is an index on the sort field, what is the efficiency of these two different paging queries? continue the test:

SQL > ALTER TABLE T MODIFY OBJECT_NAME NOT NULL

The table has changed.

SQL > CREATE INDEX IND_T_OBJECT_NAME ON T (OBJECT_NAME)

The index has been created.

In order for Oracle to take advantage of this index, make the index column non-empty, first test the standard paging SQL statement:

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 ORDER BY OBJECT_NAME

9)

10 WHERE ROWNUM = 11

OBJECT_ID OBJECT_NAME

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

Ten rows have been selected.

Time spent: 00: 00: 00.05

Execution Plan

0 SELECT STATEMENT ptimizer=CHOOSE (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=4584838 Bytes=362202202)

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

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

Statistics

0 recursive calls

0 db block gets

15 consistent gets

3 physical reads

0 redo size

578 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 order to use index and NESTED LOOP connection in standard SQL, FIRST_ROWS prompt is generally added. Without adding FIRST_ROWS prompt, Oracle uses full index scan instead of full table scan, and the efficiency is quite high. It only takes half a second to return the result.

Let's look at the performance of the analysis function:

SQL > SELECT OBJECT_ID, OBJECT_NAME

2 FROM

3 (

4 SELECT OBJECT_NAME, OBJECT_ID

5 ROW_NUMBER () OVER (ORDER BY OBJECT_NAME) RN

6 FROM T

7)

8 WHERE RN BETWEEN 11 AND 20

OBJECT_ID OBJECT_NAME

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

Ten rows have been selected.

Time spent: 00: 01: 09.17

Execution Plan

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=826 Card=4584838 Bytes=421805096)

1 0 VIEW (Cost=826 Card=4584838 Bytes=421805096)

2 1 WINDOW (NOSORT) (Cost=826 Card=4584838 Bytes=132960302)

32 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=4584838 Bytes=132960302)

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

Statistics

0 recursive calls

0 db block gets

3197229 consistent gets

118443 physical reads

0 redo size

578 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 OBJECT_NAME, OBJECT_ID

5 ROW_NUMBER () OVER (ORDER BY OBJECT_NAME) RN

6 FROM T

7)

8 WHERE RN BETWEEN 11 AND 20

OBJECT_ID OBJECT_NAME

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

17869 / 1005bd30_LnkdConstant

17870 / 1005bd30_LnkdConstant

Ten rows have been selected.

Time spent: 00: 00: 10.65

Execution Plan

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=826 Card=4584838 Bytes=421805096)

1 0 VIEW (Cost=826 Card=4584838 Bytes=421805096)

2 1 WINDOW (NOSORT) (Cost=826 Card=4584838 Bytes=132960302)

32 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=4584838 Bytes=132960302)

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

Statistics

0 recursive calls

0 db block gets

3197229 consistent gets

43319 physical reads

0 redo size

578 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

If the first execution is due to a large number of physical reads without cache, resulting in an execution time of 1 minute, then the second execution of more than 3 million logical reads, which is still ridiculously high, is very telling. The execution time is more than 10 seconds, which is less efficient than the full table scan. As you can see from the execution plan, this time STOP KEY is not pushed to the window sort of the analysis function, causing Oracle to scan all the records.

This is absolutely unacceptable for paging. But this is a test conducted in a 9i environment:

SQL > SELECT * FROM V$VERSION

BANNER

Oracle9i Enterprise Edition Release 9.2.0.4.0-Production

PL/SQL Release 9.2.0.4.0-Production

CORE 9.2.0.3.0 Production

TNS for Linux: Version 9.2.0.4.0-Production

NLSRTL Version 9.2.0.4.0-Production

See if Oracle in 10g solves this problem:

SQL > SELECT * FROM V$VERSION

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bi

PL/SQL Release 10.2.0.3.0-Production

CORE 10.2.0.3.0 Production

TNS for Linux: Version 10.2.0.3.0-Production

NLSRTL Version 10.2.0.3.0-Production

SQL > SELECT OBJECT_ID, OBJECT_NAME

2 FROM

3 (

4 SELECT OBJECT_NAME, OBJECT_ID

5 ROW_NUMBER () OVER (ORDER BY OBJECT_NAME) RN

6 FROM T

7)

8 WHERE RN BETWEEN 11 AND 20

OBJECT_ID OBJECT_NAME

30166 / 1000e8d1_LinkedHashMapValueIt

30165 / 1000e8d1_LinkedHashMapValueIt

30166 / 1000e8d1_LinkedHashMapValueIt

30165 / 1000e8d1_LinkedHashMapValueIt

30165 / 1000e8d1_LinkedHashMapValueIt

30165 / 1000e8d1_LinkedHashMapValueIt

30165 / 1000e8d1_LinkedHashMapValueIt

30165 / 1000e8d1_LinkedHashMapValueIt

30165 / 1000e8d1_LinkedHashMapValueIt

30165 / 1000e8d1_LinkedHashMapValueIt

10 rows selected.

Elapsed: 00:00:02.04

Execution Plan

Plan hash value: 3047187157

-

| | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 4969K | 436m | | 41652 (1) | 00:09:44 |

| | * 1 | VIEW | | 4969k | 436m | | 41652 (1) | 00:09:44 |

| | * 2 | WINDOW SORT PUSHED RANK | | 4969K | 132m | 342m | 41652 (1) | 00:09:44 |

| | 3 | TABLE ACCESS FULL | T | 4969K | 132m | | 17375 (1) | 00:04:04 |

-

Predicate Information (identified by operation id):

1-filter ("RN" > = 11 AND "RN" = 11 AND "RN"

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.