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

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