SQl Optimization: using same column twice with OR in Where clause, leads very poor performance

Question

I have contact table, which contain Ids, name, email, delete and other columns. Have records around 5 Million.

Writing below query, then facing no issues getting results with in limits:

execution time: <2 secs.

Select row_id 
from contact 
where row_id in (select row_id 
                 from Contact_details 
                 where email in ('abc@abc.com', 'xyz@xyz.com'))

but when converting this query with OR clause for at-least default selection of 0 row_id it starts taking too long.

execution time: >120 sec.

Select row_id 
from contact 
where row_id=0 **OR** row_id in (select row_id 
                                 from Contact_details 
                                 where email in ('abc@abc.com', 'xyz@xyz.com'))

I have tried union rather than using OR, yes that improves performance but this is an application generated query, so is there a way through which this query can be improved without using union or union all.

What is your question - do you want to understand why it's happening, or get it to run faster? — Greg Viers, Feb 08 '18 at 15:52
How long does "Select row_id from contact where row_id=0" take to execute? — Greg Viers, Feb 08 '18 at 15:52
Please post the execution plan for both queries. Also, informations about table structure, including indexes, and number of records would be useful — Aleksej, Feb 08 '18 at 16:02

MatBailie · Answer 1 · 2018-02-08T16:22:56.053

As others have said, it in part depends on the time to execute SELECT row_id FROM contact WHERE row_id = 0. If that's slow, the OR isn't too blame.

If it's not slow, an OR can often throw a spanner in the Plan being generated (I'd love to see the full explain plan for all of these queries). In which case, you could try this...

SELECT row_id 
  FROM contact 
 WHERE row_id IN (SELECT row_id 
                    FROM contact_details 
                   WHERE email in ('abc@abc.com', 'xyz@xyz.com')

                  UNION ALL

                  SELECT 0
                    FROM dual
                 )

(And give us the explain plan to that query as well...)

EDIT:

Wait, are you really just selecting row_id, even in the outer query? If so, just do this?

SELECT row_id 
  FROM contact 
 WHERE row_id IN (SELECT row_id 
                    FROM contact_details 
                   WHERE email in ('abc@abc.com', 'xyz@xyz.com')
                 )
UNION
SELECT 0
  FROM dual

Thanks @MatBailie for quick response, I know union can improve the performance, but it is a kind of system generated query for contact segment creation where I want to add 0 contact id if no id is selected. — Pulkit, Feb 08 '18 at 16:55
Then give us the explain plans to help us identify the reason for the slow performance — MatBailie, Feb 08 '18 at 17:06

score 0 · Answer 2 · edited Feb 08 '18 at 16:20

0

sometime you can have performance issues with OR. In this case you can split your query with UNION :

Select row_id 
from contact 
where row_id in (select row_id 
                       from Contact_details 
                       where email in ('abc@abc.com', 'xyz@xyz.com'))
UNION ALL
Select row_id 
from contact 
where row_id=0;

edited Feb 08 '18 at 16:20

MatBailie

83,401
18
103
137

answered Feb 08 '18 at 16:18

pblin

1
1

Care needs to be taken with that suggestion. First ensuring that a row can't appear in both queries. Based on what we've been told that seems unlikely, but certainly possible. – MatBailie Feb 08 '18 at 16:20

SQl Optimization: using same column twice with OR in Where clause, leads very poor performance

2 Answers2