oracle dba 技术顾问 faq手册

gaoyiming

整理的 faq手册。
希望对技术顾问或者 dba有用。
打开文档密码：WWW.erp100.COM
大写的.
详情见附件

一、SQL&PL SQL 怎么样大批量的更新数据而不影响正常业务 1、化整为零 一般情况下，如果需要对一个表进行大批量的更新的时候，由于涉及到的记录数很多，所以可能需要花费的时间也就很长，这种情况下，还采用一个单独的update 语句来更新的话，就会造成长时间的加锁，影响到业务。 简单的一个例子，如要更新im_user表中的非空ID为用户表bmw_users中的ID，关联字段为im_user.login_id=bmw_users.nick，语句可以这样写 代码: update im_user i set i.id=(select id from bmw_users u where i.login_id=u.nick)    where i.id is not null; . 这个语句可以更新到几百万记录，当然，耗费时间可能需要1小时以上，对于im_user这样被频繁更新的表来说，肯定是不现实的，所以，该语句可以改写为如下的PL/SQL块。 代码: declare   row_num number := 0; begin for c_usr in (select login_id from im_user t where id is null) loop    update im_user i set i.id =      (select id from bmw_users u where i.login_id = u.nick)    where login_id = c_usr.login_id;    row_num := row_num + 1;    if mod(row_num,100) =0 then      commit;    end if; end loop; commit; end; / . 这样的话，因为每更新100条就提交1次，对表的影响相对是很小的，而且，如果是一个语句，如果中途执行失败，将导致回滚，同样要耗费很长时间，但是这种情况下，因为是一边执行一边提交，基本可以分很多次来操作，之间不会有影响。 2、巧用临时表 很多情况下，需要更新的数据是根据很多条件判断出来的，查询很慢，但是更新的数据本身不多，比较快，这个时候，就可以考虑用临时表，先把需要更新的数据（包括主键）放入到临时表，然后根据主键更新，可能一个UPDATE语句就可以解决问题。 如支付宝迁移时，更新认证表数据： 先创建临时表 代码: create table bmw_idauth_db1_20050704 as select a.id,b.idauth_passdate from bmw_users a,bmw_idauth b     where a.nick=b.nick       and b.status='SUCCESS'       and b.idauth_passdate>=to_date('20050501','yyyymmdd'); create table account_db1_20050704 as select b.account_no,a.idauth_passdate    from bmw_idauth_db1_20050704 a,bmw_payment_account b           where a.id=b.user_id             and b.enabled_status='1'; . 然后根据临时表来更新，因为记录数本身只在查询获得数据比较慢，而这里更新就很快了。 代码: UPDATE (SELECT a.idauth_passdate,                b.id_auth_date,                b.is_id_auth           FROM account_db1_20050704 a, beyond_credit_info b          WHERE a.account_no = b.user_id||'0156') x    SET x.id_auth_date = x.idauth_passdate,        x.is_id_auth ='1'; . 另外一个方面，临时表可以对需要更新的数据做备份，如果发现数据更新错误或者时间，可以回滚。如对需要更新的数据，先创建一个临时备份表出来，这样的话，如果更新失败也可以回滚： 代码: create table tmp_table as select id,name,address from test_table where ……; update test_table t set name=?,address=?   where id in (select id from tmp_table); . 或者 --where exists (select null from tmp_table tmp where tmp.id=t.id) 当然，如果临时表的数据量也很大的话，也可以与方法1结合，在临时表中做循环，如 for c_usr in (select id from tmp_table t) loop 其它很多小技巧，如断点继续（也就是更新失败后，不用重新开始，从失败点继续更新）。采用方法1的PL/SQL脚本很好实现，或者结合临时表，在临时表中增加一个有序列性质的列，从小序列开始往大序列更新，记录更新到的序列号即可。 怎么对IN子查询使用绑定变量 在实际使用中，经常会有带in的子查询，如where id in (1,2,3)这样的情况，但是如果很多这样的语句在数据库中出现，将引起数据库的大量硬解析与共享池SQL碎片。所以，在实际应用中，可以采用其他方法，将这些in list给绑定起来。 如果需要绑定in list，首先，需要创建两个类型(type)： 针对数据类型的 CREATE OR REPLACE TYPE NUMTABLETYPE as table of number; 针对字符串类型的(每个list的单元大小不要超过1000字节) create or replace type vartabletype as table of varchar2(1000); 然后创建两个相关的函数 数字列表函数 代码: create or replace function str2numList( p_string in varchar2 ) return numTableType as v_str long default p_string || ','; v_n number; v_data numTableType := numTableType(); begin     loop     v_n := to_number(instr( v_str, ',' ));     exit when (nvl(v_n,0) = 0); v_data.extend; v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1))); v_str := substr( v_str, v_n+1 ); end loop;     return v_data; end; . 字符列表函数 代码: create or replace function str2varList( p_string in varchar2 ) return VarTableType as v_str long default p_string || ','; v_n varchar2(2000); v_data VarTableType := VarTableType(); begin     loop         v_n :=instr( v_str, ',' );     exit when (nvl(v_n,0) = 0); v_data.extend;     v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1))); v_str := substr( v_str, v_n+1 );     end loop;     return v_data; end; . 代码: create or replace function str2numList( p_string in varchar2 ) return numTableType as v_str long default p_string || ','; v_n number; v_data numTableType := numTableType(); begin     loop     v_n := to_number(instr( v_str, ',' ));     exit when (nvl(v_n,0) = 0); v_data.extend; v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1))); v_str := substr( v_str, v_n+1 ); end loop;     return v_data; end; . 字符列表函数 代码: create or replace function str2varList( p_string in varchar2 ) return VarTableType as v_str long default p_string || ','; v_n varchar2(2000); v_data VarTableType := VarTableType(); begin     loop         v_n :=instr( v_str, ',' );     exit when (nvl(v_n,0) = 0); v_data.extend;     v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1))); v_str := substr( v_str, v_n+1 );     end loop;     return v_data; end; . 创建之后，我们就可以采用如下的方式来使用in list的绑定了。如可以采用如下的三种方案 代码: SELECT /*+ ordered use_nl(a,u) */ id, user_id, BITAND(promoted_type,4) busauth from table(STR2NUMLIST(:bind0)) a, bmw_users u where u.user_id = a.column_value SELECT  /*+ leading(a) */ id, user_id, BITAND(promoted_type,4) busauth from bmw_users u where user_id in (select * from table(STR2NUMLIST(:bind0)) a); SELECT  /*+ index(bmw_users UK_BMW_USERS_USERID) */ id, user_id from bmw_users where user_id in (SELECT * FROM THE (SELECT CAST(STR2NUMLIST(:bind0) AS NUMTABLETYPE) FROM dual) WHERE rownum<1000) 在如上的方案中，以上语句中的hint提示，是为了稳定执行计划，防止Oracle对in list的错误估计而导致走hash连接。一般建议采用第一种方法，比较简单可靠并且可以指定稳定的计划。但是要求数据库的版本比较高，在老版本中(8i)，可能只能采用第三种方法。总的来说，1、2两种方法比3要少6个逻辑读左右。如： 代码: SQL> SELECT /*+ ordered use_nl(a,u) */ id, user_id   2   from table(STR2NUMLIST('1,2,3')) a, 3   bmw_users u   4*  where u.user_id = a.column_value Execution Plan

