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

〇〇

常规路径加载int64无提示退出，怀疑是
oparse(&cda, sqlstmt, (sb4) -1, DEFER_PARSE,                  /* parse */
               (ub4) VERSION_7))
不识别11g
如果那样，只能用高版本的
OCIStmtPrepare
重写了

〇〇

从11gr2往远程11gr1导入int64类型成功，是因为在客户端组织成数据块格式？

D:\app>mc43 LT/LT@10.6.99.65/orcl query="select * from t" file=NUL
0,1     1,1     2,1     3,1     4,4
       0 rows exported at 2010-04-07 18:51:14
recn=0
  500000 rows exported at 2010-04-07 18:51:15
recn=500000
1000000 rows exported at 2010-04-07 18:51:16
         output file  closed at 1000000 rows.

sum=1
sum(c1+c2+c3+v)=500111497488
sum(c4)=51690810
从数据库把表读入内存2406ms
4,5,67,114,152760
申请map内存0ms
申请一层内存16ms
新建一层hashmap节点688ms,recn=1000000,每毫秒读1453行,每毫秒写1453节点
申请后面内存15ms,1000000
realloc2000000
gid=1 found 0 time
新建后面节点[1],recn=2273836
新建后面节点[2],recn=2284894
新建后面节点[3],recn=2284894
新建后面节点[4],recn=2299600
新建后面节点[5],recn=2299600
新建后面节点[6],recn=2299600
新建后面节点2031ms,recn=2299600,每毫秒读492行key,每毫秒写639节点
malloc time=0ms
let time=31ms

Initializing the environment..
Logged on as LT
col0:c1 3       1
col1:c2 3       1
col2:c3 3       1
col3:c4 3       1
col4:cv 3       4
col5:sv 3       8
col6:gid        3       1
time=94ms
col_ary_rows=4096,col_ary_cols=7
1000all time=4703ms
插入结果表1000000行
malloc time=0ms
let time=47ms
col0:c1 3       1
col1:c2 3       1
col2:c3 3       1
col3:c4 3       1
col4:cv 3       4
col5:sv 3       8
col6:gid        3       1
time=63ms
col_ary_rows=4096,col_ary_cols=7
600all time=8891ms
插入结果表1299600行
释放所有动态内存16ms,2299600
释放所有map内存359ms,2299600
总时间16765ms,2299600
3步（读入+运算+写出）总时间19218ms

Exiting with SUCCESS status 0

D:\app>

〇〇

无意发现,如果目标表有数据,直接路径插入加快了很多

D:\app\instantclient10_1>..\d LT/LT@10.6.132.43/orcl query="select * from t" file=NUL
0,1     1,1     2,1     3,1     4,4
       0 rows exported at 2010-04-27 13:53:51
recn=0
  500000 rows exported at 2010-04-27 13:53:52
recn=500000
1000000 rows exported at 2010-04-27 13:53:52
         output file  closed at 1000000 rows.

sum=1
sum(c1+c2+c3+v)=500111497488
sum(c4)=51690810
从数据库把表读入内存1736ms
4,5,67,114,152760
申请map内存0ms
申请一层内存18ms
新建一层hashmap节点316ms,recn=1000000,每毫秒读3164行,每毫秒写3164节点
申请后面内存18ms,1000000
realloc2000000
gid=1 found 0 time
新建后面节点[1],recn=2273836
新建后面节点[2],recn=2284894
新建后面节点[3],recn=2284894
新建后面节点[4],recn=2299600
新建后面节点[5],recn=2299600
新建后面节点[6],recn=2299600
新建后面节点965ms,recn=2299600,每毫秒读1036行key,每毫秒写1346节点
malloc time=0ms
let time=5ms

Initializing the environment..
Logged on as LT
col0:c1 3       1
col1:c2 3       1
col2:c3 3       1
col3:c4 3       1
col4:cv 3       4
col5:sv 3       8
col6:gid        3       1
time=46ms
col_ary_rows=4096,col_ary_cols=7
1000all time=5412ms
插入结果表1000000行
malloc time=0ms
let time=8ms
col0:c1 3       1
col1:c2 3       1
col2:c3 3       1
col3:c4 3       1
col4:cv 3       4
col5:sv 3       8
col6:gid        3       1
time=15ms
col_ary_rows=4096,col_ary_cols=7
600all time=3046ms
插入结果表1299600行
释放所有动态内存4ms,2299600
释放所有map内存172ms,2299600
总时间9964ms,2299600
3步（读入+运算+写出）总时间11728ms

Exiting with SUCCESS status 0

SQL> select count(*) from yourcube;

  COUNT(*)
----------
   2299600

已用时间:  00: 00: 00.45

D:\app\instantclient10_1>..\d LT/LT@10.6.132.43/orcl query="select * from t" file=NUL
0,1     1,1     2,1     3,1     4,4
       0 rows exported at 2010-04-27 13:55:09
recn=0
  500000 rows exported at 2010-04-27 13:55:10
recn=500000
1000000 rows exported at 2010-04-27 13:55:10
         output file  closed at 1000000 rows.

sum=1
sum(c1+c2+c3+v)=500111497488
sum(c4)=51690810
从数据库把表读入内存1768ms
4,5,67,114,152760
申请map内存0ms
申请一层内存18ms
新建一层hashmap节点321ms,recn=1000000,每毫秒读3115行,每毫秒写3115节点
申请后面内存18ms,1000000
realloc2000000
gid=1 found 0 time
新建后面节点[1],recn=2273836
新建后面节点[2],recn=2284894
新建后面节点[3],recn=2284894
新建后面节点[4],recn=2299600
新建后面节点[5],recn=2299600
新建后面节点[6],recn=2299600
新建后面节点970ms,recn=2299600,每毫秒读1030行key,每毫秒写1339节点
malloc time=0ms
let time=5ms

