在 ORACLE 中查询以优化

斯里吉斯

SELECT *
FROM
(
    SELECT A_WEBC_URL AS FILE_LOCATION
    FROM BATELCO_BILLS_S
    WHERE BTL_BILL_I_NUMBER = :B2 AND BTL_BILL_I_ACC_NUMBER = '0' || :B1 
    UNION
    SELECT A_WEBC_URL AS FILE_LOCATION
    FROM BATELCO_BILLS_S
    WHERE BTL_BILL_I_NUMBER = :B2 AND (BTL_BILL_I_PROFILE_ID = :B1 OR
                                       BTL_BILL_I_PHONE_NUMBER = :B3 ) 
    UNION
    SELECT A_WEBC_URL AS SS
    FROM BATELCO_BILLS_S BILLS
    WHERE BILLS.BTL_BILL_I_PROFILE_ID = :B1 OR
        BTL_BILL_I_ACC_NUMBER = '0' || :B1 AND
        BILLS.BTL_BILL_I_NUMBER = :B2
)
WHERE ROWNUM = 1

上面的查询需要时间消耗。请帮助优化代码，以便我们可以轻松获取结果。

蒂姆·比格莱森

我没有看到联合的意义，我认为我们可以在没有联合的情况下重写子查询：

SELECT *
FROM
(
    SELECT A_WEBC_URL AS FILE_LOCATION
    FROM BATELCO_BILLS_S
    WHERE BTL_BILL_I_NUMBER = :B2 AND
         (BTL_BILL_I_ACC_NUMBER = '0' || :B1 OR
          BTL_BILL_I_PROFILE_ID = :B1 OR
          BTL_BILL_I_PHONE_NUMBER = :B3)
)
WHERE ROWNUM = 1

注意：我认为原始联合中的第三个查询在WHERE子句中的前两个术语周围缺少括号。也就是说，我认为您打算采用以下逻辑：

...
UNION
SELECT A_WEBC_URL AS SS
FROM BATELCO_BILLS_S BILLS
WHERE (BILLS.BTL_BILL_I_PROFILE_ID = :B1 OR
    BTL_BILL_I_ACC_NUMBER = '0' || :B1) AND
    BILLS.BTL_BILL_I_NUMBER = :B2

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。