看我做的数据库包装器

newkid

原帖由 yulihua49 于 2010-3-5 09:48 发表

这是个难题。不管谁修改，读时都要加行锁才保证不漏。下载时间如果很长就比较困难。
批量修改，如果找不到条件是没法办的。通用一点的，还是fetch一条改一条吧，为了少影响别人，每卸载一定的记录就commit一次。我会安排一个参数COMMITNUM，缺省10000.
至于速度问题，一般非实时下载，还是可以容忍的。关键是快速找到增量。
使用保留游标和一次绑定、投影少量列的技术，update操作已经快了不少，不过仍比fetch操作差一个数量级。
这类程序，一般编程简单一些，使用省事一些，速度够用即可。大部分用于更新字典表，这类表很少修改，比较稳定，但是表的数量较大。业务表的上载下载，同步更新往往有原子性要求，应该采取交易处理的办法。
你可有更好的算法？

你这个修改源数据的做法是很不好的，不但增加了数据库负担，而且使得判断增量时不得不做全表扫描。

WHERE 修改时间戳>=下载时间戳  ------ 这个会导致全表扫描

如果你改用批次时间戳，则只需要扫描上次下载之后的数据：

WHERE 修改时间戳>=上次下载时间  ------ 这个可以利用索引

如果你想保证事务的原子性，DIY一个通用方法是很困难的，相当于实现了ORACLE的ONLINE REDO LOG。你能够做到更新的先后顺序（先父表再子表）就很不错了。如果你有兴趣可以去研究一下ORACLE高级复制的实现原理。
所以我们要尽量用已经有的技术，比如GOLDENGATE啦，高级复制啦，人家已经研究得很成熟了。

关于时间戳漏洞参见TOM KYTE的Oracle 9i10g编程艺术:

7.3.1 一种会失败的常用数据仓库技术
我看到，许多人都喜欢用这样一种常用的数据仓库技术：
（1） 他们使用一个触发器维护源表中的一个LAST_UPDATED 列，这与上一章的6.2.3 节中讨论的
方法很相似。
（2） 最初要填充数据仓库表时，他们要记住当前的时间，为此会选择源系统上的SYSDATE。例
如，假设现在刚好是上午9:00。
（3） 然后他们从事务系统中拉（pull）出所有行，这是一个完整的SELECT * FROM TABLE 查询，
可以得到最初填充的数据仓库。
（4） 要刷新这个数据仓库，他们要再次记住现在的时间。例如，假设已经过去了1 个小时，现
在源系统上的时间就是10:00.他们会记住这一点。然后拉出自上午9:00（也就是第一次拉出
数据之前的那个时刻）以来修改过的所有记录，并把这些修改合并到数据仓库中。
注意这种技术可能会在两次连续的刷新中将相同的记录“拉出”两次。由于时钟的粒度所致，这是不可
避免的。MERGE 操作不会受此影响（即更新数据仓库中现有的记录，或插入一个新记录）。
他们相信，现在数据仓库中有了自第一次执行拉出操作以来所修改的所有记录。他们确实可能有所
有记录，但是也有可能不是这样。对于其他采用锁定系统的数据库来说，这种技术确实能很好地工作，在
这些数据库中读会被写阻塞，反之写也会被读阻塞。但是在默认支持非阻塞读的系统中，这个逻辑是有问
题的。
要看这个例子有什么问题，只需假设上午9:00 至少有一个打开的未提交事务。例如，假设在上午
8:59:30 时，这个事务已经更新了表中我们想复制的一行。在上午9:00， 开始拉数据时，会读取这个表中
的数据，但是我们看不到对这一行做的修改；只能看到它的最后一个已提交的版本。如果在查询中到达这
一行时它已经锁定，我们就会绕过这个锁。如果在到达它之前事务已经提交，我们还是会绕过它读取查询
开始时的数据，因为读一致性只允许我们读取语句开始时数据库中已经提交的数据。在上午9:00 第一次
拉数据期间我们读不到这一行的新版本，在上午10:00 刷新期间也读不到这个修改过的行。为什么呢？上
午10:00 的刷新只会拉出自那天早上上午9:00 以后修改的记录，但是这个记录是在上午8:59:30 时修改的，
我们永远也拉不到这个已修改的记录。
在许多其他的数据库中，其中读会被写阻塞，可以完成已提交但不一致的读，那么这个刷新过程就能
很好地工作。如果上午9:00（第一次拉数据时）我们到达这一行，它已经上锁，我们就会阻塞，等待这一
行可用，然后读取已提交的版本。如果这一行未锁定，那么只需读取就行，因为它们都是已提交的。
那么，这是否意味着前面的逻辑就根本不能用呢？也不是，这只是说明我们需要用稍微不同的方式来
得到“现在”的时间。应该查询V$TRANSACTION，找出最早的当前时间是什么，以及这个视图中START_TIME
列记录的时间。我们需要拉出自最老事务开始时间（如果没有活动事务，则取当前的SYSDATE 值）以来经
过修改的所有记录：
select nvl( min(to_date(start_time,'mm/dd/rr hh24:mi:ss')),sysdate)
from v$transaction;
在这个例子中，这就是上午8:59:30，即修改这一行的事务开始的那个时间。我们在上午10:00 刷新
数据时，会拉出自那个时间以来发生的所有修改，把这些修改合并到数据仓库中，这就能得到需要的所有
东西。