Initializing the environment..
Logged on as LT
col0:c1 3       1
col1:c2 3       1
col2:c3 3       1
col3:c4 3       1
col4:cv 3       4
col5:sv 3       8
col6:gid        3       1
time=47ms
col_ary_rows=4096,col_ary_cols=7
1000all time=2511ms
插入结果表1000000行
malloc time=0ms
let time=8ms
col0:c1 3       1
col1:c2 3       1
col2:c3 3       1
col3:c4 3       1
col4:cv 3       4
col5:sv 3       8
col6:gid        3       1
time=17ms
col_ary_rows=4096,col_ary_cols=7
600all time=3191ms
插入结果表1299600行
释放所有动态内存4ms,2299600
释放所有map内存184ms,2299600
总时间7233ms,2299600
3步（读入+运算+写出）总时间9038ms

Exiting with SUCCESS status 0

D:\app\instantclient10_1>

SQL> select count(*) from yourcube;

  COUNT(*)
----------
   4599200

已用时间:  00: 00: 00.89

〇〇

也比常规路径快了
D:\app\instantclient10_1>..\c LT/LT@10.6.132.43/orcl query="select * from t" file=NUL
0,1     1,1     2,1     3,1     4,4
       0 rows exported at 2010-04-27 13:59:03
recn=0
  500000 rows exported at 2010-04-27 13:59:04
recn=500000
1000000 rows exported at 2010-04-27 13:59:04
         output file  closed at 1000000 rows.

sum=1
sum(c1+c2+c3+v)=500111497488
sum(c4)=51690810
从数据库把表读入内存1908ms
======..\c----------
4,5,67,114,152760
申请map内存0ms
申请一层内存23ms
新建一层hashmap节点340ms,recn=1000000,每毫秒读2941行,每毫秒写2941节点
申请后面内存18ms,1000000
realloc2000000
gid=1 found 0 time
新建后面节点[1],recn=2273836
新建后面节点[2],recn=2284894
新建后面节点[3],recn=2284894
新建后面节点[4],recn=2299600
新建后面节点[5],recn=2299600
新建后面节点[6],recn=2299600
新建后面节点963ms,recn=2299600,每毫秒读1038行key,每毫秒写1349节点

Total number of rows to be inserted is 1000000
插入结果表time=3596

Total number of rows to be inserted is 1299600
插入结果表time=5028
释放所有动态内存4ms,2299600
释放所有map内存178ms,2299600
总时间10305ms,2299600
3步（读入+运算+写出）总时间12235ms

Exiting with SUCCESS status 0


SQL> select dbms_metadata.get_ddl('TABLE','YOURCUBE') from dual;

DBMS_METADATA.GET_DDL('TABLE','YOURCUBE')
--------------------------------------------------------------------------------

  CREATE TABLE "LT"."YOURCUBE"
   (    "C1" NUMBER(3,0),
        "C2" NUMBER(3,0),
        "C3" NUMBER(3,0),
        "C4" NUMBER(3,0),
        "SV" NUMBER(30,0),
        "CV" NUMBER(30,0),
        "GID" NUMBER(3,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

〇〇

分别作直接路径和常规路径的awr报告
execute dbms_workload_repository.create_snapshot();

..\c LT/LT@10.6.132.43/orcl query="select * from t" file=NUL
execute dbms_workload_repository.create_snapshot();
@?/rdbms/admin/awrrpt.sql

execute dbms_workload_repository.create_snapshot();

..\d LT/LT@10.6.132.43/orcl query="select * from t" file=NUL
execute dbms_workload_repository.create_snapshot();
@?/rdbms/admin/awrrpt.sql

SUNBLAZE

顶

〇〇

虚拟机
还是慢
SQL> create table t as select  mod(level,4)c1,mod(level,5)c2,mod(level,67)c3,
mod(case when mod(level,13)>0 then level end ,113)c4, level v from dual connect by level<=1E6
  2    3  ;

表已创建。

已用时间:  00: 00: 10.28
SQL> create table tcube as select c1,c2,c3,c4,sum(v)sv,count(v)cv,grouping_id(c1,c2,c3,c4)gid from t group by cube(c1,c2,c3,c4);

表已创建。

已用时间:  00: 00: 10.95
SQL> drop table tcube purge;

表已删除。

已用时间:  00: 00: 00.70
SQL> alter session enable parallel dml;

会话已更改。

已用时间:  00: 00: 00.01
SQL> create table tcube parallel (degree 8)as select /*+parallel (t,8)*/ c1,c2,c3,c4,sum(v)sv,count(v)cv,grouping_id(c1,c2,c3,c4)gid from t group by cube(c1,c2,c3,c4);

表已创建。

已用时间:  00: 00: 09.60

〇〇

并行太多也不好
，
SQL> drop table tcube purge;

表已删除。

已用时间:  00: 00: 00.07
SQL> create table tcube parallel (degree 4)as select /*+parallel (t,4)*/ c1,c2,c3,c4,sum(v)sv,count(v)cv,grouping_id(c1,c2,c3,c4)gid from t group by cube(c1,c2,c3,c4);

表已创建。

已用时间:  00: 00: 05.26

BlueSeine

精彩+执着

ocpdba591

虽然我不会C，但是还是顶上OO入版主