sql - 痛苦缓慢的DB2查询

这个查询非常缓慢，我们的团队无法找出原因。我们已经试过创建视图，但仍然非常缓慢。有什么想法吗？

SELECT
    CI . CWARCASNBR AS CASENUMBER ,
    CI . CT1FYA AS COURTAGENCYCODE ,
    CI . CIPTYSQNBR AS PARTYSEQNBR ,
    CI . CIRCDTYPE AS CASETYPECODE ,
    CP . NMELASTBUS AS LASTNAME ,
    CP . NAME_FIRST AS FIRSTNAME ,
    CP . NAME_MID AS MIDDLENAME ,
    CP . NAME_SUFFX AS SUFFIX ,
    CP . CP_SEX AS GENDER ,
    CP . CT1PA AS RACECODE ,
    CP . CP_DOB AS DOB ,
    CP . CP_SSN AS SSN ,
    A . STREETNAME AS ADDRESS1 ,
    A . ADDRLINE2 AS ADDRESS2 ,
    A . CITYPARISH AS CITY ,
    A . ADDRSTATE AS STATE ,
    A . ZIPCODE AS ZIP
FROM
    CMSDPL23 . JE026001 AS CP
  LEFT OUTER JOIN
    CMSDPR23 . JE215000 CI ON
    CP . JEBOA = CI . CWARCASNBR AND
    CP . CT1FYA = CI . CT1FYA AND
    CP . CP_SEQ_NBR = CI . CIPTYSQNBR
  LEFT OUTER JOIN
    CMSDPR23 . CT007000 A ON CP . ADDRESSID = A . ADDRESSID
                         AND CP . ADDRESSPRI = A . ADDIDSEQNO
WHERE
    CP . NMELASTBUS LIKE 'Durham' || '%' AND
    CP . NAME_FIRST LIKE 'Roger%' || '%' AND
    NOT CP . PRTY_TCDE IN ( 'OFF' , 'BEP' ) AND
    CI . CI_FLAG_1 IN ( 'C' , 'B' ) AND
    CI . CT1MKA = '23'
ORDER BY
    CI . CWARCASNBR , CI . CT1FYA ;

最佳答案

首先，所有外键关系都有索引吗？（例如，CMSDPR23.JE215000，CP.JEBOA等。
其次，LIKE强制执行完整表搜索。你能索引NMELASTBUS和NAME_FIRST（等等）并检查是否匹配吗？
第三，WHERE子句中的字段有索引吗？

关于sql - 痛苦缓慢的DB2查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2289045/