用过程语言实现group by cube的代码挑战赛

xqmei

--Can we try this way? purely DML operation using traditional GROUP BY:

truncate table yourcube;

insert into yourcube
select c1,c2,c3,c4, sum(v) sv, count(v) cv ,0 gid from t group by c1,c2,c3,c4;

insert into yourcube
select c1,c2,c3,null c4, sum(sv) sv, sum(cv) cv, 1 gid from yourcube where gid=0  group by c1,c2,c3
union all
select c1,c2,null c3,c4, sum(sv) sv, sum(cv) cv, 2 gid from yourcube where gid=0  group by c1,c2,c4
union all
select c1,null c2,c3,c4, sum(sv) sv, sum(cv) cv, 4 gid from yourcube where gid=0  group by c1,c3,c4
union all
select null c1, c2,c3,c4, sum(sv) sv, sum(cv) cv, 8 gid from yourcube where gid=0  group by c2,c3,c4

union all
select c1,c2,null c3,null c4, sum(sv) sv, sum(cv) cv, 3 gid from yourcube where gid=0  group by c1,c2
union all
select c1,null c2,c3,null c4, sum(sv) sv, sum(cv) cv, 5 gid from yourcube where gid=0  group by c1,c3
union all
select null c1,c2,c3,null c4, sum(sv) sv, sum(cv) cv, 9 gid from yourcube where gid=0  group by c2,c3
union all
select c1,null c2,null c3,c4, sum(sv) sv, sum(cv) cv, 6 gid from yourcube where gid=0  group by c1,c4
union all
select null c1,c2,null c3,c4, sum(sv) sv, sum(cv) cv, 10 gid from yourcube where gid=0  group by c2,c4
union all
select null c1,null c2, c3,c4, sum(sv) sv, sum(cv) cv, 12 gid from yourcube where gid=0  group by c3,c4

union all
select c1,null c2,null c3, null c4, sum(sv) sv, sum(cv) cv, 7 gid from yourcube where gid=0  group by c1
union all
select null c1, c2,null c3, null c4, sum(sv) sv, sum(cv) cv, 11 gid from yourcube where gid=0  group by c2
union all
select null c1, null c2, c3, null c4, sum(sv) sv, sum(cv) cv, 13 gid from yourcube where gid=0  group by c3
union all
select null c1, null c2, null c3,  c4, sum(sv) sv, sum(cv) cv, 14 gid from yourcube where gid=0  group by c4

union all
select null c1,null c2,null c3, null c4, sum(sv) sv, sum(cv) cv, 15 gid from yourcube where gid=0 ;

newkid

原帖由 xqmei 于 2010-3-5 02:00 发表
--Can we try this way? purely DML operation using traditional GROUP BY:

truncate table yourcube;

insert into yourcube
select c1,c2,c3,c4, sum(v) sv, count(v) cv ,0 gid from t group by c1,c2,c3,c4;

insert into yourcube
select c1,c2,c3,null c4, sum(sv) sv, sum(cv) cv, 1 gid from yourcube where gid=0  group by c1,c2,c3
union all
select c1,c2,null c3,c4, sum(sv) sv, sum(cv) cv, 2 gid from yourcube where gid=0  group by c1,c2,c4
union all
select c1,null c2,c3,c4, sum(sv) sv, sum(cv) cv, 4 gid from yourcube where gid=0  group by c1,c3,c4
union all
select null c1, c2,c3,c4, sum(sv) sv, sum(cv) cv, 8 gid from yourcube where gid=0  group by c2,c3,c4

union all
select c1,c2,null c3,null c4, sum(sv) sv, sum(cv) cv, 3 gid from yourcube where gid=0  group by c1,c2
union all
select c1,null c2,c3,null c4, sum(sv) sv, sum(cv) cv, 5 gid from yourcube where gid=0  group by c1,c3
union all
select null c1,c2,c3,null c4, sum(sv) sv, sum(cv) cv, 9 gid from yourcube where gid=0  group by c2,c3
union all
select c1,null c2,null c3,c4, sum(sv) sv, sum(cv) cv, 6 gid from yourcube where gid=0  group by c1,c4
union all
select null c1,c2,null c3,c4, sum(sv) sv, sum(cv) cv, 10 gid from yourcube where gid=0  group by c2,c4
union all
select null c1,null c2, c3,c4, sum(sv) sv, sum(cv) cv, 12 gid from yourcube where gid=0  group by c3,c4

