前段时间,接到公司一项目组的数据库优化需求:一个计算费用的存储过程执行起来非常之慢,需要优化。
拿到存储过程之后,快速看了下代码,然后通过PL/SQL Developer去查看SQL代码的执行计划。其中,看到下面的一条简单的SQL语句的执行计划有点儿问题,导致主表CA_SE_MANIFEST走的全表扫描,而该表的数据量很大,而TMP_COM_ID2又是一张临时表。可是主表CA_SE_MANIFEST的BL_NO_ID字段是主键字段,有唯一索引,却没有走索引,这样的话,执行效率应该不会好到哪儿去!
SQL语句:
DELETE FROM TMP_COM_ID2 WHERE EXISTS(SELECT 1 FROM CA_SE_MANIFEST WHERE BL_NO_ID = C_ID AND DOCUMENT_TYPE IN ('1','2'))
执行计划:
经过分析,发现表TMP_COM_ID2的结构信息如下,只有1个字段,其中C_ID字段的数据类型是NVARCHAR2:
SQL> desc tmp_com_id2 Name Type Nullable Default Comments ---- ------------- -------- ------- -------- C_ID NVARCHAR2(20) SQL>
而主表CA_SE_MANIFEST的BL_NO_ID字段数据类型却是VARCHAR2:
SQL> desc ca_se_manifest Name Type Nullable Default Comments ------------------ ------------------- -------- ------- -------------------------------- BL_NO_ID VARCHAR2(20 BYTE) 出口提单序号 BL_NO VARCHAR2(40 BYTE) 同一船名、航次下、分公司的提单号不重复 CARRIER_BL_NO VARCHAR2(20 BYTE) Y 船公司提单号 ... ...
基本上定位到了原因,是由于VARCHAR2和NVARCHAR2数据类型不一致导致的隐式数据类型转换,进而导致主表CA_SE_MANIFEST的主键字段BL_NO_ID上的索引失效,最终产生了全表扫描。
经过分析,可以将临时表TMP_COM_ID2的C_ID字段数据类型改成VARCHAR2,而主表CA_SE_MANIFEST的定义信息显然不可轻易修改。修改完临时表的字段数据类型之后,该条SQL的执行计划已经不再是CA_SE_MANIFEST的全表扫描了:
最终,这个小问题得以解决。
补充:① VARCHAR2 可以认为是VARCHAR的变种,它是一个变长的字符串数据类型;
②NVARCHAR2 是包含UNICODE编码格式数据的变长字符串。