Rownum about a small test

Apr 2 posted in Oracle (RSS), 88 views.

1. Problem Description

Colleagues have a test today, rownum, found a small problem with my understanding of the past not the same. I have always thought that rownum is a tag, is the order of query result set to the tag. So should be the first implementation of the rownum NOT SQL, and then apply the results rownum filter set. Such as the following:

SQL> select * from v $ version;

BANNER

-------------------------------------------------- --------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL / SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL> create table test_rownum as select mod (rownum, 4) a from dual connect by rownum <11;

Table created

SQL> select * from test_rownum;

A

----------

1

2

3

0

1

2

3

0

1

2

10 rows selected

SQL> select rownum rn1, x. * from test_rownum x;

RN1 A

---------- ----------

1 1

2 2

3 3

4 0

5 1

6 2

7 3

8 0

9 1

10 2

10 rows selected

SQL> select rownum rn2, y. * from (select rownum rn1, x. * from test_rownum x) y;

RN2 RN1 A

---------- ---------- ----------

1 1 1

2 2 2

3 3 3

4 4 0

5 5 1

6 6 2

7 7 3

8 8 0

9 9 1

10,102

10 rows selected

QL> select rownum rn2, y. * from (select rownum rn1, x. * from test_rownum x) y where mod (rn1, 2) = 0;

RN2 RN1 A

---------- ---------- ----------

1 2 2

2 4 0

3 6 2

4 8 0

5 10 2

But distinct, it is the first implementation of rownum, and then the implementation of the distinct. The following are test results:

SQL> select distinct a from test_rownum;

A

----------

1

2

3

0

SQL> select distinct a from test_rownum where rownum <2;

A

----------

1

SQL> select distinct a from test_rownum where rownum <3;

A

----------

1

2

SQL> select distinct a from test_rownum where rownum <4;

A

----------

1

2

3

SQL> select distinct a from test_rownum where rownum <5;

A

----------

1

2

3

0

2. From the implementation plan on an analysis of

Just now see is the suspicion of the problem situation, the following implementation plan from the point of view about:

(1) ordinary SQL: There is only one full table scan.

SQL> explain plan FOR

2 SELECT x. * from test_rownum x;

Explained

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT

-------------------------------------------------- ------------------------------

Plan hash value: 2707342976

-------------------------------------------------- ------------------------------

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

-------------------------------------------------- ------------------------------

| 0 | SELECT STATEMENT | | 10 | 130 | 3 (0) | 00:00:01

| 1 | TABLE ACCESS FULL | TEST_ROWNUM | 10 | 130 | 3 (0) | 00:00:01

-------------------------------------------------- ------------------------------

Note

-----

- Dynamic sampling used for this statement

12 rows selected

(2) query results, showing rownum: the implementation of the plan at this time a count count many operation

SQL> explain plan FOR

2 select rownum rn1, x. * from test_rownum x;

Explained

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT

-------------------------------------------------- ------------------------------

Plan hash value: 2770385711

-------------------------------------------------- ------------------------------

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

-------------------------------------------------- ------------------------------

| 0 | SELECT STATEMENT | | 10 | 130 | 3 (0) | 00:00:01

| 1 | COUNT | | | | |

| 2 | TABLE ACCESS FULL | TEST_ROWNUM | 10 | 130 | 3 (0) | 00:00:01

-------------------------------------------------- ------------------------------

Note

-----

- Dynamic sampling used for this statement

13 rows selected

(3) a predicate many rownum <3: At this point count count operation, the addition of a termination key (stopkey), that is, queries to the rownum = 3 are not satisfied with the conditions found, the data will no longer be behind the investigation.

SQL> explain plan FOR

2 select rownum rn1, x. * from test_rownum x WHERE ROWNUM <3;

Explained

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT

-------------------------------------------------- ------------------------------

Plan hash value: 2968155833

-------------------------------------------------- ------------------------------

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

-------------------------------------------------- ------------------------------

| 0 | SELECT STATEMENT | | 2 | 26 | 2 (0) | 00:00:01

| * 1 | COUNT STOPKEY | | | | |

| 2 | TABLE ACCESS FULL | TEST_ROWNUM | 10 | 130 | 2 (0) | 00:00:01

-------------------------------------------------- ------------------------------

Predicate Information (identified by operation id):

-------------------------------------------------- --

