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

This article introduces what the Oracle paging query format is like, the content is very detailed, interested friends can refer to, hope to be helpful to you.

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

This article introduces the help of HASH SORT CLUSTER tables for paging queries.

A few days ago, a netizen asked why most of the examples in this series did not include query conditions.

In fact, paging is only a standard way of writing, paging nested business SQL is the key to achieve the query function, and this part may be very simple, or it may be very complicated. Therefore, it is difficult to simulate the performance of various complex business SQL in paging. If you want to analyze paging queries, you can only classify different business SQL features, such as: including GROUP BY operations, including UNION ALL queries, obtaining records through full table scanning, obtaining records through index scanning, and so on.

And to assume some premises, since we want to discuss the efficiency of paging, then the paging SQL will generally return a large amount of data or return an unknown number of records. If the business SQL only returns less than 10 records, then there is no need for paging at all; if the business SQL itself can get the results within seconds, then you don't have to worry too much about the performance after paging.

So the previous discussion is actually about the performance benefits of paging when a large amount of data is returned.

As mentioned by netizens, most SQL do not contain query conditions, in fact, this is the result of simplified SQL. If the query conditions of SQL can quickly locate the results and return a small amount of data, then the situation itself is very efficient, and the use of paging will not lead to further performance improvement; if the index returns a large amount of data, or because the amount of data is too large to use the index at all, instead of using a full table scan, then this situation can be simulated by a full table scan of a single table.

However, Oracle does have a table structure that is very suitable for sorting pages that meet the criteria of an index query, which is the HASH sorted clustering table:

SQL > CREATE CLUSTER C_HASH_SORT

2 (ID NUMBER, CREATED DATE SORT)

3 HASHKEYS 100000 SIZE 1125

Cluster created.

SQL > CREATE TABLE T_HASH_SORT

2 (ID NUMBER

3 OWNER VARCHAR2 (30)

4 OBJECT_NAME VARCHAR2 (30)

5 OBJECT_TYPE VARCHAR2 (30)

6 CREATED DATE SORT)

7 CLUSTER C_HASH_SORT (ID, CREATED)

Table created.

SQL > CREATE TABLE T_NORMAL

2 (ID NUMBER

3 OWNER VARCHAR2 (30)

4 OBJECT_NAME VARCHAR2 (30)

5 OBJECT_TYPE VARCHAR2 (30)

6 CREATED DATE)

Table created.

SQL > INSERT INTO T_HASH_SORT

2 SELECT *

3 FROM

4 (

5 SELECT MOD (ROWNUM, 100000) ID

6 A.OWNER

7 OBJECT_NAME

8 OBJECT_TYPE

9 A.CREATED

10 FROM DBA_OBJECTS A, DBA_DB_LINKS

11)

12 ORDER BY ID, CREATED

2476775 rows created.

SQL > COMMIT

Commit complete.

SQL > INSERT INTO T_NORMAL

2 SELECT *

3 FROM

4 (

5 SELECT MOD (ROWNUM, 100000) ID

6 A.OWNER

7 OBJECT_NAME

8 OBJECT_TYPE

9 A.CREATED

10 FROM DBA_OBJECTS A, DBA_DB_LINKS

11)

12 ORDER BY ID, CREATED

2476775 rows created.

SQL > COMMIT

Commit complete.

SQL > SET TIMING ON

SQL > SET AUTOT ON

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID, OWNER, OBJECT_TYPE, CREATED

8 FROM T_NORMAL

9 WHERE ID = 11232

10 ORDER BY CREATED

11) A

12 WHERE ROWNUM 10

RN ID OWNER OBJECT_TYPE CREATED

11 11232 SYS JAVA CLASS 11-June-08

12 11232 SYS JAVA CLASS 11-June-08

13 11232 PUBLIC SYNONYM 11-June-08

14 11232 SYS JAVA CLASS 11-June-08

15 11232 CTXSYS TABLE 11-June-08

16 11232 ORDSYS JAVA RESOURCE 11-June-08

17 11232 MDSYS PACKAGE BODY 11-June-08

18 11232 PUBLIC SYNONYM 11-June-08

19 11232 PUBLIC SYNONYM November-June-08

20 11232 JIANGSU15 INDEX December-June-08

10 rows selected.

Elapsed: 00:00:00.11

Execution Plan

Plan hash value: 1455441750

-

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

-

| | 0 | SELECT STATEMENT | | 20 | 1380 | 3213 (1) | 00:00:45 |

| | * 1 | VIEW | | 20 | 1380 | 3213 (1) | 00:00:45 |

| | * 2 | COUNT STOPKEY | | |

| | 3 | VIEW | | 106 | 5936 | 3213 (1) | 00:00:45 |

| | * 4 | SORT ORDER BY STOPKEY | | 106 | 5936 | 3213 (1) | 00:00:45 |

| | * 5 | TABLE ACCESS FULL | T_NORMAL | 106 | 5936 | 3212 (1) | 00:00:45 |

-

Predicate Information (identified by operation id):

1-filter ("RN" > 10)

2-filter (ROWNUM10)

2-filter (ROWNUM CREATE INDEX IND_T_NORMAL ON T_NORMAL (ID, CREATED)

Index created.

Elapsed: 00:00:04.18

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID, OWNER, OBJECT_TYPE, CREATED

8 FROM T_NORMAL

9 WHERE ID = 11232

10 ORDER BY CREATED

11) A

12 WHERE ROWNUM 10

RN ID OWNER OBJECT_TYPE CREATED

11 11232 SYS JAVA CLASS 11-June-08

12 11232 SYS JAVA CLASS 11-June-08

13 11232 PUBLIC SYNONYM 11-June-08

14 11232 SYS JAVA CLASS November-June-08

15 11232 CTXSYS TABLE 11-June-08

16 11232 ORDSYS JAVA RESOURCE 11-June-08

17 11232 MDSYS PACKAGE BODY 11-June-08

18 11232 PUBLIC SYNONYM 11-June-08

19 11232 PUBLIC SYNONYM November-June-08

20 11232 JIANGSU15 INDEX December-June-08

10 rows selected.

Elapsed: 00:00:00.01

Execution Plan

Plan hash value: 1590327436

-

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

-

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

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

| | * 2 | COUNT STOPKEY | | |

| | 3 | VIEW | | 25 | 1400 | 4 (0) | 00:00:01 |

| | 4 | TABLE ACCESS BY INDEX ROWID | T_NORMAL | 25 | 1400 | 4 (0) | 00:00:01 |

| | * 5 | INDEX RANGE SCAN | IND_T_NORMAL | 25 | | 3 (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.