比较的版本和配置
Oracle 19c
docker pull registry.cn-hangzhou.aliyuncs.com/zhuyijun/oracle:19c SQL> SELECT BANNER_FULL FROM V$VERSION; BANNER_FULL -------------------------------------------------------------------------------- Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> show sga; Total System Global Area 3137338784 bytes Fixed Size 9141664 bytes Variable Size 654311424 bytes Database Buffers 2466250752 bytes Redo Buffers 7634944 bytes达梦
wget http://download.dameng.com/eco/dm8/dm8_20250206_x86_rh6_rq_single.tar docker load -i ./dm8_20250206_x86_rh6_rq_single.tar SQL> SELECT * FROM v$version; LINEID BANNER ---------- --------------------------------- 1 DM Database Server 64 V8 2 DB Version: 0x7000d 3 03134284336-20250117-257733-20132 4 Msg Version: 32 5 Gsu level(5) cnt: 01.connect by语句
Oracle：
SQL> col a for 999999999999999 SQL> select sum(level) a from dual connect by level<=1e6; A ---------------- 500000500000 Elapsed: 00:00:00.22 SQL> select sum(i)a,substr(i,1,1) from(select level i from dual connect by level<=1e6)group by substr(i,1,1); A SUBS ---------------- ---- 85858530303 8 25252469697 2 55555500000 5 65656510101 6 75757520202 7 15152459596 1 35353479798 3 45454489899 4 95959540404 9 9 rows selected. Elapsed: 00:00:00.24 SQL> select sum(level) a from dual connect by level<=1e7; select sum(level) a from dual connect by level<=1e7 * ERROR at line 1: ORA-30009: Not enough memory for CONNECT BY operation Elapsed: 00:00:00.29 SQL> select sum(level) a from dual connect by level<=3e6; select sum(level) a from dual connect by level<=3e6 * ERROR at line 1: ORA-30009: Not enough memory for CONNECT BY operation Elapsed: 00:00:00.22 SQL> select sum(level) a from dual connect by level<=2e6; A ---------------- 2000001000000 Elapsed: 00:00:00.25 SQL> select sum(i)a,substr(i,1,1) from(select level i from dual connect by level<=2e6)group by substr(i,1,1); A SUBS ---------------- ---- 85858530303 8 25254469697 2 55555500000 5 65656510101 6 75757520202 7 1515150959596 1 35353479798 3 45454489899 4 95959540404 9 9 rows selected. Elapsed: 00:00:00.48可能是内存设置问题，千万行、三百万行connect by level均报内存不足。
百万行简单加总220毫秒，按数字第一个字符分组汇总240毫秒, 二百万行简单加总240毫秒，按数字第一个字符分组汇总480毫秒。
达梦：
SQL> select sum(level) from dual connect by level<=1e6; LINEID SUM(LEVEL) ---------- -------------------- 1 500000500000 used time: 47.203(ms). Execute id is 1817. SQL> select sum(i)a,substr(i,1,1) from(select level i from dual connect by level<=1e6)group by substr(i,1,1); LINEID A SUBSTR(I,1,1) ---------- -------------------- ------------- 1 15152459596 1 2 25252469697 2 3 35353479798 3 4 45454489899 4 5 55555500000 5 6 65656510101 6 7 75757520202 7 8 85858530303 8 9 95959540404 9 9 rows got used time: 114.917(ms). Execute id is 805. SQL> select sum(level) a from dual connect by level<=2e6; LINEID A ---------- -------------------- 1 2000001000000 used time: 121.851(ms). Execute id is 807. SQL> select sum(i)a,substr(i,1,1) from(select level i from dual connect by level<=2e6)group by substr(i,1,1); LINEID A SUBSTR(I,1,1) ---------- -------------------- ------------- 1 1515150959596 1 2 25254469697 2 3 35353479798 3 4 45454489899 4 5 55555500000 5 6 65656510101 6 7 75757520202 7 8 85858530303 8 9 95959540404 9 9 rows got used time: 205.320(ms). Execute id is 806. SQL> select sum(level) from dual connect by level<=1e7; LINEID SUM(LEVEL) ---------- -------------------- 1 50000005000000 used time: 432.414(ms). Execute id is 1818. SQL> select sum(i),substr(i,1,1) from(select level i from dual connect by level<=1e7)group by substr(i,1,1); LINEID SUM(I) SUBSTR(I,1,1) ---------- -------------------- ------------- 1 1515160959596 1 2 2525251969697 2 3 3535352979798 3 4 4545453989899 4 5 5555555000000 5 6 6565656010101 6 7 7575757020202 7 8 8585858030303 8 9 9595959040404 9 used time: 845.997(ms). Execute id is 1819.百万行简单加总47毫秒，按数字第一个字符分组汇总115毫秒, 二百万行简单加总122毫秒，按数字第一个字符分组汇总205毫秒。千万行简单加总432毫秒，按数字第一个字符分组汇总846毫秒。
按比例推算Oracle比达梦慢一半。
2.with function功能
Oracle
SQL> WITH FUNCTION f1(C INT) RETURN INT AS BEGIN RETURN C* 10; END; 2 SELECT f1(5236) FROM DUAL; 3 / F1(5236) ---------- 52360 计算阶乘 SQL> WITH FUNCTION fac(C INT) RETURN INT AS BEGIN RETURN case when C=1 then 1 else C* fac(C-1) end; END; SELECT fac(22)f FROM DUAL; / F -------------------------- 1124000727777607680000 Elapsed: 00:00:00.01 计算斐波那契数 SQL> WITH FUNCTION fib(C INT) RETURN INT AS BEGIN RETURN case when C=1 or C=2 then 1 else fib(C-1)+ fib(C-2) end; END; SELECT fib(35)f FROM DUAL; / F -------------------------- 9227465 Elapsed: 00:00:01.76基本的函数，递归调用的函数都可以执行成功
达梦
SQL> WITH FUNCTION f1(C INT) RETURN INT AS BEGIN RETURN C* 10; END; 2 SELECT f1(5236) FROM DUAL; 3 / LINEID F1(5236) ---------- ----------- 1 52360 used time: 2.382(ms). Execute id is 66303. WITH FUNCTION fac(C INT) RETURN INT AS BEGIN RETURN case when C=1 then 1 else C* fac(C-1) end; END; SELECT fac(22)f FROM DUAL; [-7057]:Too many nested level -7057: FAC line 1 .... used time: 710.387(ms). Execute id is 0. WITH FUNCTION fib(C INT) RETURN INT AS BEGIN RETURN case when C=1 or C=2 then 1 else fib(C-1)+ fib(C-2) end; END; SELECT fib(35)f FROM DUAL; [-7057]:Too many nested level -7057: FIB line 1 .... used time: 743.573(ms). Execute id is 0.最基本的函数可以，递归调用的函数定义不报错但不能实用。搜索报错信息，参照这篇文章相关dm.ini参数配置如下
EXPR_N_LEVEL = 200 #Maximum nesting levels for expression N_PARSE_LEVEL = 100 #Maximum nesting levels for parsing object MAX_SQL_LEVEL = 500 #Maximum nesting levels of VM stack frame for sql修改参数还要重启服务就不测试了。