1 - filter (ROWNUM <3)

Note

-----

- Dynamic sampling used for this statement

18 rows selected

(4) the implementation of this rownum more clear. The following two rownum continue with our test:

- Both inside and outside the SQL layer of a multi-count operation count, where the predicate rn1 <4 was used as a filter.

SQL> explain plan FOR

2 select rownum rn2, y. * from (select rownum rn1, x. * from test_rownum x) y where rn1 <4;

Explained

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT

-------------------------------------------------- ------------------------------

Plan hash value: 3737182419

-------------------------------------------------- ------------------------------

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

-------------------------------------------------- ------------------------------

| 0 | SELECT STATEMENT | | 10 | 260 | 3 (0) | 00:00:

| 1 | COUNT | | | | |

| * 2 | VIEW | | 10 | 260 | 3 (0) | 00:00:

| 3 | COUNT | | | | |

| 4 | TABLE ACCESS FULL | TEST_ROWNUM | 10 | 130 | 3 (0) | 00:00:

-------------------------------------------------- ------------------------------

Predicate Information (identified by operation id):

-------------------------------------------------- --

2 - filter ( "RN1" <4)

Note

-----

- Dynamic sampling used for this statement

20 rows selected

- Predicate into the SQL outer rownum: the implementation of plans to the expected.

SQL> explain plan FOR

2 select rownum rn2, y. * from (select rownum rn1, x. * from test_rownum x) y where ROWNUM <4;

Explained

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT

-------------------------------------------------- ------------------------------

Plan hash value: 1119532285

-------------------------------------------------- ------------------------------

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

-------------------------------------------------- ------------------------------

| 0 | SELECT STATEMENT | | 3 | 78 | 2 (0) | 00:00:

| * 1 | COUNT STOPKEY | | | | |

| 2 | VIEW | | 10 | 260 | 2 (0) | 00:00:

| 3 | COUNT | | | | |

| 4 | TABLE ACCESS FULL | TEST_ROWNUM | 10 | 130 | 2 (0) | 00:00:

-------------------------------------------------- ------------------------------

Predicate Information (identified by operation id):

-------------------------------------------------- --

1 - filter (ROWNUM <4)

Note

-----

- Dynamic sampling used for this statement

20 rows selected

(5) take a look at the implementation of distinct programs: the implementation of a termination of the first key (stopkey) count of the count, and then the implementation of the distinct.

SQL> explain plan FOR

2 select distinct a from test_rownum where rownum <3;

Explained

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT

-------------------------------------------------- ------------------------------

Plan hash value: 1179436725

-------------------------------------------------- ------------------------------

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

-------------------------------------------------- ------------------------------

| 0 | SELECT STATEMENT | | 2 | 26 | 4 (25) | 00:00:0

| 1 | HASH UNIQUE | | 2 | 26 | 4 (25) | 00:00:0

| * 2 | COUNT STOPKEY | | | | |

| 3 | TABLE ACCESS FULL | TEST_ROWNUM | 10 | 130 | 3 (0) | 00:00:0

-------------------------------------------------- ------------------------------

Predicate Information (identified by operation id):

-------------------------------------------------- --

2 - filter (ROWNUM <3)

Note

-----

- Dynamic sampling used for this statement

19 rows selected

3. Polymerization function test other

Possible for the Aggregation function to speculate, rownum is the case: rownum priority class than the aggregate function. Thus polymerization of some commonly used test functions and found that indeed the case:

- 1. Count

SQL> explain plan FOR

2 select COUNT (*) from test_rownum where rownum <3;

Explained

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT

-------------------------------------------------- ------------------------------

Plan hash value: 3018287901

-------------------------------------------------- -------------------------

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

-------------------------------------------------- -------------------------

| 0 | SELECT STATEMENT | | 1 | 3 (0) | 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| * 2 | COUNT STOPKEY | | | | |

| 3 | TABLE ACCESS FULL | TEST_ROWNUM | 10 | 3 (0) | 00:00:01 |

-------------------------------------------------- -------------------------

Predicate Information (identified by operation id):

-------------------------------------------------- --

2 - filter (ROWNUM <3)

Note

-----

- Dynamic sampling used for this statement

19 rows selected

SQL> alter table test_rownum add b number;

Table altered

SQL> update test_rownum set b = rownum;

10 rows updated

SQL> commit;

Commit complete