[ 本帖最后由 newkid 于 2010-3-7 03:42 编辑 ]

newkid

原帖由 linyisen1985 于 2010-3-5 10:09 发表
OMG,这个帖子讨论了一年多！？我看到26页的时候就完全跟不上了，一直翻到最后一页发现大家还在讨论，太佩服了

别人很少插嘴了，基本上就是两个倔老头在抬杠谁也不服谁。

〇〇

就是专用和通用之争，因为要2次开发，所以要有通用性，但效率就不如专用了
用户的习惯也是1个问题，前几天和业务人员讨论需求
2个调查表相同的指标用不同的编码，疑惑问他们答复说就是在表格上的排列顺序，这个他们是“不可能”改的

最后达成的妥协是，他们用详细一些（包含简单一些的表的所有指标）的表的指标描述2个表的逻辑审核关系

newkid

这不是通用专用之争，而是编程风格之争。
楼主的出发点是“程序员都不爱写SQL”，因此包装器替他们做了数据存取的事，程序员写数据库之外的计算。
这就意味着注定了不能利用到数据库的全部威力。
我的主张则是进可能利用SQL, 把存储过程看作SQL的轻量级包装器。
虽然他一再强调这个OCI包装器可以执行任何SQL, 因此我能用的他都能用，但实际情况是，写手工模板的工作量包含了写SQL, 而且比在存储中写SQL更麻烦，谁会这么用？

〇〇

我见到的现状是，业务人员都不爱用计算机编程

yulihua49

原帖由 〇〇 于 2010-3-6 08:46 发表
就是专用和通用之争，因为要2次开发，所以要有通用性，但效率就不如专用了

你还是没有看490楼的例子，那是个专用程序，比DAU效率低很多。
提高效率是需要很多技巧的，很多人不会费那个事。以OCI编程为例，很多人甚至不愿bind。
好的通用程序包装了很多技巧在里边，让你不知不觉中使用，因此不仅方便而且高效。

业务人员的确讨厌程序，我们也讨厌。在前边好多页我有一个例子，关于统计的。
铁路的统计系统非常讨厌，经常改变统计口径，发一个电报，十天半月就要实施。

我们也非常烦老改程序，于是写了一个通用的统计脚本解释语言，让业务人员自己写（程序）。他们还是接受了这个语言，因为不用老求我们了。这个脚本语言解释器的处理方法就是DAU的前身。

他们可以写程序，但绝不能用SQL因为英语困难点。

还有，他们不关心什么数据结构，他们只关心那个统计表的第几项与其它表的第几项有什么关系。
比如，今日，货报2（XB2）的第二项（昨日合计）＝昨天货报2的第一项（当日合计）：

.,XB2,2=\-1,XB2,1  #.代表今天，\-1代表昨天

第三项＝第四至第六项之和：
.,XB2,3=4++6
等等。
这些都由解释器翻译成SQL，这些都需要通用的SQL执行器，因为编程时根本就不知道语句，当然表名列名也是一概不知。
所以需要写表名列名的语言都不好使，如PRO*C。所以只能用OCI。

能否不写SQL而解决应用逻辑问题，在这里是颠覆性观念。

[ 本帖最后由 yulihua49 于 2010-3-7 14:21 编辑 ]

〇〇

请提供一个"快速"把数组写入数据库表的oci例子
要比
http://www.itpub.net/thread-1275438-1-1.html
（100万行4秒）快，来完成我自己翻版
http://www.itpub.net/thread-1271979-9-2.html
超过cube的宏伟目标

