Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use index hints to reduce the number of nesting layers of pagination in the database". In the daily operation, I believe that many people have doubts about how to use index hints to reduce the number of nesting layers of pagination in the database. The editor consulted all kinds of data and sorted out simple and useful operation methods. I hope it will be helpful for you to answer the question of "how to use index hints to reduce the number of nesting layers of paging in the database"! Next, please follow the editor to study!

First of all, it should be emphasized that this is not a standard or recommended way of writing paging statements, which requires a clear understanding of the structure of tables and indexes. And there are many restrictions in this method. Therefore, it is just a separate discussion here, and there is no need to put it in the paging project.

Here is a comparison between the standard paging method and the way you use HINT:

SQL > CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2 (30) NOT NULL)

The table has been created.

SQL > INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS

50418 rows were created.

SQL > CREATE INDEX IND_T_NAME ON T (NAME)

The index has been created.

SQL > SET AUTOT ON

SQL > SET AUTOT ON EXP

SQL > SELECT *

2 FROM

3 (

4 SELECT A.C., ROWNUM RN

5 FROM

6 (

7 SELECT * FROM T ORDER BY NAME

8) A

9 WHERE ROWNUM 10

ID NAME RN

11501 / 1023e902_OraCharsetUTFE 11

11502 / 1023e902_OraCharsetUTFE 12

46027 / 10240eba_GenPropertySequence 13

46145 / 10240eba_GenPropertySequence 14

43203 / 1025308f_SunTileScheduler 15

44344 / 1025308f_SunTileScheduler 16

37617 / 10297c91_SAXAttrList 17

38208 / 10297c91_SAXAttrList 18

24613 / 103a2e73_DefaultEditorKitEndP 19

24614 / 103a2e73_DefaultEditorKitEndP 20

Ten rows have been selected.

Carry out the plan

Plan hash value: 3635692127

-

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

-

| | 0 | SELECT STATEMENT | | 20 | 860 | 4 (0) | 00:00:01 |

| | * 1 | VIEW | | 20 | 860 | 4 (0) | 00:00:01 |

| | * 2 | COUNT STOPKEY | | |

| | 3 | VIEW | | 45221 | 1324K | 4 (0) | 00:00:01 |

| | 4 | TABLE ACCESS BY INDEX ROWID | T | 45221 | 1324K | 4 (0) | 00:00:01 |

| | 5 | INDEX FULL SCAN | IND_T_NAME | 21 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

1-filter ("RN" > 10)

2-filter (ROWNUM SELECT *

2 FROM

3 (

4 SELECT / * + INDEX (T IND_T_NAME) * / T.Christ, ROWNUM RN

5 FROM T

6 WHERE ROWNUM 10

ID NAME RN

11501 / 1023e902_OraCharsetUTFE 11

11502 / 1023e902_OraCharsetUTFE 12

46027 / 10240eba_GenPropertySequence 13

46145 / 10240eba_GenPropertySequence 14

43203 / 1025308f_SunTileScheduler 15

44344 / 1025308f_SunTileScheduler 16

37617 / 10297c91_SAXAttrList 17

38208 / 10297c91_SAXAttrList 18

24613 / 103a2e73_DefaultEditorKitEndP 19

24614 / 103a2e73_DefaultEditorKitEndP 20

Ten rows have been selected.

Carry out the plan

Plan hash value: 2512188149

-

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

-

| | 0 | SELECT STATEMENT | | 20 | 860 | 4 (0) | 00:00:01 |

| | * 1 | VIEW | | 20 | 860 | 4 (0) | 00:00:01 |

| | * 2 | COUNT STOPKEY | | |

| | 3 | TABLE ACCESS BY INDEX ROWID | T | 45221 | 1324K | 4 (0) | 00:00:01 |

| | 4 | INDEX FULL SCAN | IND_T_NAME | 45221 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

1-filter ("RN" > 10)

2-filter (ROWNUM

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.