performance issue with view and union
I am having an issue with a view that is a union of a "current" table and a "historical" table.
When selecting from this view with a data between selection using literal values, it comes back
quickly and uses a good index. However, when the date between gets its values from another table
using a sub-select, or even sub-selecting literals from dual, it goes into a full table scan.
If I take the union out of the view and create two separate views, it also works correctly.
The selection from the view below works correctly with the first date between and chooses