Charles Hooper wrote:
> Maxim Demenko wrote:
>> Sorry, could not resist ;-)
>>
>> SQL> declare
>> 2 s number;
>> 3 c number;
>> 4 begin
>> 5
>> 6 s:=dbms_utility.get_time;
>> 7 for i in 1..100000 loop
>> 8 SELECT SUM(DECODE(SUBSTR('562225622256222',ROWNUM,1),'2',1,0))
>> 9 into c
>> 10 FROM DUAL CONNECT BY LEVEL<=15;
>> 11 end loop;
>> 12 s := dbms_utility.get_time -s;
>> 13 dbms_output.put_line('SUBSTR/DECODE/CONNECT BY time: '||
>> trunc(s/100));
>> 14
>> 15 s:=dbms_utility.get_time;
>> 16 for i in 1..100000 loop
>> 17 SELECT SUM(SIGN(INSTR('562225622256222','2',1,ROWNUM)))
>> 18 into c
>> 19 FROM DUAL CONNECT BY LEVEL<=15;
>> 20 end loop;
>> 21 s := dbms_utility.get_time -s;
>> 22 dbms_output.put_line('SIGN/INSTR/CONNECT BY time: '||
>> trunc(s/100));
>> 23
>> 24 s:=dbms_utility.get_time;
>> 25 for i in 1..100000 loop
>> 26 select length('562225622256222') -
>> length(replace('562225622256222','2'))
>> 27 into c
>> 28 from dual;
>> 29 end loop;
>> 30 s := dbms_utility.get_time -s;
>> 31 dbms_output.put_line('LENGTH/REPLACE time: '|| trunc(s/100));
>> 32
>> 33 s:=dbms_utility.get_time;
>> 34 for i in 1..100000 loop
>> 35 select length(regexp_replace('562225622256222','[^2]',''))
>> 36 into c
>> 37 from dual;
>> 38 end loop;
>> 39 s := dbms_utility.get_time -s;
>> 40 dbms_output.put_line('REGEXP_REPLACE time: '|| trunc(s/100));
>> 41 end;
>> 42 /
>> SUBSTR/DECODE/CONNECT BY time: 17
>> SIGN/INSTR/CONNECT BY time: 18
>> LENGTH/REPLACE time: 13
>> REGEXP_REPLACE time: 13
>>
>> PL/SQL procedure successfully completed.
>>
>>
>> Best regards
>>
>> Maxim
>
> Nice test!
>
> SUBSTR/DECODE/CONNECT BY time: 5
> SIGN/INSTR/CONNECT BY time: 5
> LENGTH/REPLACE time: 1
> REGEXP_REPLACE time: 1
>
> The above test results are from Oracle 10.2.0.2 running on Windows 2003
> x64
>
> Inefficiency of SQL is not a problem, it just means that you need a
> bigger CPU :-)
>
> Actually, the test proves a valid point - just because it works, does
> not mean that it should be used. Thanks for taking the time to build
> the test.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
If you have even a tinge of envy I suggest responding with:
- Write it out to a file using UTL_FILE
- Read it back in using DBMS_SCHEDULER
- Creating it as a wrapped package using DBMS_DDL
- Executing it using DBMS_SQL.
Use the PL/SQL data types POSITIVEN and NATURALN to confuse almost
everyone as to your actual intent. ;-)
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Jan 23 2007 - 14:50:12 CST