union all
select c1,null c2,null c3, null c4, sum(sv) sv, sum(cv) cv, 7 gid from yourcube where gid=0  group by c1
union all
select null c1, c2,null c3, null c4, sum(sv) sv, sum(cv) cv, 11 gid from yourcube where gid=0  group by c2
union all
select null c1, null c2, c3, null c4, sum(sv) sv, sum(cv) cv, 13 gid from yourcube where gid=0  group by c3
union all
select null c1, null c2, null c3,  c4, sum(sv) sv, sum(cv) cv, 14 gid from yourcube where gid=0  group by c4

union all
select null c1,null c2,null c3, null c4, sum(sv) sv, sum(cv) cv, 15 gid from yourcube where gid=0 ;

还可以逐级汇总，比如gid=3可以从gid=1或gid=2汇总得来。

xqmei

yes, for such a small set of data, it is un-necessary, I believe.

newkid

原帖由 xqmei 于 2010-3-5 03:01 发表
yes, for such a small set of data, it is un-necessary, I believe.

你看我在7楼和11楼用PLSQL模拟的，速度提高了近一倍，还是很可观的。

〇〇

这种方法前一阵已经测试过了

〇〇

把DEFAULT_ARRAY_SIZE从50改为1000,貌似快了1些

D:\app>by

D:\app>set MSVCDir=D:\app\pc\vc6

D:\app>set INCLUDE=D:\app\pc\vc6\ATL\INCLUDE;D:\app\pc\vc6\INCLUDE;D:\app\pc\vc6\MFC\INCLUDE;

D:\app>set LIB=D:\app\pc\vc6\LIB;D:\app\pc\vc6\MFC\LIB;

D:\app>set PATH=D:\app\pc\vc6\BIN;D:\app\oracle\product\11.1.0\db_1\bin;C:\Program Files\HP\NCU;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem

D:\app>set oracle_home=D:\app\oracle\product\11.1.0\db_1

D:\app>rem cl -ID:\app\oracle\product\11.1.0\db_1\oci\include -ID:\app\oracle\product\11.1.0\db_1\precomp\public -I. -ID:\app\pc\vc6\include -D_MT -D_DLL -Z
i .c /link D:\app\oracle\product\11.1.0\db_1\oci\lib\msvc\oci.lib /libpath:D:\app\oracle\product\11.1.0\db_1\precomp\lib /libpath:D:\app\oracle\product\11.1
.0\db_1\precomp\lib\msvc orasql9.LIB /LIBPATH:D:\app\pc\vc6\lib msvcrt.lib /nod:libc

D:\app>vc6en
@echo off
rem Set PATH=D:\app\pc\vc6\bin;%PATH%
Set PATH=D:\app\VC6EN\VC98\bin;%PATH%
Set INCLUDE=d:\app\stl;D:\app\VC6EN\VC98\include;%INCLUDE%
Set LIB=D:\app\VC6EN\VC98\lib;%LIB%

D:\app>cl /O2 /EHsc map9a.cpp /link oci.lib
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.

map9a.cpp
Microsoft (R) Incremental Linker Version 6.00.8168
Copyright (C) Microsoft Corp 1992-1998. All rights reserved.

/out:map9a.exe
oci.lib
map9a.obj

D:\app>
D:\app\instantclient10_1>\app\map9a LT/LT@10.6.132.43/orcl query="select c1,c2,c3,c4,v from(select  mod(level,9)c1,mod(level,97)c2,mod
(level,119)c3,mod(case when mod(level,13)>0 then level end ,113)c4, level v from dual connect by level<=1E6)" file=NUL

       0 rows exported at 2010-03-05 10:10:29
recn=0
  500000 rows exported at 2010-03-05 10:10:32
recn=500000
1000000 rows exported at 2010-03-05 10:10:36
         output file  closed at 1000000 rows.