yulihua49

原帖由 〇〇 于 2010-3-7 16:49 发表
请提供一个"快速"把数组写入数据库表的oci例子
要比
http://www.itpub.net/thread-1275438-1-1.html
（100万行4秒）快，来完成我自己翻版
http://www.itpub.net/thread-1271979-9-2.html
超过cube的宏伟目标

你的cube施加了很多限制，测试一下可以，推广到一般不行。
一般情况还是cube好一些。
我一再强调20/80法则，一般情况使用DAU，省不少事，极端的情况，程序员自己搞吧，我们提供平台的就管不了那么多了。
批量插入看来性能不错，但是我还没有想好批量错误的妥善处理，暂时弄不了。
如果在DAU环境下玩批量，目前就请直接使用sqlora，一切问题自己处理。
newkid所说有一定道理，嫌DAU费事就自己直接搞，在我的应用里也有这种情况。
但更多的情况是写模板时多费一点点事，后来就省了很多事，比方数据打包通信或写文件，与其它数据综合处理等等环节。这完全在程序员自己了，从整体考虑决定采取什么方式。

你那个数组绑定好像有点问题，明天上班给你查查。另外绑定几千几万的，确实内存开销过大，建议每次适当的一批，大量数据多批绑定。
那个sqlldr2专干一件事，可以耗资源多一些。如果是大的OLTP程序的一部分，消耗过大资源不妥。

[ 本帖最后由 yulihua49 于 2010-3-7 21:31 编辑 ]

yulihua49

原帖由 〇〇 于 2010-3-7 16:49 发表
请提供一个"快速"把数组写入数据库表的oci例子
要比
http://www.itpub.net/thread-1275438-1-1.html
（100万行4秒）快，来完成我自己翻版
http://www.itpub.net/thread-1271979-9-2.html
超过cube的宏伟目标

这是sqlora批量绑定的例子：

1. tuxticket@jgbticket:~/sdbc/sqlora/libsqlora8-2.3.3/examples> cat ex12.c
   
2. /* $Id: ex12.c 221 2002-08-24 12:54:47Z kpoitschke $ */
   
3. #include <stdio.h>
   
4. #include <stdlib.h>
   
5. #include "examples.h"
   
6. 
   
7. enum {
   
8.   MAX_NAME_LEN = 64,
   
9.   MAX_ARRAY_SIZE = 4            /* Note: usually this should be 100 or so.
   
10.                                  * Used a smaller one to do test all paths in the code
    
11.                                  */
    
12. };
    
13. 
    
14. /**
    
15. * Structure to store the query results
    
16. */
    
17. typedef struct _result_t {
    
18.   char name[MAX_NAME_LEN+1];    /* output ENAME */
    
19.   double salary;                /* output SAL */
    
20.   short name_ind;               /* indicator variable of ENAME */
    
21.   short sal_ind;                /* indicator variable of SAL */
    
22.   unsigned short name_rlen;     /* the actual length of ENAME */
    
23.   unsigned short name_rcode;    /* the return code of ENAME */
    
24. } result_t;
    
25. 
    
26. void print_record __P((result_t * r));
    
27. 
    
28. 
    
29. int do_array_select(sqlo_db_handle_t dbh, double min_salary)
    
