④内部公开请勿外传--1EM上观察到的SQL执行情况--2初步发现语句耗时在VH表，55秒。1/4④内部公开请勿外传--3原始语句SELECTt0.FID,t0.fcreatedate,t29.fbizvoucheridFROMt_AR_receivableT0LEFTOUTERJOINt_AR_receivable_VHT29ONt0.FID=t29.FIDWHERE(((((((t0.FDATE>=TO_DATE('2019-10-0100:00:00','YYYY-MM-DDHH24:MI:SS'))AND(t0.FDATE<=TO_DATE('2019-10-3123:59:59','YYYY-MM-DDHH24:MI:SS')))ANDt29.FVOUCHERGROUPNO=688)ANDt0.FISINIT='0')AND(t0.FBYVERIFY<>'2'))ANDt0.FSETTLEORGID=2444836)AND(t0.FSETTLEORGIDIN(2444836,0)))ORDERBYt0.FCREATEDATEDESC,t29.FBIZVOUCHERIDASC--4测试语句的执行情况，发现耗时主要集中在这一步骤上：t29.FVOUCHERGROUPNO=688SELECTt0.FID,t0.fcreatedate,t29.fbizvoucheridFROMt_AR_receivableT0LEFTOUTERJOINt_AR_receivable_VHT29ONt0.FID=t29.FIDWHERE(((((((t0.FDATE>=TO_DATE('2019-10-0100:00:00','YYYY-MM-DDHH24:MI:SS'))AND(t0.FDATE<=TO_DATE('2019-10-3123:59:59','YYYY-MM-DDHH24:MI:SS')))ANDt29.FVOUCHERGROUPNO=688)ANDt0.FISINIT='0')AND(t0.FBYVERIFY<>'2'))ANDt0.FSETTLEORGID=2444836)AND(t0.FSETTLEORGIDIN(2444836,0)))ORDERBYt0.FCREATEDATEDESC,t29.FBIZVOUCHERIDASC5原来在匹配此条件时，需要先对表字段t29.FVOUCHERGROUPNO作to_number的数据转换，2/4④内部公开请勿外传--6检查表结构，发现该字段的数据类型为NCHARVAR2，--7修改该条件，添加上TO_NCHAR，SELECTt0.FID,t0.fcreatedate,3/4④内部公开请勿外传t29.fbizvoucheridFROMt_AR_receivableT0LEFTOUTERJOINt_AR_receivable_VHT29ONt0.FID=t29.FIDWHERE(((((((t0.FDATE>=TO_DATE('2019-10-0100:00:00','YYYY-MM-DDHH24:MI:SS'))AND(t0.FDATE<=TO_DATE('2019-10-3123:59:59','YYYY-MM-DDHH24:MI:SS')))ANDt29.FVOUCHERGROUPNO=to_Nchar(688))ANDt0.FISINIT='0')AND(t0.FBYVERIFY<>'2'))ANDt0.FSETTLEORGID=2444836)AND(t0.FSETTLEORGIDIN(2444836,0)))ORDERBYt0.FCREATEDATEDESC,t29.FBIZVOUCHERIDASC--8不再做转换操作，语句耗时立即下降到2秒以内。总结：以文本值做为过滤条件时，需要注意数据与字段数据类型的匹配关系，如有必要，可以添加函数来做数据转换，以确保等号两端数据类型匹配，这样才能确保语句的效率。4/4