sum=1
sum(c1+c2+c3+v)=500111497488
sum(c4)=51690810
从数据库把表读入内存6973ms --包括connect by level的时间
4,5,67,114,152760
申请map内存0ms
申请一层内存15ms
新建一层节点515ms,recn=1000000,每毫秒读1941行,每毫秒写1941节点
申请后面内存16ms,1000000
新建后面节点2371ms,recn=2299600,每毫秒读421行key,每毫秒写548节点
0,first=2,renc=0
1,first=1000007F,renc=1000003
2,first=20007F00,renc=1000002
3,first=30007F7F,renc=2260123
4,first=407F0000,renc=1000001
5,first=507F007F,renc=2260125
6,first=607F7F00,renc=2284774
7,first=707F7F7F,renc=2284669
8,first=8F000000,renc=1000000
9,first=9F00007F,renc=2260124
10,first=AF007F00,renc=2273610
11,first=BF007F7F,renc=2284668
12,first=CF7F0000,renc=2284775
13,first=DF7F007F,renc=2299366
14,first=EF7F7F00,renc=2299485
15,first=FF7F7F7F,renc=2299599
汇总后面节点140ms,recn=2299600,每毫秒读7142行，每毫秒写9282行
释放所有动态内存0ms,2299600
释放所有map内存203ms,2299600
总时间3276ms,2299600

D:\app\instantclient10_1>\app\map9a LT/LT@10.6.132.43/orcl query="select c1,c2,c3,c4,v from t" file=NUL

       0 rows exported at 2010-03-05 10:16:12
recn=0
  500000 rows exported at 2010-03-05 10:16:14
recn=500000
1000000 rows exported at 2010-03-05 10:16:15
         output file  closed at 1000000 rows.

sum=1
sum(c1+c2+c3+v)=500111497488
sum(c4)=51690810
从数据库把表读入内存3385ms
4,5,67,114,152760
申请map内存0ms
申请一层内存31ms
新建一层节点437ms,recn=1000000,每毫秒读2288行,每毫秒写2288节点
申请后面内存31ms,1000000
新建后面节点2231ms,recn=2299600,每毫秒读448行key,每毫秒写582节点
0,first=2,renc=0
1,first=1000007F,renc=1000003
2,first=20007F00,renc=1000002
3,first=30007F7F,renc=2260123
4,first=407F0000,renc=1000001
5,first=507F007F,renc=2260125
6,first=607F7F00,renc=2284774
7,first=707F7F7F,renc=2284669
8,first=8F000000,renc=1000000
9,first=9F00007F,renc=2260124
10,first=AF007F00,renc=2273610
11,first=BF007F7F,renc=2284668
12,first=CF7F0000,renc=2284775
13,first=DF7F007F,renc=2299366
14,first=EF7F7F00,renc=2299485
15,first=FF7F7F7F,renc=2299599
汇总后面节点141ms,recn=2299600,每毫秒读7092行，每毫秒写9217行
释放所有动态内存0ms,2299600
释放所有map内存203ms,2299600
总时间3089ms,2299600

[ 本帖最后由 〇〇 于 2010-3-5 10:18 编辑 ]

〇〇

现在就差用direct path 写回表

yulihua49

原帖由 〇〇 于 2010-3-5 10:14 发表
把DEFAULT_ARRAY_SIZE从50改为1000,貌似快了1些

D:\app>by

D:\app>set MSVCDir=D:\app\pc\vc6

D:\app>set INCLUDE=D:\app\pc\vc6\ATL\INCLUDE;D:\app\pc\vc6\INCLUDE;D:\app\pc\vc6\MFC\INCLUDE;

D:\app>set LIB=D:\app\pc\vc6\LIB;D:\app\pc\vc6\MFC\LIB;

D:\app>set PATH=D:\app\pc\vc6\BIN;D:\app\oracle\product\11.1.0\db_1\bin;C:\Program Files\HP\NCU;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem

D:\app>set oracle_home=D:\app\oracle\product\11.1.0\db_1

D:\app>rem cl -ID:\app\oracle\product\11.1.0\db_1\oci\include -ID:\app\oracle\product\11.1.0\db_1\precomp\public -I. -ID:\app\pc\vc6\include -D_MT -D_DLL -Z
i .c /link D:\app\oracle\product\11.1.0\db_1\oci\lib\msvc\oci.lib /libpath\app\oracle\product\11.1.0\db_1\precomp\lib /libpath\app\oracle\product\11.1
.0\db_1\precomp\lib\msvc orasql9.LIB /LIBPATH\app\pc\vc6\lib msvcrt.lib /nod:libc

D:\app>vc6en
@echo off
rem Set PATH=D:\app\pc\vc6\bin;%PATH%
Set PATH=D:\app\VC6EN\VC98\bin;%PATH%
Set INCLUDE=d:\app\stl;D:\app\VC6EN\VC98\include;%INCLUDE%
Set LIB=D:\app\VC6EN\VC98\lib;%LIB%