使用论坛的出售功能，需要者下载。

palvin

好东东，我也来顶一把

palvin

原帖由 hema 于 2007-9-8 22:39 发表
秋葵软件采用C++开发语言，C/C++是当今世界开发的主流语言，70-90%的开发人员在使用它，几乎所有大型、关键系统：操作系统、军事系统、计算机语言系统本身都毫不例外地选择它。C/C++成为计算机主流语言不是一件偶然的 ...

广告不要发到这里吧。

chenzgstar

JIU XU YAO ZHE GE

chenzgstar

，为什么总告诉我密码不正确，word文档无法打开呢？我一个字母一个字母敲得，还告诉密码不正确。

chenzgstar

打开了，原来都大写

gaoyiming

真是不好意思
非常对不起

skyczn

小高，我支持你5点吧，还是很心疼的，你可是大财主，看来贫富差距越来越大，社会越来越不和谐了。

happy-onion

谢谢，全力支持楼主

6721170

支持，和谐呀，和谐，好

6721170

好东东，我也来顶一把

gaoyiming

你没有购买吧
我这个是按照小陈的意思来测试的 论坛的出售功能的

achieving123

好东东，我也来顶一把

hurler

为什么要售价？

hurler

这得回多少帖子啊
建议给初学者的东西
或新来的取消某些限制

hurler

继续努力吧！！！

hurler

算了，不要了！！！！

hurler

怎么努力只扣除了却下不了？？？

hurler

下不了啊，这会惨了

Ravine2008

这是一个好东西,
谢谢了.