CODEWITHSUNDEEP
sqloracle-databasequery-optimization
Sreejith A
Sreejith A

Reputation: 27

Query IN ORACLE to Optimize

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

The above query taking time to consume.Please help to optimize the code so that we can fetch the results easily.

Upvotes: 0

Views: 59

Answers (2)

Dima Yankin
Dima Yankin

Reputation: 460

This query should run very fast, 3 unions are not needed but they shouldn't affect performance much. And you don't have any nested loops or joined tables in your query.

If you have performance problems, I think you just need indexes on this table. All of your WHERE conditions require checking of BTL_BILL_I_NUMBER field, so this query will run fast only if this column is indexed.

Do you have this column indexed?

If not, then you (or your DBA) should run:

CREATE INDEX IND_BTL_BILL_I_NUMBER ON BATELCO_BILLS_S (BTL_BILL_I_NUMBER);

If you don't see immediate effect, you (or your DBA) should gather Oracle optimizer stats for this table.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

I don't see the point of the unions, and I think we can rewrite the subquery without unions:

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

Note: I think the third query in your original union is missing parentheses around the first two terms in the WHERE clause. That is, I think you intended the following logic:

...
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

Upvotes: 3

Related Questions