测试xugu liunx

〇〇

/tmp/xugu_lin_x64  -daemon
XuGu SQL Server 8.1.0 (Built:2014-06-18 00:30:00.052702)
Start at: 2014-6-28 12:29:25.
Daemon process is still running.

〇〇

〇〇 发表于 2014-6-28 12:26
/tmp/xugu_lin_x64  -daemon
XuGu SQL Server 8.1.0 (Built:2014-06-18 00:30:00.052702)
Start at: 2014 ...

仍然不支持connect by level
SQL>with t as(select level t from dual connect by level<=1e4)
2   ,s as(select level s from dual connect by level<=5e3)
3   ,a as(select (s-1)*1e4+t a from t,s)
4   select substr(a,-1,1)x,sum(a)s from a
5   group by substr(a,-1,1)
6   order by 1;
Error: [E19224] Connect by 表达式错误

〇〇

真的奇怪
SQL>create table t as select 1 a union select 2;

Execute successful.
Use time:5 ms.

SQL>create table t2 as
2   select row_number()over(order by a.a) rn from
3   (select t.a from t,t t1,t t2,t t3,t t4,t t5,t t6,t t7,t t8,t t9)a,
4   (select t.a from t,t t1,t t2,t t3)b
5   ;

Execute successful.
Use time:50 ms.

SQL>create table t1w as select rn from t2 where rn<=10000;

Execute successful.
Use time:20 ms.

SQL>create table t5k as select rn from t2 where rn<=5000;

Execute successful.
Use time:10 ms.

SQL>with a as(select (s.rn-1)*10000+t.rn a from t1w t,t5k s)
2   select substr(a,-1,1)x,sum(a)s from a
3   group by substr(a,-1,1)
4   order by 1;

X | S |
------------------------------------------------------------------------------

Total 0 records.

Use time:22260 ms.

SQL>with a as(select (s.rn-1)*10000+t.rn a from t1w t,t5k s)
2   select substr(a,length(a),1)x,sum(a)s from a
3   group by substr(a,length(a),1)
4   order by 1;

X | S |
------------------------------------------------------------------------------

Total 0 records.

Use time:19680 ms.
用常量是可以的
SQL>select substr('10',length('10'),1) ;

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

Total 1 records.

Use time:0 ms.
数值型也能自动转的
SQL>select substr(10,length(10),1) ;

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

Total 1 records.

Use time:5 ms.

〇〇

负数位置也是可以的
SQL>select substr(10,-1,1) ;

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

Total 1 records.

Use time:0 ms.

SQL>select substr('10',-1,1) ;

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

Total 1 records.

Use time:5 ms.

〇〇

这都算错了，看来大表关联有问题
SQL>with a as(select (s.rn-1)*10000+t.rn a from t1w t,t5k s)
2   select count(a),sum(a) from a;

EXPR1 | EXPR2 |
--------------------------------------------------------------------
0 | <NULL>|

Total 1 records.

Use time:19265 ms.

〇〇

不用with是对的
SQL>select count(*) from t1w;

EXPR1 |
---------------------------------------
10000 |

Total 1 records.

Use time:0 ms.

SQL>select count(*) from t5k;

EXPR1 |
---------------------------------------
5000 |

Total 1 records.

Use time:0 ms.

SQL>select count(*) from t1w,t5k;

EXPR1 |
---------------------------------------
50000000 |

Total 1 records.

Use time:4005 ms.

〇〇

〇〇 发表于 2014-6-28 12:55
不用with是对的
SQL>select count(*) from t1w;

SQL>select substr(a,-1,1)x,sum(a)s from (select (s.rn-1)*10000+t.rn a from t1w t,t5k s) a
2   group by substr(a,-1,1)
3   order by 1;

X | S |
------------------------------------------------------------------------------
0| 125000025000000|
1| 124999980000000|
2| 124999985000000|
3| 124999990000000|
4| 124999995000000|
5| 125000000000000|
6| 125000005000000|
7| 125000010000000|
8| 125000015000000|
9| 125000020000000|

Total 10 records.

Use time:68370 ms. （超过10行，就要按任意键。。。。）

〇〇

〇〇 发表于 2014-6-28 12:58
SQL>select substr(a,-1,1)x,sum(a)s from (select (s.rn-1)*10000+t.rn a from t1w t,t5k s) a
2   gro ...

用了新客户端
SQL>set pagesize 50000
pagesize 50000

SQL>select substr(a,-1,1)x,sum(a)s from (select (s.rn-1)*10000+t.rn a from t1w t,t5k s) a
2   group by substr(a,-1,1)
3   order by 1;

X | S |
------------------------------------------------------------------------------
0| 125000025000000|
1| 124999980000000|
2| 124999985000000|
3| 124999990000000|
4| 124999995000000|
5| 125000000000000|
6| 125000005000000|
7| 125000010000000|
8| 125000015000000|
9| 125000020000000|

Total 10 records.

Use time:45270 ms.

〇〇

终于改好了
SQL>with a as(select (s.rn-1)*10000+t.rn a from t1w t,t5k s)
2   select substr(a,-1,1)x,sum(a)s from a
3   group by substr(a,-1,1)
4   order by 1;

X | S |
------------------------------------------------------------------------------
0| 125000025000000|
1| 124999980000000|
2| 124999985000000|
3| 124999990000000|
4| 124999995000000|
5| 125000000000000|
6| 125000005000000|
7| 125000010000000|
8| 125000015000000|
9| 125000020000000|

Total 10 records.

Use time:49630 ms.

〇〇

还支持并行度
SQL>with a as(select (s.rn-1)*10000+t.rn a from t1w t,t5k s)
2   select substr(a,-1,1)x,sum(a)s from a
3   group by substr(a,-1,1)
4   order by 1 parallel 2;

X | S |
------------------------------------------------------------------------------
0| 125000025000000|
1| 124999980000000|
2| 124999985000000|
3| 124999990000000|
4| 124999995000000|
5| 125000000000000|
6| 125000005000000|
7| 125000010000000|
8| 125000015000000|
9| 125000020000000|

Total 10 records.

Use time:22285 ms.