SQL Performance (MOSC)

MOSC Banner

performance issue with view and union

edited Oct 30, 2014 2:47PM in SQL Performance (MOSC) 11 commentsAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center