xugu性能测试

〇〇

没有索引，in和not in都很快
SQL>select count(*) from t1m where expr1 in(select expr1 from t1m2);

EXPR1 |
------------------------------------------------------------------------------
1048576 |

Total 1 records.

Use time:827 ms.

SQL>select count(*) from t1m where expr1 not in(select expr1 from t1m2);

EXPR1 |
------------------------------------------------------------------------------
0 |

Total 1 records.

Use time:702 ms.

〇〇

exists/no exists也很快
SQL>select count(*) from t1m where exists (select 1 from t1m2 where t1m2.expr1=t1m.expr1);

EXPR1 |
------------------------------------------------------------------------------
1048576 |

Total 1 records.

Use time:734 ms.

SQL>select count(*) from t1m where not exists (select 1 from t1m2 where t1m2.expr1=t1m.expr1);

EXPR1 |
------------------------------------------------------------------------------
0 |

Total 1 records.

Use time:733 ms.

〇〇

dm7好像更快
SQL>create table t as select 1 a union select 2;
操作已执行
已用时间: 102.581(毫秒). 执行号:44.
SQL>
SQL>select count(*) from
2   (select t.a from t,t t1,t t2,t t3,t t4,t t5,t t6,t t7,t t8,t t9)a,
3   (select t.a from t,t t1,t t2,t t3,t t4,t t5,t t6,t t7,t t8,t t9)b;

行号       COUNT(*)
---------- --------------------
1          1048576

已用时间: 7.118(毫秒). 执行号:45.
SQL>create table t1m as select row_number()over(order by a.a)from
2   (select t.a from t,t t1,t t2,t t3,t t4,t t5,t t6,t t7,t t8,t t9)a,
3   (select t.a from t,t t1,t t2,t t3,t t4,t t5,t t6,t t7,t t8,t t9)b;
操作已执行
已用时间: 00:00:01.132. 执行号:46.


SQL>create table t1m2 as select rownum from
2   (select t.a from t,t t1,t t2,t t3,t t4,t t5,t t6,t t7,t t8,t t9)a,
3   (select t.a from t,t t1,t t2,t t3,t t4,t t5,t t6,t t7,t t8,t t9)b;
create table t1m2 as select rownum from
(select t.a from t,t t1,t t2,t t3,t t4,t t5,t t6,t t7,t t8,t t9)a,
(select t.a from t,t t1,t t2,t t3,t t4,t t5,t t6,t t7,t t8,t t9)b;
第3 行附近出现错误[-2111]:无效的列名[ROWNUM].
SQL>create table t1m2 as select rownum expr1 from
2   (select t.a from t,t t1,t t2,t t3,t t4,t t5,t t6,t t7,t t8,t t9)a,
3   (select t.a from t,t t1,t t2,t t3,t t4,t t5,t t6,t t7,t t8,t t9)b;
操作已执行
已用时间: 730.070(毫秒). 执行号:47.
SQL>desc t1m
列名                            类型       长度       标度       是否可以为空
------------------------------- ---------- ---------- ---------- ------------
ROW_NUMBER()OVER(ORDERBYA.AASC) BIGINT     19         0          Y
SQL>desc t1m2
列名       类型       长度       标度       是否可以为空
---------- ---------- ---------- ---------- ------------
EXPR1      BIGINT     19         0          Y
SQL>drop table t1m;
操作已执行
已用时间: 131.963(毫秒). 执行号:48.
SQL>create table t1m as select row_number()over(order by a.a) expr1 from
2   (select t.a from t,t t1,t t2,t t3,t t4,t t5,t t6,t t7,t t8,t t9)a,
3   (select t.a from t,t t1,t t2,t t3,t t4,t t5,t t6,t t7,t t8,t t9)b;
操作已执行
已用时间: 00:00:01.120. 执行号:49.
SQL>select count(*) from t1m where expr1 in(select expr1 from t1m2);

行号       COUNT(*)
---------- --------------------
1          1048576

已用时间: 429.223(毫秒). 执行号:50.
SQL>select count(*) from t1m where expr1 not in(select expr1 from t1m2);

行号       COUNT(*)
---------- --------------------
1          0

已用时间: 324.660(毫秒). 执行号:51.
SQL>select count(*) from t1m where exists (select 1 from t1m2 where t1m2.expr1=t1m.expr1);

行号       COUNT(*)
---------- --------------------
1          1048576

已用时间: 303.226(毫秒). 执行号:52.
SQL>select count(*) from t1m where not exists (select 1 from t1m2 where t1m2.expr1=t1m.expr1);

行号       COUNT(*)
---------- --------------------
1          0

已用时间: 308.607(毫秒). 执行号:53.
SQL>

〇〇

2个数据库对于create table as select都不要求有列名，dm用长字符串，xugu则用expr1代替

myth8860

〇〇 发表于 2014-4-4 18:10
2个数据库对于create table as select都不要求有列名，dm用长字符串，xugu则用expr1代替

别名其实也挺有学问的

〇〇

〇〇 发表于 2014-3-29 09:24
g_max_loop_num = 100000; 存贮过程最大循环次数

改大还是不行

又收到一个版本
SQL>set g_max_loop_num to 2000000

Execute successful.

。。。
LOOP_NUM(1640000)
LOOP_NUM(1650000)
LOOP_NUM(1660000)
LOOP_NUM(1670000)
148933

Execute successful.
Use time:1165 ms.

〇〇

〇〇 发表于 2014-4-19 07:27
又收到一个版本
SQL>set g_max_loop_num to 2000000

比达梦7快
SQL>call newkidi(2e6);

148933
PL/SQL 过程已成功完成
已用时间: 00:00:02.490. 执行号:43.