SQL> select * from test_rownum;

AB

---------- ----------

1 1

2 2

3 3

0 4

1 5

2 6

3 7

0 8

1 9

2 10

10 rows selected

SQL> explain plan FOR

2 select a, COUNT (b) from test_rownum where rownum <3 GROUP BY a;

Explained

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT

-------------------------------------------------- ------------------------------

Plan hash value: 3427750650

-------------------------------------------------- ------------------------------

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

-------------------------------------------------- ------------------------------

| 0 | SELECT STATEMENT | | 2 | 52 | 4 (25) | 00:00:0

| 1 | HASH GROUP BY | | 2 | 52 | 4 (25) | 00:00:0

| * 2 | COUNT STOPKEY | | | | |

| 3 | TABLE ACCESS FULL | TEST_ROWNUM | 10 | 260 | 3 (0) | 00:00:0

-------------------------------------------------- ------------------------------

Predicate Information (identified by operation id):

-------------------------------------------------- --

2 - filter (ROWNUM <3)

Note

-----

- Dynamic sampling used for this statement

19 rows selected

- 2. Avg

SQL> explain plan FOR

2 select AVG (a) from test_rownum where rownum <3;

Explained

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT

-------------------------------------------------- ------------------------------

Plan hash value: 3018287901

-------------------------------------------------- ------------------------------

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

-------------------------------------------------- ------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0) | 00:00:0

| 1 | SORT AGGREGATE | | 1 | 13 | |

| * 2 | COUNT STOPKEY | | | | |

| 3 | TABLE ACCESS FULL | TEST_ROWNUM | 10 | 130 | 3 (0) | 00:00:0

-------------------------------------------------- ------------------------------

Predicate Information (identified by operation id):

-------------------------------------------------- --

2 - filter (ROWNUM <3)

Note

-----

- Dynamic sampling used for this statement

19 rows selected

- 3. Max

SQL> explain plan FOR

2 select MAX (a) from test_rownum where rownum <3;

Explained

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT

-------------------------------------------------- ------------------------------

Plan hash value: 3018287901

-------------------------------------------------- ------------------------------

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

-------------------------------------------------- ------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0) | 00:00:0

| 1 | SORT AGGREGATE | | 1 | 13 | |

| * 2 | COUNT STOPKEY | | | | |

| 3 | TABLE ACCESS FULL | TEST_ROWNUM | 10 | 130 | 3 (0) | 00:00:0

-------------------------------------------------- ------------------------------

Predicate Information (identified by operation id):

-------------------------------------------------- --

2 - filter (ROWNUM <3)

Note

-----

- Dynamic sampling used for this statement

19 rows selected

- 4. Min

SQL> explain plan FOR

2 select MIN (a) from test_rownum where rownum <3;

Explained

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT

-------------------------------------------------- ------------------------------

Plan hash value: 3018287901

-------------------------------------------------- ------------------------------

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

-------------------------------------------------- ------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0) | 00:00:0

| 1 | SORT AGGREGATE | | 1 | 13 | |

| * 2 | COUNT STOPKEY | | | | |

| 3 | TABLE ACCESS FULL | TEST_ROWNUM | 10 | 130 | 3 (0) | 00:00:0

-------------------------------------------------- ------------------------------

Predicate Information (identified by operation id):

-------------------------------------------------- --

2 - filter (ROWNUM <3)

Note

-----

- Dynamic sampling used for this statement

19 rows selected

- 5. Sum

SQL> explain plan FOR

2 select SUM (a) from test_rownum where rownum <3;

Explained

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT

-------------------------------------------------- ------------------------------

Plan hash value: 3018287901

-------------------------------------------------- ------------------------------

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

-------------------------------------------------- ------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0) | 00:00:0

| 1 | SORT AGGREGATE | | 1 | 13 | |

| * 2 | COUNT STOPKEY | | | | |

| 3 | TABLE ACCESS FULL | TEST_ROWNUM | 10 | 130 | 3 (0) | 00:00:0

-------------------------------------------------- ------------------------------

Predicate Information (identified by operation id):

-------------------------------------------------- --

2 - filter (ROWNUM <3)

Note

-----

- Dynamic sampling used for this statement

19 rows selected

4. Conclusion

If the SQL at the same level of existence and convergence function rownum, rownum filter then run, then the application of aggregate functions on the group operating result set to be new results.