老方块Oracle--数值类型性能考虑

我们在设计数据库表，或者在使用SQL，写程序时都会经常用到数值类型。比如常见的number、int、float。

float是浮点类型,也属于数值类型，我们最常用的是number类型。

他的格式是number(p,s)p是精度，代表有效位，s刻度代表精确到小数点前/后多少位。

e.g.

如果输入数据是15.123,那么number(4,2)的输出值==15.12

如果输入数据是12345.678,那么number(5,-2)的输出值==12300.

 

很多人只知道数值类型，但是很多人对他的性能并没有多少了解,也没注意到一些重要的细节。

有些数值，对精度要求不高。刘翔的110米栏，世界纪录是多少？12.81，跑步的类型可以设置成number(4,2)

p=4，精度，也就是有效位
s=2，可以保留两位小数点

 

由精度引申出两种重要的数值类型：number和float类型。而int是number的特例。

到了oracle 10g新引进了两种数值类型binary_float 和binary_double，都是浮点类型

 

除了数据准确性外，它对存储空间有影响。我们说精度越低,存储的空间越小,性能越高。但是，空间节约对于性能的提高是非常有限的。浮点类型还有更重要的因素来影响性能

浮点运算机制跟number的运算机制是完全不同的，浮点的性能要高很多

从浮点的内部机制来说起，浮点数是一个有理数子集中一个数的数字表示，通常用于在计算机上近似一个任意的实数。硬件的计算速度比软件(软件的计算，其实也是靠硬件来计算的)，那CPU来计算数值快还是ORACLE软件来计算数值快？当然是cpu。

那我们的浮点类型 和number类型是用什么来计算的？浮点类型是cpu，number是oracle来计算的。

浮点是硬件来计算的，NUMBER类型，是由软件来计算的，知道两者区别了吧。而且他们两者的性能差至少1个数量级，如果大家不信，那我就给大家来做做实验,让大家相信

第1步：
create table number_tuning
( num_type     number,
  float_type   binary_float,
  double_type  binary_double
)
/

先创建了测试表number_tuning,表中有3个字段,其中一字段是NUMBER类型，另两字段分别是浮点类型,上面创建的表有3个字段吧,分别是1个number类型，和2个浮点类型

第2步：
insert /*+ APPEND */  into number_tuning
select rownum, rownum, rownum
  from all_objects
/
commit;

向表中插入初始化数据,插入的值都是rownum,也就是每行的行号.接下来我们开始来测试性能,看哪种类型计算快，我们是不是要设置计时器？

第3步：
set timing on
select sum(ln(num_type)) from number_tuning;
select sum(ln(float_type)) from number_tuning;
select sum(ln(double_type)) from number_tuning;

用了一个合计的函数SUM和一个对数函数LN来计算数值类型,计算3种数值类型,看看,这3条语句分别计算了多少时间

number-->1.81,   float-->0.21,   double-->0.03

浮点类型的运算，比number要快很多,实验的效果明显，长见识啊

 

问，我们的DBA没有注意这些细节，如果我们的业务表已经设置成NUMBER类型了，我又不想把表结构的NUMBER类型转换成浮点类型，又想提高计算效率，该怎么办？

答：

第4步

select sum(ln(cast( num_type as binary_double ) )) 
from number_tuning;

计算的列是number类型，但是使用了cast函数，将number类型转成浮点类型了，cast函数可以强制在各种类型间转换

用来0.09s（原来是1.81）

那我们能不能像浮点类型一样，也启用硬件运算呢？Oracle是不是该提供一种浮点机制的整型呢？

10g新特征，pls_integer类型。如果我们需要整型字段，pls_integer类型可以提高性能，因为pls_integer类型是硬件计算的

 

求第二大值（四种方法）SQL应该怎么写？

主要要学的是思路

第1步：
create table t (x int);  
insert into t values(1); 
insert into t values(3); 
insert into t values(5); 
insert into t values(7); 
insert into t values(9); 
commit;

第二大是 7

select x
    from ( select x, rownum r
             from ( select x from t order by x desc )
                    where rownum <= 2 )
   where r = 2;

 

7求出来了。

我们看最外围的SELECT,我们叫主查询(父查询)，主查询里面的所有SELECT，我们叫子查询

生成顺序，先子后父，1,2,3。子查询是默认的NO_EXPAND模式，NO_EXPAND是SQL的提示

子查询按类型分：
o 标准子查询
o 关联子查询
o inline view

 

标准子查询或者称为标量子查询：没有关联别SELECT语句的子查询。

里面两个SELECT的WHERE条件有没有关联到其他SELECT，其中一个甚至没有where。

中间层的子查询被where rownum <=2过滤了

ORACLE中，ROWNUM像一个隐藏的字段。记录的是行数。
SELECT ROWNUM,A.* FROM TABLE A 就出来了 
可以查第几条数据
注意;rownum，见"1"眼定终身。条件里只有包含1，才能运行，否则不能
例如下面的错误用法：
select x from t order by x desc ) where rownum >= 5；——>错误
因为没有包含1

第2种方法:既然是求第2大的值，最大值给T掉，那第2大的值就是老大

select max(x) 
 from t 
 where x < (select max(x) from t) 
 / 

运行效果与上面求7类似

第3种方法:这方法不推荐，性能不好，又不好掌握。

select *   
 from t   
 where (select count(*)  
          from t t2   
         where t2.x > t.x ) = 1   
 / 

已经学过了标准子查询，接下来学习下关联子查询和inline view
inline view(内联视图)：from后的子查询我们有个称号叫inline view

inline view相当于是一个临时表

关联子查询的定义：如果子查询中的条件跟其他高辈分的SELECT有关联，就变成关联子查询了

大家看，这个WHERE条件，是t2的x跟父查询的x关联了，所以叫关联子查询
关联子查询在求出最里层的子查询的结果集时，必然会关联到关联的父查询

大家看，父查询中5个值里，只有7满足子查询中有一个值大于他
第4种方法：

select x 
  from ( select x, row_number() over ( order by x desc ) r 
           from t 
     ) 
 where r = 2 
/ 

row_number() over ( order by x desc )这是分析函数的格式

分析函数 OVER (PARTITION BY X ORDER BY Y ) RN，后面RN是分析函数的别名，这里OVER里面，可以设置分组和排序，学习下分析函数，非常有用，性能也相对较好