用等比数列解析伪列level的另一个作用

最新推荐文章于 2025-05-27 11:37:40 发布

原创最新推荐文章于 2025-05-27 11:37:40 发布 · 1.4k 阅读

0 ·

CC 4.0 BY-SA版权

Oracle SQL 开发专栏收录该内容

45 篇文章

订阅专栏

本文详细解释了SQL中Connect by Level的功能与应用。通过实例演示如何使用Level伪列来控制表扫描次数，并介绍了如何计算不同层级下返回的行数及扫描次数。适用于需要理解层级查询的数据库开发者。

我们都知道，level是个伪列，代表当前节点所在的层级；对根节点来说，level返回1；根节点到子节点返回2，以此类推。

借助level，我们可以控制对表的扫描次数。第一次扫描得出的结果集的level都是1,第二次扫描的结果集的level都是2,依此类推。

实验环境：

SQL> create table test as select ename from emp where rownum<=2;

Table created.

SQL> select * from test;

ENAME
----------
SMITH
ALLEN

当level=1时：

SQL> select level,ename from test connect by level=1;

     LEVEL ENAME
---------- ----------
	 1 SMITH
	 1 ALLEN

当level<=2时：

SQL> select level,ename from test connect by level<=2;

     LEVEL ENAME
---------- ----------
	 1 SMITH
	 2 SMITH
	 2 ALLEN
	 1 ALLEN
	 2 SMITH
	 2 ALLEN

6 rows selected.

当level<=3时：

SQL> select level,ename from test connect by level<=3;

     LEVEL ENAME
---------- ----------
	 1 SMITH
	 2 SMITH
	 3 SMITH
	 3 ALLEN
	 2 ALLEN
	 3 SMITH
	 3 ALLEN
	 1 ALLEN
	 2 SMITH
	 3 SMITH
	 3 ALLEN

     LEVEL ENAME
---------- ----------
	 2 ALLEN
	 3 SMITH
	 3 ALLEN

14 rows selected.

当level=1时，进行1次扫描；当level<=2时，第二次进行2次扫描；当level<=3时，第三次进行4次扫描，......，当level<=n时，第n次进行2^(n-1)次方扫描。那么所有的扫描次数总和为：

2^0 + 2^1+ 2^2+...+2^(n-1)

利用等比数列求和公式，可得：

S（n）=2^n - 1

同时，我们还可以对返回行的总数进行计算。

当level=1，扫描1次，返回2行；当level<=2，扫描3次，返回6行；当level<=3时，扫描7次，返回14行；....；当level<=n时，扫描2^n - 1次，返回：

2^1 + 2^2+ 2^3+2^4+...+2^n

利用等比数列求和公式，可得：

T（n）=2^(n+1)-2

得到到返回行的总数，我们可以用（返回行到总数）/（逻辑读）用来大概判断sql语句是否需要优化。