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

This article introduces the relevant knowledge of "what is the method of Oracle paging query". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

As mentioned earlier, it doesn't make much sense to use standard paging functions for set operations and aggregation operations, and the following articles focus on set operations and aggregation operations. First of all, the set operation is discussed here.

When a query needs to be paged, sorting operations are included in most cases, because in the absence of sorting operations, it is difficult to ensure that the data returned by paging is continuous and not duplicated.

So only cases that include sorting are considered here:

SQL > CREATE TABLE T AS SELECT * FROM DBA_TABLES

The table has been created.

SQL > CREATE TABLE T1 AS SELECT * FROM DBA_INDEXES

The table has been created.

SQL > CREATE INDEX IND_T_OWNER ON T (OWNER)

The index has been created.

SQL > CREATE INDEX IND_T1_OWNER ON T1 (OWNER)

The index has been created.

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

The PL/SQL process completed successfully.

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

The PL/SQL process completed successfully.

SQL > SET AUTOT ON

SQL > SELECT / * + FIRST_ROWS * / OWNER, NAME

2 FROM

3 (

4 SELECT ROWNUM RN, OWNER, NAME

5 FROM

6 (

7 SELECT OWNER, TABLE_NAME NAME

8 FROM T

9 UNION ALL

10 SELECT OWNER, INDEX_NAME NAME

11 FROM T1

12 ORDER BY OWNER

13)

14 WHERE ROWNUM 10

OWNER NAME

CTXSYS DR$SECTION_GROUP

CTXSYS DR$THS_BT

CTXSYS DR$THS_FPHRASE

CTXSYS DR$THS_PHRASE

CTXSYS DR$THS

CTXSYS DR$SQE

CTXSYS SYS_IOT_OVER_26472

CTXSYS DR$INDEX_OBJECT

CTXSYS DR$POLICY_TAB

CTXSYS DR$INDEX_PARTITION

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=20 Bytes=940)

1 0 VIEW (Cost=17 Card=20 Bytes=940)

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=17 Card=2877 Bytes=97818)

4 3 SORT (ORDER BY STOPKEY) (Cost=7 Card=2877 Bytes=76522)

5 4 UNION-ALL

6 5 TABLE ACCESS (FULL) OF 'T' (Cost=3 Card=1157 Bytes=30082)

7 5 TABLE ACCESS (FULL) OF 'T1' (Cost=4 Card=1720 Bytes=46440)

Statistics

0 recursive calls

0 db block gets

44 consistent gets

0 physical reads

0 redo size

639 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

10 rows processed

SQL > SELECT / * + FIRST_ROWS * / OWNER, NAME

2 FROM

3 (

4 SELECT ROWNUM RN, OWNER, NAME

5 FROM

6 (

7 SELECT OWNER, TABLE_NAME NAME

8 FROM T

9 UNION ALL

10 SELECT OWNER, INDEX_NAME NAME

11 FROM T1

12 ORDER BY OWNER

13)

14)

15 WHERE RN > 10 AND RN SELECT / * + FIRST_ROWS * / OWNER, NAME

2 FROM

3 (

4 SELECT ROWNUM RN, OWNER, NAME

5 FROM

6 (

7 SELECT OWNER, NAME

8 FROM

9 (

10 SELECT OWNER, TABLE_NAME NAME

11 FROM T

12 ORDER BY OWNER

13)

14 WHERE 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.