-- 一个关于效率问题的测试2 --

-- 测试1：



eygle@SZTYORA> select count(*) from mobilefrends where mobile='13897188299';



  COUNT(*)

----------

       101



eygle@SZTYORA> @c:\mystat.sql "redo size"



NAME                           VALUE

------------------------- ----------

redo size                     197660



eygle@SZTYORA> exec addfriendsfast1



('13897188299','13511456790,13877776666,13900124444,13566778899,15077094125,15900897764,13690145561,15941307721,15879421340,13244791488,15169177743,136911475



39');



PL/SQL 过程已成功完成。



eygle@SZTYORA> @c:\mystat2.sql "redo size"



NAME                               V DIFF

------------------------- ---------- --------------------------------

redo size                     206880            9,220



eygle@SZTYORA> select count(*) from mobilefrends where mobile='13897188299';



  COUNT(*)

----------

       113



-- 可以看到插入12条记录，生成约9220 Byte 的redo日志



-- 试想一下：mobilefrends现在有近一千万条记录，若每插入一条记录，要先判断此记录在mobilefrends表中是否已经存在，

-- 那么当我传过来300条记录时，就是执行“ select count(1) into v_cnt from mobilefrends where mobile=v_mobile and frendmobile=v_frendmobile; ”这个语句300次

-- 此方法显然是不可取的



-- 再看方法二(利用临时表)：

create global temporary table mobilefrends_mem(frendmobile varchar2(20))

on commit delete rows;



create or replace procedure addfriendsfast2(

  v_mobile in varchar2, -- 是宿主手机号

  v_list in clob)       -- 是传过来的一批、将要将其添加为好友的手机号手机号，以逗号隔开

is

  v_loop number(18,0);

  v_frendmobile mobilefrends.frendmobile%type;

  v_list2 clob;

  v_cnt number(18,0);

begin

  select ','||v_list||',', length(v_list)-length(replace(v_list,',',''))+2 into v_list2, v_loop from dual;

  for i in 2..v_loop loop

    insert into mobilefrends_mem(frendmobile)

    select substr(v_list2,instr(v_list2,',',1,i-1)+1, instr(v_list2,',',1,i)-instr(v_list2,',',1,i-1)-1) as frendmobile

     from dual;

  end loop;

  insert into mobilefrends(mobile,frendmobile,cdate,udate)

  select distinct v_mobile, t1.frendmobile, sysdate, sysdate -- 去重

  from mobilefrends_mem t1

  where t1.frendmobile not in (select t2.frendmobile from mobilefrends t2 where t2.mobile=v_mobile )

    and t1.frendmobile is not null -- 排除空值

    and t1.frendmobile<>v_mobile;  -- 自己不能将自己添加为好友

  commit;

end;

/



-- 测试2：



eygle@SZTYORA> select count(*) from mobilefrends where mobile='13898105155';



  COUNT(*)

----------

         9



eygle@SZTYORA> @c:\mystat.sql "redo size"



NAME                           VALUE

------------------------- ----------

redo size                     206880



eygle@SZTYORA> exec addfriendsfast1



('13898105155','13511456790,13877776666,13900124444,13566778899,15077094125,15900897764,13690145561,15941307721,15879421340,13244791488,15169177743,136911475



39');



PL/SQL 过程已成功完成。



eygle@SZTYORA> @c:\mystat2.sql "redo size"



NAME                               V DIFF

------------------------- ---------- --------------------------------

redo size                     216072            9,192



eygle@SZTYORA> select count(*) from mobilefrends where mobile='13898105155';



  COUNT(*)

----------

        21



-- 可以看到插入12条记录，生成约9192 Byte 的redo日志



-- 再看插入24条记录时两种方法的执行时间：

eygle@SZTYORA> set timing on;



eygle@SZTYORA> exec addfriendsfast1



('13898117070','13511456790,13877776666,13900124444,13566778899,15077094125,15900897764,13690145561,15941307721,15879421340,13244791488,15169177743,136911475



39,13299472833,13390907765,15594309191,15899997819,13444456672,13699954646,13766889910,15001438871,15019181663,13941566788,13177455468,13301522233');



PL/SQL 过程已成功完成。



已用时间:  00: 00: 00.09



eygle@SZTYORA> exec addfriendsfast2



('13698117899','13511456790,13877776666,13900124444,13566778899,15077094125,15900897764,13690145561,15941307721,15879421340,13244791488,15169177743,136911475



39,13299472833,13390907765,15594309191,15899997819,13444456672,13699954646,13766889910,15001438871,15019181663,13941566788,13177455468,13301522233');



PL/SQL 过程已成功完成。



已用时间:  00: 00: 00.06



-- 可以看到，插入24条记录，方法二比方法一要快三分之一倍，随着插入记录的增多，这个比率将会有更大的提高(您可以多测试一下)



-- 再看方法三(利用connect by 行转列)：

create or replace procedure addfriendsfast3(

  v_mobile in varchar2, -- 是宿主手机号

  v_list in clob)       -- 是传过来的一批、将要将其添加为好友的手机号手机号，以逗号隔开

is

begin

    insert into MobileFrends(mobile, frendmobile, cdate, udate)

    with a as( select distinct to_char(substr(','||v_list||',',instr(','||v_list||',',',',1,level)+1,

                                       instr(','||v_list||',',',',1,level+1)-instr(','||v_list||',',',',1,level)-1)) frendmobile

                 from dual

              connect by

                level <= length(','||v_list||',') - length(replace(','||v_list||',', ',', ''))-1 )

    select v_mobile, a.frendmobile, sysdate, sysdate

      from a

     where not exists (select 1 from MobileFrends b where b.mobile=v_mobile and b.frendmobile=a.frendmobile)

       and a.frendmobile is not null

       and a.frendmobile<>v_mobile; -- 自己不能将自己添加为好友

  commit;

end;

/



eygle@SZTYORA> exec addfriendsfast2



('13914226313','13511456790,13877776666,13900124444,13566778899,15077094125,15900897764,13690145561,15941307721,15879421340,13244791488,15169177743,136911475



39,13299472833,13390907765,15594309191,15899997819,13444456672,13699954646,13766889910,15001438871,15019181663,13941566788,13177455468,13301522233');



PL/SQL 过程已成功完成。



已用时间:  00: 00: 00.23



eygle@SZTYORA> exec addfriendsfast3



('13698117899','13511456790,13877776666,13900124444,13566778899,15077094125,15900897764,13690145561,15941307721,15879421340,13244791488,15169177743,136911475



39,13299472833,13390907765,15594309191,15899997819,13444456672,13699954646,13766889910,15001438871,15019181663,13941566788,13177455468,13301522233');



PL/SQL 过程已成功完成。



已用时间:  00: 00: 00.09



eygle@SZTYORA> exec addfriendsfast2



('13914226314','13511456790,13877776666,13900124444,13566778899,15077094125,15900897764,13690145561,15941307721,15879421340,13244791488,15169177743,136911475



39,13299472833,13390907765,15594309191,15899997819,13444456672,13699954646,13766889910,15001438871,15019181663,13941566788,13177455468,13301522233');



PL/SQL 过程已成功完成。



已用时间:  00: 00: 00.23



eygle@SZTYORA> exec addfriendsfast3



('13698117889','13511456790,13877776666,13900124444,13566778899,15077094125,15900897764,13690145561,15941307721,15879421340,13244791488,15169177743,136911475



39,13299472833,13390907765,15594309191,15899997819,13444456672,13699954646,13766889910,15001438871,15019181663,13941566788,13177455468,13301522233');



PL/SQL 过程已成功完成。



已用时间:  00: 00: 00.09





-- 还有：由于方法一采用的是循环判断插入方法，所以同一批记录的cdate、udate并不相同，但方法二和方法三不会出现这种情况！



-----------------------------------------------------------------------------------------------------------------------------------



-- 附：生产库运行版的真实存储过程如下(还考虑了frendname字段)：



-- 用序列，用于去除重复手机号的数据

-- (如果手机号重复，以最后一个输入的手机号为准，例如：13691147539:luoyoumou1,13691147539:luoyoumou2，则真正插入原表的记录应该是13691147539:luoyoumou2)



CREATE GLOBAL TEMPORARY TABLE MobileFrends_Mem(

frendmobile varchar2(20) not null,

frendname varchar2(100) null

) on commit delete rows;



create or replace procedure add_friends(

  v_mobile in varchar2,

  v_list in clob)

as

  v_mobile2 mobilefrends.mobile%type;

begin

  -- v_list 传入的格式: 手机号:名称\n手机号:名称\n手机号:名称\n....

  v_mobile2 := trim(v_mobile);

  if v_mobile2 is not null and lengthb(v_mobile2)<=20 then

    insert into MobileFrends_Mem(frendmobile, frendname)

    with a as (

      select to_char(substr(','||v_list||',',instr(','||v_list||',',',',1,level)+1,

                    instr(','||v_list||',',',',1,level+1)-instr(','||v_list||',',',',1,level)-1)) frendmobile

        from dual

     connect by

       level <= length(','||v_list||',')-length(replace(','||v_list||',',',',''))-1),

         b as (select trim(substr(a.frendmobile,1,instr(a.frendmobile,':')-1)) as frendmobile, 

                      trim(substr(a.frendmobile,instr(a.frendmobile,':')+1)) as frendname

                 from a )

    select b.frendmobile, b.frendname frendname -- 考虑手机号前后带有空格的情况

     from b

    where lengthb(b.frendmobile)<=20 and lengthb(b.frendname)<=100

      and b.frendmobile is not null

      and b.frendmobile<>v_mobile2; -- 不能将自己添加为自己的好友



    -- 去除重复数据：

    delete from MobileFrends_mem a1

     where rowid not in (select max(rowid) from MobileFrends_mem a2 group by a2.frendmobile);



    merge into MobileFrends a

    using MobileFrends_mem b on (a.mobile=v_mobile2 and a.frendmobile=b.frendmobile)

    when matched then

    update set a.frendname=b.frendname,a.udate=sysdate

    where a.frendname<>b.frendname 

    when not matched then

    insert(mobile,frendmobile,frendname,cdate,udate)

    values(v_mobile2,b.frendmobile,b.frendname,sysdate,sysdate);

    commit;

  end if;

exception when others then

  rollback;

end;

/



exec add_friends('13691147539','15077094125:程彩翼,13699154724:罗有勇,15077498871:小明');



select * from mobilefrends

where mobile='13691147539';



exec add_friends('13691147539','15077094125:程彩翼,13699154724:罗有勇,15077498871:小明,13344778891:李小花,15077094125:老婆');

------------------------------------ 搭建测试环境：-------------------------------------

---------- 测量 redo 的脚本 ------------------------------------------------------------



-- 要查看生成的redo量相当简单，我们可以使用SQL*Plus的内置特性AUTOTRACE。不过AUTOTRACE只能用于简单的DML，对其他的操作就力所不能及了，

-- 例如：它无法查看一个存储过程调用做了什么。为此，我们需要访问两个动态性能视图：

-- *(1) V$MYSTAT，其中有会话的统计信息。

-- *(2) V$STATNAME，这个视图能告诉我们V$MYSTAT中的每一行表示什么(所查看的统计名)。



-- 因为为了测试其性能的需要，我经常要做这种测量，所以使用了两个脚本，分别为mystat和mystat2(我将这两个脚本都保存在C盘根目录下)。

-- mystat.sql脚本把我们感兴趣的统计初始值(如redo大小)保存在一个SQL*Plus变量中：

set verify off

column value new_val V

define S="&1"



set autotrace off;

select a.name, b.value

from v$statname a, v$mystat b

where a.statistic# = b.statistic#

and lower(a.name) like '%' || lower('&S')||'%'

/



-- mystat2.sql脚本只是打印出该统计的初始值和结束值之差：

set verify off

select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff

from v$statname a, v$mystat b

where a.statistic# = b.statistic#

and lower(a.name) like '%' || lower('&S')||'%'

/



---------------------------------------------------------------------------------------



eygle@SZTYORA> select * from (select distinct mobile from mobilefrends order by sys_guid() ) where rownum<10;



MOBILE

----------------------------------------

13914226313

13499997213

13898105155

13626495385

13329559458

13132056780

13897188299

13611241935

13547119893



已选择9行。



------------------------------------------------------------------------------------------------------------



eygle@SZTYORA> set timing on

eygle@SZTYORA> select mobile, frendmobile from mobilefrends where mobile=13611241935;



MOBILE                                   FRENDMOBILE

---------------------------------------- ----------------------------------------

13611241935                              13511456790

13611241935                              13772394933

13611241935                              13616260471

13611241935                              13233556509

13611241935                              13601951317

13611241935                              13564595719

13611241935                              13608378935

13611241935                              13637199598

13611241935                              13503482506

13611241935                              13705216597

13611241935                              13853669951

13611241935                              13716309920

13611241935                              13111407424

13611241935                              13122505326

13611241935                              13846410891

13611241935                              13562543709

13611241935                              13528888145

13611241935                              13603215533



已选择18行。



已用时间:  00: 00: 02.09



执行计划

----------------------------------------------------------

Plan hash value: 395359246



----------------------------------------------------------------------------------

| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |              |   413 |  9912 | 10689   (5)| 00:02:09 |

|*  1 |  TABLE ACCESS FULL| MOBILEFRENDS |   413 |  9912 | 10689   (5)| 00:02:09 |

----------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   1 - filter(TO_NUMBER("MOBILE")=13611241935)



Note

-----

   - dynamic sampling used for this statement





统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

      37479  consistent gets

          0  physical reads

          0  redo size

       1139  bytes sent via SQL*Net to client

        396  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         18  rows processed



eygle@SZTYORA> select mobile, frendmobile from mobilefrends where mobile='13611241935';



MOBILE                                   FRENDMOBILE

---------------------------------------- ----------------------------------------

13611241935                              13111407424

13611241935                              13122505326

13611241935                              13233556509

13611241935                              13503482506

13611241935                              13511456790

13611241935                              13528888145

13611241935                              13562543709

13611241935                              13564595719

13611241935                              13601951317

13611241935                              13603215533

13611241935                              13608378935

13611241935                              13616260471

13611241935                              13637199598

13611241935                              13705216597

13611241935                              13716309920

13611241935                              13772394933

13611241935                              13846410891

13611241935                              13853669951



已选择18行。



已用时间:  00: 00: 00.01



执行计划

----------------------------------------------------------

Plan hash value: 3011189231



------------------------------------------------------------------------------------

| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |                 |   413 |  9912 |     4   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| PK_MOBILEFRENDS |   413 |  9912 |     4   (0)| 00:00:01 |

------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   1 - access("MOBILE"='13611241935')



Note

-----

   - dynamic sampling used for this statement





统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          5  consistent gets

          0  physical reads

          0  redo size

       1139  bytes sent via SQL*Net to client

        396  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         18  rows processed



-- 可以看到 第一个查询，其条件为 where mobile=13611241935　，执行的是全表扫描，其执行时间为2.09秒；

--          第二个查询，其条件为 where mobile='13611241935'　，执行主键索引扫描，其执行时间为0.01秒；

-- 第二个查询条件仅比第一个查询条件多了两个单引号(')，其效率却比第一个查询的提高了208倍。

-- 这是因为 mobile字段是字符串字段，当执行 where mobile=13611241935 会发生mobile隐式类型转换(将varchar2转变为整型，然后再与13611241935比较)，

-- 导致无法采用索引；而当执行 where mobile='13611241935'，不会有隐式类型转换的情况！

-------------------------------------------------------------------------------------------------------



-- 现在，有开发人员写了一个批量插入 mobilefrends 表的存储过程： ---

create or replace procedure addfriendsfast1(

  v_mobile in varchar2, -- 是宿主手机号

  v_list in clob)       -- 是传过来的一批、将要将其添加为好友的手机号手机号，以逗号隔开

is

  v_loop number(18,0);

  v_frendmobile mobilefrends.frendmobile%type;

  v_list2 clob;

  v_cnt number(18,0);

begin

  select ','||v_list||',', length(v_list)-length(replace(v_list,',',''))+2 into v_list2, v_loop from dual;

  for i in 2..v_loop loop

    select substr(v_list2,instr(v_list2,',',1,i-1)+1, instr(v_list2,',',1,i)-instr(v_list2,',',1,i-1)-1) into v_frendmobile from dual;

    if v_frendmobile is not null and v_frendmobile<>v_mobile then -- 自己不能将自己添加为好友

      select count(1) into v_cnt from mobilefrends where mobile=v_mobile and frendmobile=v_frendmobile;

      if v_cnt=0 then

        insert into mobilefrends(mobile,frendmobile,cdate,udate) values(v_mobile,v_frendmobile,sysdate,sysdate);

      end if;

    end if;

  end loop;

  commit;

end;

/