Oracle и connect by level

Сегодня столкнулся с интересным поведением connect by level на Oracle 9 и 10XE

SQL> SELECT BANNER FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL> SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 10;

LEVEL
----------
1

SQL> SELECT COUNT(LEVEL) FROM DUAL CONNECT BY LEVEL < 10;

COUNT(LEVEL)
------------
9

SQL> SELECT MAX(LEVEL) FROM DUAL CONNECT BY LEVEL < 10;

MAX(LEVEL)
----------
9

SQL> SELECT *
2 FROM (
3 SELECT LEVEL FROM DUAL CONNECT BY LEVEL <10
4 );

LEVEL
----------
1
2
3
4
5
6
7
8
9

9 rows selected.

SQL> explain plan for select * from (select level from dual connect by level<10);

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | VIEW | | | | |
| 2 | CONNECT BY WITHOUT FILTERING| | | | |
| 3 | TABLE ACCESS FULL | DUAL | | | |
-----------------------------------------------------------------------------

Note: rule based optimization

11 rows selected.

Продолжаем эксперимент на 10XE.

SQL> SELECT BANNER FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
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> SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 10;

LEVEL
----------
1
2
3
4
5
6
7
8
9

9 rows selected.

SQL> SELECT COUNT(LEVEL) FROM DUAL CONNECT BY LEVEL < 10;

COUNT(LEVEL)
------------
9

SQL> SELECT MAX(LEVEL) FROM DUAL CONNECT BY LEVEL < 10;

MAX(LEVEL)
----------
9

SQL> SELECT * FROM (SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 10);

LEVEL
----------
1
2
3
4
5
6
7
8
9

9 rows selected.

SQL> explain plan for select level from dual connect by level < 10;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1236776825

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | CONNECT BY WITHOUT FILTERING| | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------

9 rows selected.