Query IN ORACLE to Optimize

Question

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.

I don't see the point of the unions, which all select the same column from the same table. Why do you think you need them? — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 20, 2018 at 7:36
Can't you replace the UNIONs with simple OR logic? If not can you use UNION ALL instead to remove the sort but permit duplicates? — McMurphy
– McMurphy, Commented Feb 20, 2018 at 7:46

Tim Biegeleisen · Accepted Answer · 2018-02-20 07:46:36Z

3

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

answered Feb 20, 2018 at 7:46

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

Although it has no impact on performance, the subquery is not needed.

Dima Yankin · Accepted Answer · 2018-02-20 08:36:58Z

1

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.

answered Feb 20, 2018 at 8:36

Dima Yankin

4602 silver badges12 bronze badges

Collectives™ on Stack Overflow

Query IN ORACLE to Optimize

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related