D:\app>cl /O2 /EHsc map9a.cpp /link oci.lib
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.

map9a.cpp
Microsoft (R) Incremental Linker Version 6.00.8168
Copyright (C) Microsoft Corp 1992-1998. All rights reserved.

/out:map9a.exe
oci.lib
map9a.obj

D:\app>
D:\app\instantclient10_1>\app\map9a LT/LT@10.6.132.43/orcl query="select c1,c2,c3,c4,v from(select  mod(level,9)c1,mod(level,97)c2,mod
(level,119)c3,mod(case when mod(level,13)>0 then level end ,113)c4, level v from dual connect by level

我试用非平衡二叉树归并，速度更慢，2倍。
读写文件肯定比数据库快不止一个数量级。
你完整做一个从数据库读出-归并-写文件的流程。

[ 本帖最后由 yulihua49 于 2010-3-5 10:50 编辑 ]

〇〇

数据库读出-归并-写文件map9.cpp已经作了，你只要把最后fopen的注释打开
问题是把数组写到数据库表，你有经验，提供一个例子。不用direct path也行。

〇〇

试了2种插入,绑定变量和pl/sql
            FOR i IN 1..n LOOP\
                INSERT INTO part_nos\
                    VALUES (partno(i), descrip(i));\
            END LOOP;\

都比较慢，10万行

        t=clock();

        /*
         *  Increment empno by 10, and execute the INSERT
         *  statement. If the return code is 1 (duplicate
         *  value in index), then generate the next
         *  employee number.
         */
         for(i=0;i<100000;i++)
         {
                 sprintf(ename,"li%d",i);
        empno += 10;
        if (oexec(&cda1) && cda1.rc != 1)
        {
            err_report(&cda1);
            do_exit(EXIT_FAILURE);
        }
        while (cda1.rc == 1)
        {
            empno += 10;
            if (oexec(&cda1) && cda1.rc != 1)
            {
                err_report(&cda1);
                do_exit(EXIT_FAILURE);
            }
        }
          /* end for (;;) */
}
/* Commit the change. */
        if (ocom(&lda))
        {
            err_report(&lda);
            do_exit(EXIT_FAILURE);
        }
        printf(
        "\n\n%s added to the %s department as employee number %dtime=%dms\n",
                 ename, dept, empno,clock()-t);

D:\app\instantclient10_1>D:\app\oci\samples\cdemolt
Connected to ORACLE as scott/tiger@10.6.132.43/orcl

Enter employee name (or CR to EXIT): li
Enter employee job: li
Enter employee salary: 10
Enter employee dept: 20


li99999 added to the RESEARCH department as employee number 1017944time=16661ms

Enter employee name (or CR to EXIT):
Exiting...
G'day

http://www.itpub.net/thread-1272755-1-1.html

    t=clock();
    /*  Bind the C arrays to the PL/SQL tables. */
    printf("\nBinding arrays...");
    if (obndra(&cda,
               (text *) ":description",
               -1,
               (ub1 *) descrip,
               DESC_LEN,
               VARCHAR2_TYPE,
               -1,
               descrip_indp,
               descrip_alen,
               descrip_rc,
               (ub4) MAX_TABLE_SIZE,
               &descrip_cs,
               (text *) 0,
               -1,
               -1))
            oci_error();

    if (obndra(&cda,
               (text *) ":partno",
               -1,
               (ub1 *) numbers,
               (sword) sizeof (sword),
               INT_TYPE,
               -1,
               num_indp,
               num_alen,
               num_rc,
               (ub4) MAX_TABLE_SIZE,
               &num_cs,
               (text *) 0,
               -1,
               -1))
            oci_error();

    printf("\nExecuting block...");
    for(j=0;j<100;j++)
    if (oexec(&cda))
        oci_error();

    /* drop table part_nos */
  //  if (oparse(&cda, dt, -1, 0, 2))
  //      oci_error();

    printf("time=%d\n",clock()-t);

D:\app\instantclient10_1>D:\app\oci\samples\blt
Connecting to ORACLE...
Dropping table...
Creating table...
Creating package...
Creating package body...
Parsing PL/SQL block...time=702

Binding arrays...
Executing block...time=4399