query runs, but view from query doesn't
PRISMJan 24 2012 — edited Jan 24 2012We have a query that pulls data from about 10 tables in a remote database over DB link. When we run the query, which consists of a UNION, results are returned in roughly 20 seconds. However, when we create a view out of that query locally, SELECT * FROM the view never returns. It just spins and spins indefinitely.
Interestingly, if we remove one half of the UNIONed query, the view will return results. And if we create 2 views, each with one half of the UNION, and UNION the views together, it also returns the results.
Any ideas why we can't use the full UNIONed query in one view?
We are having this same problem in both 10.1.0.5 and 11.2.0.3.