DB性能-隐式转换

1        什么是隐式转换

当源数据的类型和目标数据的类型不同的时候，如果没有转换函数，就会发生隐式转换，也称自动转换。当然，

有些情况下有些类型是不可以发生转换的，比如说从DATE类型转换到NUMBER类型就会报错。

2        什么场景会发生隐式转换

在oracle中，如果不同的数据类型之间关联，如果不显式转换数据，则它会根据以下规则对数据进行隐式转换

1)      比较时，一般是字符型转换为数值型，字符型转换为日期型

2)      算术运算时，一般把字符型转换为数值型，字符型转换为日期型

3)      连接时(||)，一般是把数值型转换为字符型,日期型转换为字符型

4)      赋值、调用函数时，以定义的变量类型为准。

 

3        为什么要避免隐式转换

隐式转换的缺点：

1)      使用显示类型转换会让我们的SQL更加容易被理解,也就是可读性更强,但是隐式类型转换却没有这个优点。

2)      隐式类型转换往往对性能产生不好的影响，特别是左值的类型被隐式转为了右值的类型。这种方式很可能使我们本来可以使用索引的而没有用上索引,也有可能会导致结果出错。

3)      隐式类型转换可能依赖于发生转换时的上下文环境，比如to_date(sysdate, fmt),一旦上下文环境改变，很可能我们的程序就不能运行。

4)      隐式类型转换的算法或规则，以后Oracle可能改变，这是很危险的，意味着旧的代码很可能在新的Oracle版本中运行出现问题(性能、错误等)，显示类型转换总是有最高的优先级，所以显示类型转换没有这种版本更替可能带来的问题。

5)      隐式类型转换是要消耗时间的,当然同等的显式类型转换时间也差不多,最好的方法就是避免类似的转换,在显示类型转换上我们会看到，最好不要将左值进行类型转换，到时候有索引也用不上索引，还要建函数索引，索引储存和管理开销增大。

 总结：

Oracle 使用数据类型的优先级来决定隐式类型转换，原则是将优先级低的转换为优先级高的。

隐式转换发生在字段列上时将使索引失效。

 

随着公司业务的数据量的增加，在高并发和大吞吐量的场景下，隐式转换产生的性能问题会被千倍万倍的放大，将很有可能会导致系统崩塌或者交易崩溃，会造成很大的业务经济损失。

 

Oracle使用数据类型的优先级来决定自动类型转换,Oracle类型如下优先：
■ Datetime and interval 类型
■ BINARY_DOUBLE
■ BINARY_FLOAT
■ NUMBER
■ 字符类型
■ 所有其它内置类型

 

 

4        如何判断SQL发生了隐式转换

在编写复杂SQL以后，一般要去走一下执行计划，查看一下整个SQL的性能，比如是否存在全表扫描，笛卡尔积，隐式转换问题。

 

隐式转换的关注点：

1.执行计划是否有internal_function()函数。

2.执行计划是否字段上多出了处理函数，比如to_number()

 

验证：minus

示例：A（原SQL，有隐式转换）-àB（改造后SQL）

(A minus B ) union all (B minus A)  

 

(SELECT *

from l_loan_mas

WHERE TRUNC(LOAN_DATE) <=  TRUNC(SYSDATE - 100)

MINUS

select *

from l_loan_mas

WHERE LOAN_DATE <  TRUNC(SYSDATE - 99))

UNION ALL

(SELECT *

from l_loan_mas

WHERE TRUNC(LOAN_DATE) <=  TRUNC(SYSDATE - 100)

MINUS

select *

from l_loan_mas

WHERE LOAN_DATE <  TRUNC(SYSDATE - 99))

 

 

5        常见的隐式转换类型及修改方案

 

5.1       对时间字段做函数操作的时候发生的隐式转换：

5.1.1       有对时间进行格式化：

TO_CHAR(T.DATE_CREATED, 'yyyymmdd') = TO_CHAR(SYSDATE, 'yyyymmdd')

可以修改为如下：

T.DATE_CREATED < to_date(TO_CHAR(SYSDATE + 1, 'yyyymmdd'), 'yyyy/mm/dd')

 and T.DATE_CREATED >= to_date(TO_CHAR(SYSDATE, 'yyyymmdd'), 'yyyy/mm/dd')

5.1.2       对时间字段操作函数,比较两边都是trunc取值 <=：

 TRUNC(CREATED_DATE) <= TRUNC(SYSDATE - 5)

可修改为：

CREATED_DATE < TRUNC(SYSDATE - 4)

 

示例：

