[20220104]in list 几种写法性能测试.txt
–//以前写过几种in list的写法,从来没有测试过这几种方法的性能测试看看.
1.环境:
SCOTT@book> @ver1
PORT_STRING VERSION BANNER
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)
Original: https://www.cnblogs.com/lfree/p/15765338.html
Author: lfree
Title: [20220104]in list 几种写法性能测试.txt
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/606226/
转载文章受原作者版权保护。转载请注明原作者出处！