30. {
    
31.   sqlo_stmt_handle_t sth;       /* statement handle */
    
32.   int i;                        /* loop variable */
    
33.   int status;                   /* return code of sqlo_... */
    
34.   result_t result[MAX_ARRAY_SIZE];
    
35.   int rows_fetched;             /* number of rows fetched per execute */
    
36.   int rows_fetched_total = 0;   /* total number of rows */
    
37.   int done_fetching = 0;        /* flag indicating end of fetch */
    
38.   double salary = min_salary;   /* input variable for SAL */
    
39.   

复制代码

int skip_size = sizeof(result[0]); /* The skip size */

1. 
   
2.   sth = prepare_cursor(dbh, &salary); /* see ex10.c */
   
3. 
   
4.   /* define output */
   
5.   if (SQLO_SUCCESS !=
   
6.       (sqlo_define_by_pos2(sth, 1, SQLOT_STR, result[0].name, sizeof(result[0].name),
   
7.                            &result[0].name_ind, &result[0].name_rlen,
   
8.                            &result[0].name_rcode, skip_size)) ||
   
9.       (sqlo_define_by_pos2(sth, 2, SQLOT_FLT, &result[0].salary, sizeof(result[0].salary),
   
10.                            &result[0].sal_ind, 0, 0, skip_size))) {
    
11.     error_exit(dbh, "sqlo_define_by_pos");
    
12.   }
    
13. 
    
14.   /* execute and fetch  the result */
    
15.   status = sqlo_execute(sth, 0);
    
16. 
    
17.   while (!done_fetching) {
    
18. 
    
19.     rows_fetched = MAX_ARRAY_SIZE;
    
20. 
    
21.     /* get the next set */
    
22.     status = sqlo_fetch(sth, MAX_ARRAY_SIZE);
    
23. 
    
24.     if (0 > status)
    
25.       error_exit(dbh, "sqlo_execute(NEXT)");
    
26.     else if (SQLO_NO_DATA == status) {
    
27.       
    
28.       rows_fetched = sqlo_prows(sth);
    
29.          
    
30.       /* sqlo_prows returns now the total number of fetched rows
    
31.        * the difference to the previous total fechted rows is
    
32.        * the number of rows fetched in this last call to sqlo_execute
    
33.        */
    
34.       rows_fetched = rows_fetched - rows_fetched_total;
    
35.       done_fetching = 1;
    
36. 
    
37.     }
    
38.    
    
39.     /* print the records */
    
40.     for (i = 0; i < rows_fetched; ++i)
    
41.       print_record(&result[i]);
    
42. 
    
43.     rows_fetched_total += rows_fetched;
    
44. 
    
45.   }
    
46.   printf("Selected %d employees\n", rows_fetched_total);
    
47.   /* finished. */
    
48.   sqlo_close(sth);
    
49. 
    
50.   return 1;
    
51. }
    
52. 
    
53. /* print record */
    
54. void print_record(result_t * r /* I - The record */ )
    
55. {
    
56.   printf("Name=%-8s Salary= %6.2f\n",
    
57.          (r->name_ind == SQLO_NULL_IND ? "NULL" : r->name),
    
58.          (r->sal_ind == SQLO_NULL_IND ? -1.0 : r->salary));
    
59. }
    
60. 
    
61. /* $Id: ex12.c 221 2002-08-24 12:54:47Z kpoitschke $ */

复制代码

变量都在结构里，4个记录一批，注意红字的skip_size，和bind时要有变量类型和skip_size。

[ 本帖最后由 yulihua49 于 2010-3-8 16:18 编辑 ]

yulihua49

原帖由 newkid 于 2010-2-24 01:37 发表
测试结果：

全自动软软解析：
BEGIN
   FOR I IN 1..100000 LOOP
       INSERT INTO T VALUES (I,I,I,I);
   END LOOP;
END;
/

试了你的第一个存储过程，是最快的。
./ti ld.ini
insert 100000Rec's TIMEVAL=8779961us    //DAU
ORA_Rpc:return=0,TIMEVAL=3624729us     //RPC
这种存粹引擎内部的处理，存储过程较优。

1. 
   
2.        ret=DAU_init(&dau,&SQL_Connect,"T",0,0);
   
3.         if(ret) {
   
4.                 printf("Table T not exist!\n");
   
5.                 ___SQL_CloseDatabase__(&SQL_Connect);
   
6.                 return 2;
   
7.         }
   
8.         ip=dau.srm.rec;
   
9.         now=now_usec();
   
10.         for(i=0;i<100000;i++) {
    
11.                 ip[0]=ip[1]=ip[2]=ip[3]=i;
    
12.                 ret=bind_ins(&dau,stmt);
    
13.                 if(ret) {
    
14.                         printf("insert error %s\n",stmt);
    
15.                         break;
    
16.                 }
    
17.         }
    
18.         printf("insert %dRec's TIMEVAL=%lldus\n",i,now_usec()-now);
    
19. 
    
20.         strcpy(stmt,"TICKET.TI");
    
21.         now=now_usec();
    
22.         ret=ORA_Rpc(&SQL_Connect,stmt,&result);
    
23.         printf("ORA_Rpc:return=%d,TIMEVAL=%lldus\n",ret,now_usec()-now);
    
24. 
    
25. 
    

复制代码

OCI调用存储过程似乎比sqlplus快点，sqlplus调用时间是4-5秒。

[ 本帖最后由 yulihua49 于 2010-3-26 15:19 编辑 ]