select *

from l_loan_mas

WHERE TRUNC(LOAN_DATE) <=  TRUNC(SYSDATE - 5);

                       

修改后

select *

from l_loan_mas

WHERE LOAN_DATE < TRUNC(SYSDATE - 4);

 

 

 

 

 

5.1.3       对时间字段操作函数,比较两边都是trunc取值 =：

Trunc(E_CREATED) = Trunc (SYSDATE)

可修改为:

T.DATE_CREATED < trunc(SYSDATE + 1)

and T.DATE_CREATED >=trunc(SYSDATE)

 

实例：

SELECT LOAN_DATE

FROM L_LOAN_MAS

WHERE  Trunc( LOAN_DATE)   = Trunc (SYSDATE);

 

 

修改后方案

SELECT LOAN_DATE

FROM L_LOAN_MAS

WHERE  LOAN_DATE   <=trunc(SYSDATE + 1)

AND LOAN_DATE >trunc(SYSDATE);

 

 

5.1.4       时间字段操作函数,比较两边都是trunc取值 >=：

TRUNC(LLM.COMPLETED_DATE) >= TRUNC(sysdate, 'MM')

可以修改为：

LLM.COMPLETED_DATE >= TRUNC(sysdate, 'MM')

 

select appl_no,loan_date

from l_loan_mas

where trunc(loan_date) > =trunc(sysdate,'MM');

 

select appl_no,loan_date

from l_loan_mas

where loan_date > =trunc(sysdate,'MM');

 

 

5.1.5       对时间字段有ADD_MONTHS的操作如下：

TRUNC(T.DUE_DATE, 'mm') = ADD_MONTHS(TRUNC(:B1, 'mm'), -1)

可修改为：

T.DUE_DATE >= ADD_MONTHS(TRUNC(:B1, 'mm'), -1)

AND T.DUE_DATE< TRUNC(:B1, 'mm')

 

示例：

select appl_no,loan_date

from l_loan_mas

where trunc(loan_date) =ADD_MONTHS(TRUNC(sysdate, 'mm'), -1)

 

select appl_no,loan_date

from l_loan_mas

where loan_date >= ADD_MONTHS(TRUNC(sysdate, 'mm'), -1)

and loan_date < TRUNC(sysdate, 'mm')

 

 

5.1.6       时间函数,比较两边都是trunc取值 >：

TRUNC(FCD) > TRUNC(SYSDATE) – 3

可修改为：

FCD >= TRUNC(SYSDATE) – 2

 

示例：

select *

from l_loan_mas

WHERE TRUNC(LOAN_DATE)  > TRUNC(SYSDATE) -3;

 

select *

from l_loan_mas

WHERE LOAN_DATE  >= TRUNC(SYSDATE) -2

 

 

5.1.7       对时间字段的操作取月份差的例如：

floor(months_between(SYSDATE,loan_date)) >= 2

可以修改为：

loan_date <= add_months(SYSDATE, -2)

 

select *

from l_loan_mas

where floor(months_between(SYSDATE,loan_date)) >= 2

 

修改后

select *

from l_loan_mas

where loan_date <= add_months(SYSDATE, -2);

 

5.2       取值类型与字段类型不匹配

5.2.1       字段是varchar或者是char 而关系对等中是纯数字：例如下面

bank_flag = 1

可以修改为

bank_flag = ‘1’

 

示例：

l_loan_mas.loan_code字段为varchar类型

SELECT *

FROM l_loan_mas

WHERE loan_code = 44;

 

修改后

SELECT *

FROM l_loan_mas

WHERE loan_code = '44'

 

5.2.2       字段是number类型二传过来比较的值是string:

PK_SERIAL = :13 可以修改为  PK_SERIAL = to_number(:13)

 

示例：l_loan_mas.appl_amt类型 NUMBER(12,2)    

SELECT appl_no,appl_amt

FROM l_loan_mas.appl_amt

WHERE appl_amt = '50000';

 

修改后

SELECT appl_no,appl_amt

FROM l_loan_mas

WHERE appl_amt = 50000;

 

5.2.3       两个字段关联，其中一个字段是number类型，另外一个是varchar类型：

UP_DEPT_CODE  VARCHAR2(12)，PK_SERIAL   NUMBER number类型。

有如下关联时会发生隐式转换：UP_DEPT_CODE = PK_SERIAL

可以修改为：UP_DEPT_CODE = to_char(PK_SERIAL)

 

小结：

1.日期不要作操作。

2. 写=号时 两边类型一定要一致，不一致时需要显示转换。