Odd UNION behavior in Oracle SQL query
Here's my request:
SELECT my_view.*
FROM my_view
WHERE my_view.trial in (select 2 as trial_id from dual union select 3 from dual union select 4 from dual)
and my_view.location like ('123-%')
When I run this query, it returns results that don't match the condition my_view.location like ('123-%')
. It is as if this condition is completely ignored. I can even change it to my_view.location IS NULL
and it returns the same results even though this field is not null.
I know this query seems ridiculous with choices from dual, but I structured it in such a way as to replicate the problem I am having when I use the WITH WITH clause (the results of this query are a choice of two built-in views).
I can modify the query like this and it returns the expected results:
SELECT my_view.*
FROM my_view
WHERE my_view.trial in (2, 3, 4)
and my_view.location like ('123-%')
Unfortunately I don't know the trial values up front (they are requested in the WITH WITH clause), so I cannot structure my query that way. What am I doing wrong?
I will say that my_view is composed of 3 other views, the results of which UNION ALL
, and each of which retrieves some data from a DB link. Not that I think it's important, but just in case.
a source to share
One thing you might try if you're out of luck with this route is to replace "IN" with "EXISTS" or "NOT EXISTS".
If you could accomplish what you want using joins, that would be the best option due to performance. If you have views retrieving data from views, you can often make a single query to do what you want, which gives you better performance with subqueries.
a source to share
If you do
EXPLAIN PLAN FOR
SELECT my_view.*
FROM my_view
WHERE my_view.trial in (select 2 as trial_id from dual union select 3 from dual union select 4 from dual)
and my_view.location like ('123-%');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
you should see where the location predicate is not being used, 9). My bet is that it has something to do with DB links and you won't be able to reproduce it if all tables are local.
Optimizing a distributed query gets tricky.
a source to share
Try changing your UNION query to use UNION ALL, as in:
SELECT my_view.*
FROM my_view
WHERE my_view.trial in (select 2 as trial_id from dual
UNION ALL
select 3 AS TRIAL_ID from dual
UNION ALL
select 4 AS TRIAL_ID from dual)
and my_view.location like ('123-%')
I also added "AS TRIAL_ID" in 3 and 4 cases. I agree that none of these questions matter, but sometimes I've come across situations where things I thought didn't matter did.
Good luck.
a source to share