[20220708]PLSQL – choosing the BEST data type.txt

https://connor-mcdonald.com/2022/07/08/pl-sql-choosing-the-best-data-type/

--//重复测试:

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

2.测试脚本:

$　cat ppp.txt
create or replace procedure ppp is
  x &&1 := 0;
begin
  for i in  1 .. 100000000 loop
     x := x + 1;
  end loop;
end;
/

$ cat ttt.txt
set timing  off
@ppp.txt &&1
set timing  on
exec ppp
exec ppp
exec ppp
quit

3.测试：
$ echo number pls_integer  integer binary_integer simple_integer | tr ' ' '\n' | xargs -IQ bash -c  "echo Q Q ;sqlplus -s -l scott/book @ttt.txt Q" | egrep "er|Elapsed"
number number
Elapsed: 00:00:03.88
Elapsed: 00:00:04.36
Elapsed: 00:00:04.35
pls_integer pls_integer
Elapsed: 00:00:01.76
Elapsed: 00:00:01.46
Elapsed: 00:00:01.45
integer integer
Elapsed: 00:00:06.40
Elapsed: 00:00:06.16
Elapsed: 00:00:06.17
binary_integer binary_integer
Elapsed: 00:00:01.48
Elapsed: 00:00:01.42
Elapsed: 00:00:01.43
simple_integer simple_integer
Elapsed: 00:00:01.55
Elapsed: 00:00:01.25
Elapsed: 00:00:01.25

$ echo number pls_integer  integer binary_integer simple_integer | tr ' ' '\n' | xargs -IQ bash -c  "echo Q Q ;sqlplus -s -l scott/book @ttt.txt Q" | egrep "er|Elapsed"| awk '{print $2}'| paste -d"\t" - - - -
number  00:00:03.68     00:00:03.41     00:00:03.40
pls_integer     00:00:01.75     00:00:01.46     00:00:01.45
integer 00:00:06.81     00:00:06.59     00:00:06.58
binary_integer  00:00:01.73     00:00:01.43     00:00:01.42
simple_integer  00:00:01.49     00:00:01.26     00:00:01.25

--//作者的测试如下：
number pls_integer  integer binary_integer simple_integer
  1.74       00.57   03.72  00.58          0.56

--//可以看出simple_integer,pls_integer,binary_integer快一些。number，integer最慢。
--//具体说明参考链接https://connor-mcdonald.com/2022/07/08/pl-sql-choosing-the-best-data-type/