[20220104]in list 幾種寫法效能測試.txt

[20220104]in list 幾種寫法效能測試.txt

--//以前寫過幾種in list的寫法,從來沒有測試過這幾種方法的效能測試看看.

1.環境:
SCOTT@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table job_times (sid number, time_ela number,method varchar2(20));

2.in list測試例子:
--//注:我的測試僅僅測試number型別列表,主要我們生產系統用的這類也最多,另外就是xmltable的字元列表估計比較麻煩.
--//也許下篇測試看看.

--//1.使用str2numlist,str2varlist函式,原始碼在網上很容易找到.
CREATE OR REPLACE TYPE numtabletype AS TABLE OF NUMBER
/

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;
/

--//select * from table (cast(STR2NURLIST(:st2) as numtabletype));

--//2.使用xmltable,可能僅僅適合11g:
SQL> var a varchar2(60);
SQL> exec :a := '10,20';
PL/SQL procedure successfully completed.

SQL> select * from dept where deptno in  (select (column_value).getnumberval() from xmltable(:a));
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS

--//3.正規表示式例子:

SELECT *
  FROM dept
 WHERE deptno IN
 ( SELECT TO_NUMBER (REGEXP_SUBSTR ( '10,20' ,'[^,]+' ,1 ,LEVEL)) FROM DUAL CONNECT BY REGEXP_SUBSTR ( '10,20' ,'[^,]+' ,1 ,LEVEL) IS NOT NULL);

3.測試指令碼:

$ seq -f "%-1.0f" 1e9 90000011 1e10|wc
    100     100    1100

$ seq -f "%-1.0f" 1e9 90000011 1e10 | paste -sd',' >|aa.txt

$ cat m16.txt
set verify off
set linesize 32767
variable vmethod varchar2(20);
exec :vmethod := '&&2';
insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:vmethod) ;
commit ;

declare
    v_string varchar2(4000);
        l_count PLS_INTEGER;
begin
     v_string := '1000000000,1090000011,...,9910001089';
     for i in 1 .. &&1 loop
         select count(*) into l_count from (select * from table (cast(str2numlist(v_string) as numtabletype)));
--         select count(*) into l_count from (select (column_value).getnumberval() from xmltable(v_string));
--       select count(*) into l_count from (select to_number (regexp_substr ( v_string ,'[^,]+' ,1 ,level)) from dual connect by regexp_substr ( v_string ,'[^,]+' ,1 ,level) is not null);
--       DBMS_OUTPUT.PUT_LINE (l_count);
    end loop;
end ;
/
update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method=:vmethod;
commit;
set linesize 270
quit

--//v_string 的值從前面的aa.txt複製過來,我截斷了。

4.測試:
--//在測試開始前我猜測使用正規表示式最慢，使用函式應該最快。

$ zzdate ;sqlplus -s -l scott/book @m16.txt 1e6  str2numlist >/dev/null;zzdate
trunc(sysdate)+16/24+33/1440+03/86400 == 2022/01/04 16:33:03 == timestamp'2022-01-04 16:33:03'
trunc(sysdate)+16/24+40/1440+16/86400 == 2022/01/04 16:40:16 == timestamp'2022-01-04 16:40:16'

$ zzdate ;sqlplus -s -l scott/book @m16.txt 1e6  xmltable     >/dev/null;zzdate
trunc(sysdate)+16/24+41/1440+00/86400 == 2022/01/04 16:41:00 == timestamp'2022-01-04 16:41:00'
trunc(sysdate)+17/24+12/1440+47/86400 == 2022/01/04 17:12:47 == timestamp'2022-01-04 17:12:47'

$ zzdate ;sqlplus -s -l scott/book @m16.txt 1e6  regexp_substr>/dev/null;zzdate
trunc(sysdate)+17/24+18/1440+50/86400 == 2022/01/04 17:18:50 == timestamp'2022-01-04 17:18:50'
trunc(sysdate)+00/24+43/1440+43/86400 == 2022/01/05 00:43:43 == timestamp'2022-01-05 00:43:43'

METHOD                 COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
-------------------- ---------- ---------------------- -------------
str2numlist                   1                  43286         43286
xmltable                      1                 190635        190635
regexp_substr                 1                2668927       2668927

--//沒有想到正規表示式執行時間有點誇張，可以明顯看出使用函式str2numlist最快。
--//還可以看出正規表示式是一個很耗CPU資源的操作，一些語句即使出現在select部分，輸出多條記錄對CPU資源但是影響也會很大。