oracle11g - Why do these 2 oracle functions perform differently? -

oracle11g - Why do these 2 oracle functions perform differently? -

i wrote function in oracle convert ip addresses integers. seemed slow. wrote sec function same thing faster. unfortunately ended slower , not know why.

original function;

function get_ip_integer ( ip_in in varchar2 ) homecoming number dot_counter integer; current_dot integer; last_dot integer := 1; current_integer integer := 0; output_integer integer := 0; begin dot_counter in 1..3 loop current_dot := instr(ip_in,'.',last_dot); current_integer := to_number(substr(ip_in,last_dot,current_dot - last_dot)); last_dot := current_dot + 1; case dot_counter when 1 current_integer := current_integer * 16777216; when 2 current_integer := current_integer * 65536; when 3 current_integer := current_integer * 256; end case; output_integer := output_integer + current_integer; current_integer := 0; end loop; current_integer := to_number(substr(ip_in,last_dot)); output_integer := output_integer + current_integer; homecoming output_integer; end get_ip_integer;

it picks apart , works well. thought improve wrote this;

function get_ip_integer1 ( ip_in in varchar2 ) homecoming number octet_counter integer; current_integer integer := 0; output_integer integer := 0; begin octet_counter in 1..4 loop current_integer := to_number(regexp_substr(ip_in,'\w+',1,octet_counter)); current_integer := power(2,24 - ((octet_counter-1)*8)) * current_integer; output_integer := output_integer + current_integer; end loop; homecoming output_integer; end get_ip_integer1;

this works seems run much (about twice long) slower. assume either powerfulness function or regexp_substr pig. hoping more knowledge might point out which, and/or why.

here little piece of knowledge: in oracle 11g, have hierarchical pl/sql profiler. show pl/sql spending time.

oracle oracle11g