The view has been defined using UNION ALL from 2 tables one is the current table having data related to current month and another its archive which has all the past data. The tables have been partitioned on date.
The query throws a spool error and never comes back even after allotting lots and lots of spool.
If the view is replaced by its base tables the result is quicker. In the explain we see rowhash being used when we use the base table but in the explain when we use the view it show synscronised scanning and the time estimate is also much higher. Another trivia is it shows one unknown table with the tableid (hex) rather than the table name.
Does UNION ALL from views spools ou always? What exactly happens inthe optimizer? What is the solution for such problem? Why does the explain show that single new internal object tableid (hex) rather than the table name?