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 format (1): http://yangtingkun.itpub.net/post/468/100278

Oracle paging query format (2): http://yangtingkun.itpub.net/post/468/101703

Oracle paging query format (3): http://yangtingkun.itpub.net/post/468/104595

Oracle paging query format (4): http://yangtingkun.itpub.net/post/468/104867

Oracle paging query format (5): http://yangtingkun.itpub.net/post/468/107934

Oracle paging query format (6): http://yangtingkun.itpub.net/post/468/108677

Oracle paging query format (7): http://yangtingkun.itpub.net/post/468/109834

Oracle paging query format (8): http://yangtingkun.itpub.net/post/468/224557

Oracle paging query format (9): http://yangtingkun.itpub.net/post/468/224409

GROUP BY STOPKEY, a new feature of Oracle10g, enables Oracle10g to solve the problem that GROUP BY operations cannot be paged as mentioned in the previous article.

Before 10g, the GROUP BY operation of Oracle must be fully performed before the result can be returned to the user. However, Oracle10g adds a GROUP BY STOPKEY execution path, which allows users to abort running operations at any time according to STOPKEY when performing GROUP BY operations.

This makes the standard paging function play a role again for GROUP BY operations.

SQL > CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A

The table has been created.

SQL > CREATE INDEX IND_T_CREATED ON T (CREATED)

The index has been created.

SQL > ALTER TABLE T MODIFY CREATED NOT NULL

The table has changed.

SQL > ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'

The session has changed.

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

The PL/SQL process completed successfully.

SQL > SET AUTOT ON

SQL > SET TIMING ON

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT CREATED, COUNT (*)

8 FROM T

9 GROUP BY CREATED

10) A

11 WHERE ROWNUM

< 20 12 ) 13 WHERE RN >

= 10

RN CREATED COUNT (*)

10 2005-12-19 17:07:57 50

11 2005-12-19 17:07:58 36

12 2005-12-19 17:08:24 10

13 2005-12-19 17:08:25 49

14 2005-12-19 17:08:26 66

15 2005-12-19 17:08:27 62

16 2005-12-19 17:08:28 81

17 2005-12-19 17:08:29 82

18 2005-12-19 17:08:33 1

19 2005-12-19 17:08:35 3

Ten rows have been selected.

Time spent: 00: 00: 00.04

Carry out the plan

Plan hash value: 3639065582

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

| | 0 | SELECT STATEMENT | | 19 | 665 | 1 (0) |

| | * 1 | VIEW | | 19 | 665 | 1 (0) |

| | * 2 | COUNT STOPKEY |

| | 3 | VIEW | | 973 | 21406 | 1 (0) |

| | * 4 | SORT GROUP BY STOPKEY | | 973 | 7784 | 1 (0) |

| | 5 | INDEX FULL SCAN | IND_T_CREATED | 984 | 7872 | 1 (0) |

Predicate Information (identified by operation id):

1-filter ("RN" > = 10)

2-filter (ROWNUM= 10

13 AND RN

< 20; RN CREATED COUNT(*) ---------- ------------------- ---------- 10 2005-12-19 17:09:27 34 11 2005-12-19 17:09:31 29 12 2005-12-19 17:09:40 29 13 2005-12-19 17:09:58 11 14 2005-12-19 17:10:06 6 15 2005-12-19 17:10:12 48 16 2005-12-19 17:10:20 24 17 2005-12-19 17:10:37 8 18 2005-12-19 17:10:40 2 19 2005-12-19 17:10:49 2 已选择10行。 已用时间: 00: 00: 00.06 执行计划 ---------------------------------------------------------- Plan hash value: 4036621539 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 973 | 34055 | 14 (36)| |* 1 | VIEW | | 973 | 34055 | 14 (36)| | 2 | COUNT | | | | | | 3 | VIEW | | 973 | 21406 | 14 (36)| | 4 | HASH GROUP BY | | 973 | 7784 | 14 (36)| | 5 | INDEX FAST FULL SCAN| IND_T_CREATED | 50359 | 393K| 10 (10)| ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">

= 10 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.